Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Asp.Net Programming

How to use SQL Server paging


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

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.

Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espanol : http://asp.net.do/foros/
======================================

"Donald Adams" <BDA_2@hotmail.com> wrote in message

news:55AE2C6B-1D43-402E-A04E-DB40627A481C@microsoft.com...

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

"Juan T. Llibre" <nomailrepl@nowhere.com> wrote in message
news:uJBLgY3pHHA.208@TK2MSFTNGP05.phx.gbl...

"Juan T. Llibre" <nomailrepl@nowhere.com> wrote in message
news:uJBLgY3pHHA.208@TK2MSFTNGP05.phx.gbl...

> !> 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...! ;-)

--
http://www.markrae.net

-----------------------------------------------Reply-----------------------------------------------

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.

:-)

Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espaol : http://asp.net.do/foros/
======================================

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.
--
Jim Anderson, MCSD
Consultant
Columbus, Ohio

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

On Jun 5, 7:22 pm, Jim Anderson <anderson_jim at Excite DotCom> wrote:

> 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.
> --
> Jim Anderson, MCSD
> Consultant
> Columbus, Ohio

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

-----------------------------------------------Reply-----------------------------------------------

On Jun 6, 2:22 pm, Alexey Smirnov <alexey.smir@gmail.com> wrote:

> 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.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc