Solved

TOP Query Performance Improvement

Posted on 2011-03-15
10
479 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loop to go backward 90 days 2 18
Run SQL Server Proc from Access 11 28
Whats wrong in this query - Select * from tableA,tableA 11 28
Mysql Left Join Case 10 48
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

785 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