Solved

TOP Query Performance Improvement

Posted on 2011-03-15
10
482 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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