How to use SQL Server paging

[Donald Adams]

I heard in an online video by Scott Gu, that there is an option to have the
asp.net datagrid in .net 2.0 do the paging in SQL Server 2005 rather than
pull all the data from the database and do it on the web server.
I've been unable to find any good documentation on this. I saw a property
to turn on paging if the select supports it, but that did not give me enough
information to complete my task.
Does anyone know where I can find documentation how to do this?

Thanks in advance,
Donald Adams

 

[Juan T. Llibre]

re:
!> I've been unable to find any good documentation on this.

I googled for : ( "SQL Server 2005" paging "Scott Guthrie" )
....and came up with the article in 5 seconds.

http://weblogs.asp.net/scottgu/archive/2006/01/01/434314.aspx

Scott provides sample code to experiment with.

 

[AlexS]

This works ok only on small sets (like 100000 records or around)

Problem is this approach requires order by clause, which is very inefficient
on big tables (>1Mio records)

I have 2.5Mio records table, and selection of page in 1Mio range takes
around 30 seconds on my PC. In beginning (1st, 2nd etc pages) selection
takes 10-15 seconds. Even though I have index on table. If order by is same
as index, selection is a bit faster, but just a bit (maybe 30% in best case)

I wonder if it is possible to use row_number without order by to speed up
selection. I have index already, so SQL Server should use it by default I
think.

Alex

 

[Mark Rae]

!> I've been unable to find any good documentation on this.

I googled for : ( "SQL Server 2005" paging "Scott Guthrie" )
...and came up with the article in 5 seconds.

That's just showing off...! ;-)

 

[Juan T. Llibre]

re:
!> That's just showing off...! ;-)

To quote the *first* recommendation in my ASP.NET FAQ :

---000---
With that in mind, the first FAQ recommendation is that you use search engines to
find out if the question you intend to ask has been already answered in newsgroups.

Use resources like : Google Groups Advanced Search and MSN Search to search for the text of
the problem which is stumping you, and save everybody the waste of time that asking a FAQ entails.

Remember : thousands of programmers will read your post. Be considerate of their time.
---000---

In this case, it wasn't a FAQ ( in fact, it was, rather pointedly, a *non-FAQ* ), but searching
Google and/or MSN *first* should be a pre-requisite to posting *any* question here.

 

[Guest]

Putting more than 100,000 records in a grid is never going to be efficicient,
whether you have paging turned on or not. I'd revisit your design. I doubt
your user's are really going to want to look at more than a 100,000 records
in a session. I suggest changing your query to only bring back a subset of
your data and if the record the user wants isn't there, go get another chunk
of data, etc.

 

[Guest]

Yes, and I might add that the ASP.NET Quickstarts should be added as a third
option. I've been doing this stuff since 2000, and I still refer to it.
Cheers,
Peter
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net

 

[Guest]

Putting more than 100,000 records in a grid is never going to be efficicient,
whether you have paging turned on or not. I'd revisit your design. I doubt
your user's are really going to want to look at more than a 100,000 records
in a session. I suggest changing your query to only bring back a subset of
your data and if the record the user wants isn't there, go get another chunk
of data, etc.

I fully agree with Jim.

Anyway, there are some things that you can check, Alex.

1) Index for the ORDER BY field. Update statistics for the table

2) For the first N pages you could do the following query

SELECT * FROM (SELECT TOP 110 ROW_NUMBER()
OVER (order by id) AS RowId, * FROM Table1) a
WHERE RowId > 100 AND RowId <= 110

In this example I select 10 rows starting from 101 through 110 (page
#10)

This should be faster on the large tables

3) and finally

http://www.4guysfromrolla.com/webtech/042606-1.shtml

Hope it helps

 

[Guest]

SELECT * FROM (SELECT TOP 110 ROW_NUMBER()
OVER (order by id) AS RowId, * FROM Table1) a
WHERE RowId > 100 AND RowId <= 110

Also do not use SELECT * FROM

Specify only the fields you need.