Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I'm stumped by why a particular query is slow Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 2:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:10 PM
Points: 20, Visits: 166
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



Post #1406267
Posted Friday, January 11, 2013 2:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1406271
Posted Friday, January 11, 2013 2:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:10 PM
Points: 20, Visits: 166
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



Post #1406274
Posted Friday, January 11, 2013 2:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 2,934, Visits: 2,959
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,
SQL Server developer at Seavus
www.seavus.com
Post #1406278
Posted Friday, January 11, 2013 3:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:10 PM
Points: 20, Visits: 166
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.



Post #1406283
Posted Monday, January 14, 2013 5:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1406659
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse