Solved

Select Query Optimisation

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

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 143

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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

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 143

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 143

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 74

Expert Comment

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

Expert Comment

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 23
SQL Select in Access 2003 3 21
Database Integrity 1 48
IIF in access query 19 21
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

791 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