September 3, 2009 at 10:05 am
My bad - I attached the plans to the message now.
September 3, 2009 at 10:29 am
It seems that a small change to the TSQL select statement will make the query much slower. Just adding one more (int) field to the output column list causes a major (problem) change in the execution plan.
Which makes sense. The additional column RecID is presumably not part of the Unit_Readings_ReadingID index, but the other columns selected from that table are.
The fact that (for the fast plan) SQL Server can retrieve all the columns it needs from the Unit_Readings_ReadingID index makes using that index efficient.
By adding the RecID column, by using the "fast plan", the SQL Server optimser would have to find the rows it wants from the index, and then do a lookup for each row to find the additional column. This time, the optimiser thinks it's quicker to scan the whole clustered index.
If you add the RecID colum to the Unit_Readings_ReadingID index, you will probably get the "Fast Plan"
September 3, 2009 at 10:37 am
The RecID is not part of the index, BUT neither are at least 2 of the other fields in the column list....
but those 2 not in the index are actually from the other tables...hmmm
But the full select list I originally posted was okay until I put the selection criteria into local variables...
Man I'm totally lost!
September 4, 2009 at 5:07 am
Ian Hockaday (9/3/2009)
The RecID is not part of the index, BUT neither are at least 2 of the other fields in the column list....but those 2 not in the index are actually from the other tables...hmmm
But the full select list I originally posted was okay until I put the selection criteria into local variables...
Man I'm totally lost!
The columns from the other tables aren't relevant to the way the optimiser has decided to access the unit_headings table. Hopefully you can see why adding RecID will have changed the way the optimiser decided to no longer use the Unit_Readings_ReadingID index when accessing that table... Google "covered index" if you are still unsure.
If you look at your "fast plan" you will see that the optimiser is heading straight for the rows it needs from the unit_headings table, using the index. It is then looking up the data from the other tables, based ONLY on the rows it needed from the unit_headings table.
For your "slow plan", the optimiser has decided to read through the whole of the unit_headings table to find the rows it wants, before looking up the data from the other tables.
As Gus pointed out, the reason for the switch after you changed to variables may be due to "parameter sniffing". SQL Server maintains statistics about the distribution of various values in the index (simplistically... how many entries have the value "A", how many have the value "B" etc). When you use variables, the optimiser tries to work out what values are in the variables, and builds a plan on the basis of those values and the information in the index statistics. If it works out that the variable contains "A" and a large percentage of the values in the index are "A", the optimiser may decide to read through the whole table, rather than use the index, which will be quicker in this case. That query plan is then cached, and re-used. The next time you execute the SQL with a different value (say "Z", which only has a few values), the optimiser will re-use that plan it stored for "A". In this case it is a bad plan for "Z".
Does that make any sense?
September 4, 2009 at 8:09 am
Try dynamic sql in the sproc. That will get you hard-coded values for every execution just like if you ran it in SSMS window with explicit values. You will pay a very small price in compilations and cache size, but that is well worth it to get 5 second runtimes as opposed to 15min runtimes. Please ensure you guard against SQL Injection.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 4, 2009 at 12:20 pm
If you look closely you will see that by adding recid to the select statement, sql has chosen totally different indexes to search, namely it is using 'Unit_Readings_Unique' in the 'slow' query to pick up that field. In your fast query it is using 'Unit_Readings_ReadingID'. Have you tried adding recid as an 'included' column on 'Unit_Readings_ReadingID'? That is where I would start.
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
September 5, 2009 at 1:52 pm
Within the stored proc I tried building the SQL command in a VARCHAR(MAX), @sCmd, variable and then, EXEC (@sCmd) - per Ten C suggestion; strangely enough it was slow.
If I do a PRINT of @sCmd and then execute that string in a TSQL window, it is fast.
Thanks for the suggestion though - I thought it would work too...
September 9, 2009 at 9:09 am
I have given up on trying to make a 'usable' stored procedure for this application.
What I will do is to embed the SQL code into the call from the .Net program.
My test gave 5,806 rows in 0 secs, using embedded SQL. The stored procedure call took 18 mins. 24 secs.
Thanks for all the help.
ianhoc
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply