?
Solved

TOP Query Performance Improvement

Posted on 2011-03-15
10
Medium Priority
?
483 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
Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

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?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

741 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