I have a small problem with some paging SQL code I'm using and was looking for some advice. First here is the code...
WITH OrderedOrders AS
SELECT EdArt.Section, EdArt.Status,ROW_NUMBER() OVER(ORDER BY Ed.CreatedDate DESC) AS 'RowNumber'
FROM Table1 Ed, Table2 EdArt
WHERE (EdArt.Section_I = "LIVE") AND (Ed.ID = EdArt.ID) AND (EdArt.Status_I = 1)
SELECT * FROM OrderedOrders
WHERE (RowNumber BETWEEN @start and @stop)
This code works fine, but in the OrderedOrders section you might notice I'm searching through the whole table to page the contents. In the example above @start and @stop have the value of 1 and 20 which is fine if the table only has a small number of records, but this search brings back over 150, 000 records. This is running really slow when searching the whole table and I know it's the OrderedOrders part doing it.
Now you're probably thinking, why not use the CreatedDate to filter some results, well that is what the query will do most of the time but I do want to be able to page all results.
Anyone know an easier way to page or a better way to utilise OrderedOrders than this?