Solved

TOP Query Performance Improvement

Posted on 2011-03-15
10
477 Views
Last Modified: 2012-05-11
This is driving me crazy so I'm hoping somebody can tell me why this is happening.  I have a select query with a sub query.  The sub query returns 300 rows in under 1 second.  Whenever I run the whole query though it's taking forever to run.  I gave up after 10 minutes.  But, if I put a TOP 10000000000 in the sub query the entire query finishes in under 1 second.  Why is this happening?  The sub query runs in under 1 second either way and returns the same number of rows.  

Using the example query below TABLE1 has a composite PK index.  I've rebuilt the index.  The order of the fields I'm joining on match the order the PK clustered index was created on.  I've created other indexes of each of the fields I'm joining on as well just to see if that worked.  The estimated execution plan is the same with or without the TOP.  
SELECT *
FROM TABLE1 A
   INNER JOIN (
      SELECT Val1,Val2, Val3, Val4, Val5, SUM(Val6)
      FROM TABLE2 C INNER JOIN ServerlOtherTables D
         ON C.ID = D.ID
      WHERE Val6 IS NOT NULL AND Val7 = 3
      GROUP BY Val1,Val2,Val3,Val4,Val5) B
   ON A.Val1 = B.Val1
   AND A.Val2 = B.Val2
   AND A.Val3 = B.Val3
   AND A.Val4 = B.Val4
   AND A.Val5 = B.Val5

Open in new window

0
Comment
Question by:tim_cs
10 Comments
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35137585
You can increase the performance of query  by applying individual index on each item used in Group clause.
0
 
LVL 15

Author Comment

by:tim_cs
ID: 35137671
I've already tried adding additional indexes.  I am noticing some differences in the execution plan now whenever I run with and without the TOP.  Also, putting the sub query into a CTE doesn't make any difference but using a temp table does.  

I'm just trying to understand what's happening and why some methods work and other don't even though they are returning the same exact rows.  
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35137753
is there any input variable, or is the statement static?
0
 
LVL 15

Author Comment

by:tim_cs
ID: 35137773
Nope, no input variables.  The entire query is static.  
0
 
LVL 10

Accepted Solution

by:
John Claes earned 400 total points
ID: 35137948
Tim

Your Issue :
inner query returns Directly the result. (using top or Not)

The full query's speed changes between the useage of the TOP in the Inner Query

UIs this correct ?

Then my Answer is :
When using the default query the Inner Query is always performed and the result is added into a Temporary table.
The folowing step is to Join this temp table with your Table1.
Here is the Big difference in time


possible reasons :
SQL Server will come up with n execution plans for a given statement and choose the one it predicts will have the lowest cost. It doesn't produce ALL the possible plans. Therefore, any change to a query (i.e. adding a TOP clause) could result in slightly different execution plans being produced and hence in this case it's resulting in a different, better plan.

If the Executionplan is almost the same but time is different  
Posiblity 1 (need the execution plans from you)
It seems that when you have the TOP clause, the WHERE clause is evaluated first to limit the number of rows and the JOINs are done (on less rows then you get a performance boost).

BTW : Creating extra Indexes will not do the Job because only 1 index can be used in the query.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35138275
Hi You can also use some condition to shorten your sub query result.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 35138467
then I see no real solution other than to use the temp table (or table variable) for the subquery
0
 
LVL 18

Expert Comment

by:deighton
ID: 35138855
try out some joint hints

http://msdn.microsoft.com/en-us/library/ms173815.aspx

who knows?
0
 
LVL 18

Expert Comment

by:lludden
ID: 35139502
You need to look at the execution plans.  Look at the details on the parts that are a high % of the task, or that have large arrows (more data being transferred).  Check also for Lazy/eager spools.
0
 
LVL 15

Author Closing Comment

by:tim_cs
ID: 35141931
I figured I would just have to change the query around I was just more interested in understanding why is was working that way.  Thanks for the feedback.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Select Query help 3 31
SQL query 4 27
SQL Script to find duplicates 16 19
SQL Server memory Issue 7 73
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now