I'm setting up to use Row_number() for paging - It's ideal for this.
But, I am finding that there is a huge performance difference once I add that final rowID filter:
where rowId between @rowStart and @rowEnd
It is the identical query, with the only exception of that final where clause.
And when I say slow, the elapsed time goes from around 3500 ms to 32,000ms, so maybe 10 times slower.
Anyone else run into this, and work out why it is happening.
I'm trying to get the developers off of API cursors, but the where clause on the row_number negates any optimizations, making the cursors just as fast as a set based approach.
The more you are prepared, the less you need it.