Solved

Select Query Optimisation

Posted on 2008-10-10
9
211 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now