?
Solved

Select Query Optimisation

Posted on 2008-10-10
9
Medium Priority
?
218 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
[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
  • 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 

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 750 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

752 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