SELECTing nth number performance?

Hi everyone I'm using custom paging and I'm getting data with this query.开发者_如何学Python

DECLARE @pageIndex INT = 0;
DECLARE @pageSize INT = 10;
WITH foo AS (
    SELECT ROW_NUMBER() OVER (ORDER BY test ASC) as rownumber, test, col
    FROM TestDB.dbo.test
)
SELECT test, col FROM foo
WHERE rownumber BETWEEN (@pageIndex * @pageSize) + 1 AND (@pageIndex * @pageSize) + @pageSize

Its working as it should but I must consider performance so, here is the question.

Which way is better for SQL Server Performance?

Selecting whole data in table then paging in asp.net, php etc. OR selecting data with method which I described above.

I'm confused a bit because the method I described above is selecting whole data and giving Row Number to every row then Selecting rows which Row Number is between X and Y.

---

I think there is no obvious answer for this question. It really depends on following conditions:

1. How large amount of data you are paging.
2. How frequent you need to access these data (i.e. how much clients access data at a time).

If you have large amount of data I would go for SQL paging, which should be faster, since the database engine is optimized for this (providing that you have the right indexes created). However if you have small amount of data (in terms, that it'll fit in ram memory) you may load all the data, and you will boost performance, since the IO won't be a bottle neck.

In general, I would go for SQL paging, which is easier to maintain and to optimize if your dataset grows. Anyhow if io starts to be a bottleneck, depending on your resources you may replicate the databases, change the hardware etc. without the need of rewriting your running application.

To sum up: it really depends :).

---

There is is no one solution that fits all cases

If you have no more than a few pages, then it may be better to page in the web page.

Our experience is that web page paging worked for us: we used a div to show more results so all data was rendered, no round trips to either the web or database server. More practically, our view is that more than a few pages of results is useless for the user so it was capped. So we had a finite and reasonable amount of data to manage without round tripping to SQL.