January 11, 2013 at 2:26 pm
I'm hoping someone can shed some light on this. (I'm currently running SQL Server 2008 R2 Enterprise Edition)
The punchline, (before I get to the specifics below) is that the query returns 18 results, and is quite slow when I say SELECT *, but it is blazing fast if I say SELECT TOP 18 * returning the same results. This boggles me and I haven't been able to understand why or come up with a solution.
The data structure is for a directory containing profiles that are displayed on a website.
So here is the query:
SELECT *
FROM (
SELECT a1.ProfileID, a2.ProfilePracticeAreaID
FROM dbo.Profiles a1
INNER JOIN
dbo.Profiles_PracticeAreas a2 ON a2.ProjectID = @ProjectID AND a2.ProfileID = a1.ProfileID
WHERE a1.ProjectID = @ProjectID
AND a1.StateID = @StateID
AND a2.PracticeAreaID = @PracticeAreaID
) a0
INNER JOIN
dbo.Profiles a1 ON a1.ProfileID = a0.ProfileID
LEFT OUTER JOIN
dbo.Profiles_PracticeAreas a2 ON a2.ProfilePracticeAreaID = a0.ProfilePracticeAreaID
LEFT OUTER JOIN
dbo.PracticeAreas a3 ON a3.PracticeAreaID = a2.PracticeAreaID
LEFT OUTER JOIN
dbo.Profiles_Websites a4 ON a4.ProfileWebsiteID= a1.PrimaryWebsite
LEFT OUTER JOIN
dbo.Discounts a5 ON a5.DiscountID = a1.DiscountID
LEFT OUTER JOIN
dbo.Profiles_Offices a6 ON a6.ProfileOfficeID = a1.PrimaryOffice
So the first subquery is just getting the unique ids.
The dbo.Profiles table has an index on ( ProjectID, StateID ) INCLUDE ( Profile ID )
The dbo.Profiles_PracticeAreas table has an index on ( ProjectID, ProfileID ) INCLUDE ( PracticeAreaID )
All of the other tables are being joined on their respective primary keys.
Here are the IO results:
(18 row(s) affected)
Table 'Profiles'. Scan count 24, logical reads 141, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profiles_PracticeAreas'. Scan count 118, logical reads 1968, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 36, logical reads 96, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PracticeAreas'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Discounts'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profiles_Websites'. Scan count 0, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profiles_Offices'. Scan count 0, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Here are the TIME results:
(18 row(s) affected)
SQL Server Execution Times:
CPU time = 55266 ms, elapsed time = 3950 ms.
Now, if I switch this to SELECT TOP 18 *, keeping the rest of the query identical, I get this for IO results:
(18 row(s) affected)
Table 'Profiles_Offices'. Scan count 0, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Discounts'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profiles_Websites'. Scan count 0, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PracticeAreas'. Scan count 0, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profiles_PracticeAreas'. Scan count 1, logical reads 197, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profiles'. Scan count 19, logical reads 106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
And this for the TIME results:
(18 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
The biggest problem I have is that I don't understand WHY this would be the case. Aside from reworking the underlying data structure (e.g. denormalizing some of the data), I don't really see what would need to be changed to help the query optimizer to build an execution plan. The indexes I'm using seem correct to me.
I've also just run REBUILD on the index and UPDATE STATISTICS for every one of these tables, just in case, but that didn't change the results.
EDIT: The execution plan shows that the slow query is performing an Index Scan on the dbo.Profiles_PracticeAreas table on the wrong index, and the quick one is doing an Index Seek on the correct one. Any ideas why selecting TOP __ would do this?
Any pointers or tips?
Thanks.
Jeffrey Kretz
January 11, 2013 at 2:40 pm
Can you post the ddl and all indexes as well as actual execution plans? Without those details we are shooting in the dark.
Take a look at Gail's post about details to post when needing help with a performance issue.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2013 at 2:50 pm
Oops. Sorry I didn't get the full package together.
Incidentally, since the Profiles_PracticeAreas table was causing the table scan, I just now added a new index on the Primary Key field which seemed to have resolved the issue. The query optimizer somehow wasn't using the PK_Index unless I was running a SELECT TOP ___
I'm going take a bit of time fiddling with things and performing more tests on this and similar queries. If it turns out that this hasn't resolved the issue, I'll return with a full data structure and execution plans.
Thanks,
J
January 11, 2013 at 2:54 pm
Hi,
Interesting question.
Are the indexes you mentioned clustered or non-clustered? It is important. I'll presume they are clustered and can suggest to you to replace this set of data
(
SELECT a1.ProfileID, a2.ProfilePracticeAreaID
FROM dbo.Profiles a1
INNER JOIN
dbo.Profiles_PracticeAreas a2 ON a2.ProjectID = @ProjectID AND a2.ProfileID = a1.ProfileID
WHERE a1.ProjectID = @ProjectID
AND a1.StateID = @StateID
AND a2.PracticeAreaID = @PracticeAreaID
) a0
with a temp table a0 with clustered index on a0.ProfileID and then execute the query.
I'll be waiting for your results...and for the other suggestions as well.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
January 11, 2013 at 3:23 pm
Unfortunately (from a debugging perspective anyway), the problem has now gone away, ever since I added the new index on the dbo.Profiles_PracticeAreas table.
Since this has actually improved performance in the real world (the problem I was trying to solve) I don't want to delete that index just to see what would happen.
I figured I could back up the current database to my development server, then delete the new index and post the results here. HOWEVER, when I tried that the dev server's query optimizer is correctly using the PK index for it's execution plan and it running fast.
Grrr. Essentially, I can no longer replicate the error.
There must have been some aspect of the data structure that resulted in the query optimizer ignoring the PK index on the dbo.Profiles_PracticeAreas table (despite the join statement using the Primary Key field) and was instead doing an index scan on a different index.
I'm going to run SQL profiler for a while and see if there is another query exhibiting the same phenomenon. If so, I'll capture execution plan and post details.
January 14, 2013 at 5:01 am
I'd check out the new execution plan and figure out what it's doing now. Just so you know in case there are issues in the future. You do have parameters in there so you may see bad parameter sniffing in the future.
As to why performance changed when you put TOP into the query, that would have changed the choices presented to the optimizer and given you a new plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply