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.