April 29, 2010 at 10:54 pm
Hi Folks,
what do I need to do to optimize this query .. it take 10mins
the query and execution plan is attached.
I missed a query in my orginal post
April 30, 2010 at 5:06 am
There are "missing index" mentioned in both extranetplan3004-2.sqlplan and extranetplan3004-3.sqlplan.
In SQL 2005 you can see the "missing index" tags in the XML plan.
Try to create those indexes and run the query again.
April 30, 2010 at 5:19 am
...and some quick thoughts - sorry if I'm missing anything...:
- if you insert into the table variable (@TempQuote) a big number of records (usually I use the 1000 records threshold, but better test) try to use a temp table
- try to avoid "OR" by using dynamic SQL
...and a quick question - do you really need "WHERE 1 = 1"?
April 30, 2010 at 5:23 am
can you post index definition ?
I think TempTraveller table needs better indexing.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 30, 2010 at 8:12 am
It's difficult to be sure about execution plans and the issues when looking at estimated plans. When possible, please post actual plans.
The OR (ID IN... part of the query might, might, work better as an OUTER JOIN. You'll need to test it.
Just based on the cost estimates, which are not always the best measure because they're estimates, the scan against this table suggests either bad statistics or an index that insufficiently selective, [repstraining].[dbo].[TempTravellerAddressXref].[PK_TEMPTRAVELLER_ADDRESS_XREF] [ttx]. I say this because it's doing a HASH join between an estimated 3 rows and an estimated 100,000 rows. It's also the higest estimated cost, so, possibly, the most expensive operation, but since you're also using table variables, which are cost based on a single row, this may be completely inaccurate.
You're getting a key lookup on this table and only outputing a single column, [repstraining].[dbo].[TempQuoteHOTValues].Hot_Commission. You could probably make that an INCLUDE in the other index, [repstraining].[dbo].[TempQuoteHOTValues].[IX_TempQuoteHOTValues_TempQuote_ID_Premium_Override_Discount_Amount] [tqhv].
That's just a few things that I saw.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 2, 2010 at 2:39 am
One more thought from my side , you are using table variable instead you should use temp table with clustered index on it as you are using these tables in JOIN operation , it might also gain some perfromance.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 2, 2010 at 5:27 pm
Since you have WHERE 1=1 in this code, that's a pretty good indication that this is embedded SQL and not a stored procedure. The code is pretty poorly written with about a bazillion correlated subqueries and the estimated plan you show reflects that with many INDEX SEEKS taking more than 70/80 % of the workload because of REBINDS and the like.
First, I have to tell you that "Set based" code is definitely the answer to the need for speed. Then I have to tell you that solving a whole problem in a single query does NOT mean it's good set based code.
My recommendation would be to scrap this code and start over. Use proper "Divide'n'Conquer" methods to first find the minimum number of rows to be returned by the query and store that in a Temp Table. Then do the necessary proper joins to return only what needs to be returned and watch for "fat" lines in the ACTUAL execution plan... those are normally indications that the same rows are being examined over and over and that's contrary to proper set based programming.
Splitting these monsters up and possibly holding some interim result sets in Temp tables will do a lot to speed things up. I've rebuilt similar queries that took 45 minutes to run so that they returned the proper result in about 3 seconds.
--Jeff Moden
May 3, 2010 at 4:03 am
Jeff's advice is one of the best in this case.
I think that we are dealing here with an example of "Jack-of-all-trades" code (http://www.databasejournal.com/features/mssql/article.php/3830981/T-SQL-Best-Practices--Part-2.htm)
Besides the code review Jeff recommends, check if the database design also deserves some improvement.
May 3, 2010 at 5:32 pm
I have added the indexes it mentioned in the xml plan. THe query ran well initially but I have tried it the following day and it is back to the same speed.
I attached the xml plan. IT mentions a missing index that I have added previously so I am not sure why it is complaining. In any case that query is quite fast anyway.
I will take on board the loading into temp table - but I need to try and get this query running as fast as possible in the meantime.
May 3, 2010 at 9:34 pm
Bhuvnesh's suggestion seemed to of worked.
Using a temp table rather than the table variable speeds the query by upwards of 9mins
it only take 9 seconds now.
thanks all!
May 4, 2010 at 12:09 am
bodhilove (5/3/2010)
Bhuvnesh's suggestion seemed to of worked.Using a temp table rather than the table variable speeds the query by upwards of 9mins
it only take 9 seconds now.
thanks all!
thats great , you can also try to use covering index that will also improve your query.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply