Solved

TOP Query Performance Improvement

Posted on 2011-03-15
10
480 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35137753
is there any input variable, or is the statement static?
0
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.

 
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 143

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

789 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