Select Query Optimisation

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?
Phil CatterallAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you might end up having to avoid the view altogether ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Phil CatterallAuthor Commented:
This makes the query worse and the inner select takes a long time to run on its own.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what indexes do you have on the table?
how many records are there in that table?
or is vSearchListCompany actually a view???
0
 
Phil CatterallAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you would have to run the 2 queries with "include the actual explain plan", and see what difference it makes?
0
 
Phil CatterallAuthor Commented:
Thanks for your help. I will find an alternative way
0
 
sdstuberCommented:
congratulations angeliii on reaching Elite.  wow!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.