1

I have a query on sql server 2012 sp3 which is built dynamically through an application. I have noticed a case where it runs slow due to insufficient execution plan and I am trying to figure out the problem.

In this case the query that is being built has the following form

Select some columns from 
(SELECT TOP 1 1 AS NEW FROM tr) AS AL 
JOIN 
(select some columns from a view join some tables
 where column = 'a' or column = 'b' column = 'c'...) t5
ON 1=1 WHERE [t5].[ROW_NUMBER] BETWEEN 0+1 AND 0+20 ORDER BY [t5].[ROW_NUMBER]

The outer select is being used for pagination. The inner select labeled as t5 runs fast when is being executed alone in any case. However combined with the outer select for pagination it can be very slow depended on the number of values chosen in its where statement and how selective (small number of rows fetched) it is.

I have tried to change the query to improve performance but when i do this i ruin the performance of queries built by the application which is not selective (fetch many rows)

From what I see, the execution plan is depended on the values selected in the where statement. Is there a way to help sql server choose the right execution plan so that it can avoid useless rows reads?

I would appreciate any suggestion.

Andreas Dest
  • 59
  • 7
  • 2
    Use `OFFSET`/`FETCH` for pagination. – Gordon Linoff Dec 21 '16 at 13:20
  • 2
    You could try updating statistics and then run your query s SQL Server would choose the best possible execution plan. – Wes Palmer Dec 21 '16 at 13:55
  • Change to offset/fetch has no impact to the execution plan and the query is still slow. Without offset/fetch the query runs fast. Every method for pagination is slow when the query is selective, but they work fine when there is not where statement. Probably I have to update statistics because until know I was counting on Auto Update Statistics – Andreas Dest Dec 21 '16 at 14:42

0 Answers0