Select Query Optimisation
Posted on 2008-10-10
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
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
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?