SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I'm stumped by why a particular query is slow


I'm stumped by why a particular query is slow

Author
Message
jeffkretz
jeffkretz
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 219
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



Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63544 Visits: 17966
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/

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jeffkretz
jeffkretz
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 219
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



Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10440 Visits: 5157
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
jeffkretz
jeffkretz
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 219
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.



Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99471 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search