Solved

Select Query Optimisation

Posted on 2008-10-10
9
212 Views
Last Modified: 2010-04-21
Hi,

I have the following select query that is used to page data from a web application and this should return the first 10 rows.

SELECT * FROM
( select * , ROW_NUMBER() over(ORDER BY  comp_actualfleetsize DESC , Comp_CompanyId DESC )
AS rowranking  from
vSearchListCompany WHERE
comp_name LIKE N'Doors & Hardware%' ESCAPE '|' AND COALESCE(comp_status, N'') = N'' AND
COALESCE(comp_status, N'') = N'' AND COALESCE(comp_status, N'') = N''
) as A  
 WHERE
rowranking > 0 and rowranking < 11

The inner select (aliased as A) in this example actually returns no data. The problem I have is if i include the rowranking < 11 the query takes over 3 minutes to run, if I remove the rowranking < 11  and just include the rowranking>0  or have no external where clause the query is instant.  As the inner select returns no data i would have thought the external where clause would have no impact in this example?
0
Comment
Question by:Phil Catterall
  • 5
  • 3
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685605
what if you do this:
SELECT * FROM 
( SELECT TOP 11 * , ROW_NUMBER() over (ORDER BY  comp_actualfleetsize DESC , Comp_CompanyId DESC ) AS rowranking  
from vSearchListCompany 
WHERE comp_name LIKE N'Doors & Hardware%' ESCAPE '|' 
AND COALESCE(comp_status, N'') = N'' 
AND COALESCE(comp_status, N'') = N'' 
AND COALESCE(comp_status, N'') = N'' 
ORDER BY  comp_actualfleetsize DESC , Comp_CompanyId DESC 
) as A  
WHERE rowranking > 0 and rowranking < 11

Open in new window

0
 

Author Comment

by:Phil Catterall
ID: 22685618
This makes the query worse and the inner select takes a long time to run on its own.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685706
what indexes do you have on the table?
how many records are there in that table?
or is vSearchListCompany actually a view???
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Phil Catterall
ID: 22685738
vsearchlistcompany is a vew joining 4 large tables together, it has approx 380k records. I have many indexes on all the tables and it's not easy to list them all here, but I am happy with the querying spped of the inetrnal select.  I suppose my question is more, if the inner select returns results very quickly, does the external where clause make such a difference.  
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686084
you would have to run the 2 queries with "include the actual explain plan", and see what difference it makes?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22686088
you might end up having to avoid the view altogether ...
0
 

Author Closing Comment

by:Phil Catterall
ID: 31504951
Thanks for your help. I will find an alternative way
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22686940
congratulations angeliii on reaching Elite.  wow!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686966
thanks!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

786 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