I'm trying to retrieve a batch of rows and have achieved this using the row_number function, to for example retrieve the first 10,000 rows as follows:
select *
from
(select
row_number() over (order by id) as row_num, *
from
forms) as batch
where
batch.row_num between 1 and 10000
This performs well first the first batch but becomes progressively slower as it reaches higher row numbers and one of the tables I'm using has > 2.7 million rows.
Is there any way to do this with a linear performance?
There's a clustered index on the primary key column and 5 other non-unique, non-clustered on some of the other columns. I'm processing the whole table in batches sequentially and have currently chosen 10,000 as the batch size. The 1-10,000 query takes less than a second but, for example, 200,000 - 210,000 takes 10 seconds and by the time it's in the millions it's > 1 minute.
Either remember the max id from the last batch and do a
SELECT top 10000 ...
WHERE id > @maxid
ORDER BY id
or you can use an Api Cursor to efficiently process a table in sequential chunks.