SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Server Side Paging using SQL Server 2005
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/04/2007 :  09:26:23  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
A common activity in applications is to page results or record sets from a database. This is usually done on the client using the client's paging functionality or on the server through a variety of methods. In SQL Server 2000 those server side methods typically used dynamic SQL or nested TOP clauses and weren't very efficient. Using Common Table Expressions in SQL Server 2005 we have a better way to page record sets on the server.

Article Link.

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 01/05/2007 :  09:07:52  Show Profile  Visit jhermiz's Homepage  Reply with Quote
Nice, much simpler than SQL 2k. Thanks for the example as well.



Keeping the web experience alive -- http://www.web-impulse.com

RS Blog -- http://weblogs.sqlteam.com/jhermiz
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/05/2007 :  09:27:17  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Agreed. Thanks for the nice article, Graz -- you beat me to it on that one!

- Jeff
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 01/10/2007 :  15:24:23  Show Profile  Visit jhermiz's Homepage  Reply with Quote
I wrote up an example using Graz's code.
The example basically uses a GridView control in ASP.net 2.0
and shows you how to page on the server side.

The example can be found here http://jhermiz.googlepages.com/paginginsqlserver2k5andasp.net2.0


Keeping the web experience alive -- http://www.web-impulse.com

RS Blog -- http://weblogs.sqlteam.com/jhermiz
Go to Top of Page

sgtpusmc
Starting Member

USA
2 Posts

Posted - 01/25/2007 :  15:03:30  Show Profile  Reply with Quote
Hey Graz! It's been a while...

Anyway, I have mixed thoughts on this, yes CTEs can make things like pagination more efficient etc... However, pagination is fundamentally a display issue and as such I tend to hold that it properly performed at the presentation layer.

Real DBAs have Normalized Relations!
Go to Top of Page

cas_o
Posting Yak Master

United Kingdom
154 Posts

Posted - 02/23/2007 :  09:42:37  Show Profile  Visit cas_o's Homepage  Reply with Quote
quote:
Anyway, I have mixed thoughts on this, yes CTEs can make things like pagination more efficient etc... However, pagination is fundamentally a display issue and as such I tend to hold that it properly performed at the presentation layer.


Wrong wrong wrong, (in my opinion anyway) The UI should decide page size and page number to display yes, BUT the actual work of pagination (which is a form of partitioning data) belongs well and truely in the data layer not the UI layer.

;-]... Quack Waddle
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 05/08/2007 :  11:30:50  Show Profile  Reply with Quote
How does this approach of server-side pagination get affected by the underlying data changing?
If the data changes before I ask for page two, do I run the risk of seeing some of the same data in page2 as I did in page1?
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4138 Posts

Posted - 05/08/2007 :  11:49:02  Show Profile  Visit graz's Homepage  Reply with Quote
I think almost all paging solutions have that problem. Most times when it's written with ASP.NET each page refresh re-queries the database unless you explicitly cache it.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/08/2007 :  12:38:11  Show Profile  Reply with Quote
We have two methods of paging:

"Go to Page N" works as per this article.

"Go to Next/Previous Page" passes the Max/Min key, respectively, shown on the current page, plus the current page number, and locates appropriate records based on it. "Nexting", as we call it, through records is unaffected by insertions and deletions - except that if you "next" back to page 1 there may be less rows than a normal full page, indeed it might be Page No 2 or even Page No -1 !!

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/08/2007 :  12:43:43  Show Profile  Reply with Quote
Whilst on the subject ...

... do any of these "Gets rows 10 - 19" style paging methods also provide the total number of "pages"? (so that the Web page can show "Page N of M" or similar)

We are still pulling all the PKs into a temporary table, setting an Identity using OrderBy the paging columns, to then know the total number of records and be able to get "Rows N to M". Seems better, assuming complex WHERE clause etc., to get all the PKs rather than get just the "Rows N to M" but also have to do a COUNT(*) using the whole WHERE clause a second time.

Maybe there is a smarter way?

Kristen
Go to Top of Page

sgtpusmc
Starting Member

USA
2 Posts

Posted - 05/08/2007 :  13:09:08  Show Profile  Reply with Quote
quote:
Originally posted by cas_o

Wrong wrong wrong, (in my opinion anyway) The UI should decide page size and page number to display yes, BUT the actual work of pagination (which is a form of partitioning data) belongs well and truely in the data layer not the UI layer.

;-]... Quack Waddle



In exactly what way is pagination NOT a display issue? Data partitioning has NOTHING to do with how the data is displayed and is another subject altogether. Clients are completely unaware of data partitioning and vice versa.

Pagination, on the other hand is purely a display issue. Pagination is greatly affected by the client application and/or user preferences.

----------------------------------------------
Real DBAs have Normalized Relations!
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/08/2007 :  14:18:33  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by sgtpusmc

quote:
Originally posted by cas_o

Wrong wrong wrong, (in my opinion anyway) The UI should decide page size and page number to display yes, BUT the actual work of pagination (which is a form of partitioning data) belongs well and truely in the data layer not the UI layer.

;-]... Quack Waddle



In exactly what way is pagination NOT a display issue? Data partitioning has NOTHING to do with how the data is displayed and is another subject altogether. Clients are completely unaware of data partitioning and vice versa.

Pagination, on the other hand is purely a display issue. Pagination is greatly affected by the client application and/or user preferences.

----------------------------------------------
Real DBAs have Normalized Relations!



sgtpusmc -- If a SQL result returns 10,000 rows, and the client only wants 20 rows to display, it should only get 20 rows (ideally). Why should the client receive all 10,000 rows? Also, consider things like disconnected web-based clients that don't have state, or have a shared state on a server; should a web server cache all 10,000 rows as a user is paging, or request all 10,000 rows over and over to only display 20 at a time? If 100 users are hitting the web page at once, think about the traffic and the huge size of the cache that would be required to all this on the client. If the database does it, it simply queries the table and returns the rows requested.

No one believes in separating data from presentation more than me (ask anyone here!) but for paging, I do believe that it is best handled by the database layer unless you have a super-fast network and each individual client is a full desktop application that can locally cache data on their end. Even then, I am not so sure since the database server will be taxed returning all that data, especially considering that the clients will probably keep asking for all the rows over and over as the search criteria is altered and tweaked.



- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 05/08/2007 14:37:17
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/08/2007 :  14:54:07  Show Profile  Reply with Quote
"No one believes in separating data from presentation more than me (ask anyone here!) but for paging, I do believe that it is best handled by the database layer"

Well, no doubt if I stick my neck out someone will chop it off! but I reckon the criterion for:

"Get all records where SomeDate between 01-Jan and 31-Dec"

is not much different to:

"Get rows 10-19 from records ordered by SomeDate"

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/08/2007 :  15:11:56  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
They are the basically the same if you have a RowNumber column in your result set, just a SELECT with some criteria.

Whether or not it is efficient for your database to calculate and filter by "Row Number" is another question, but with SQL 2005's features, it *is* quite efficient and also easy.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/08/2007 :  15:15:38  Show Profile  Reply with Quote
Aye.

Jeff have you got any crafty fixes for me earlier question?

i.e. ==> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77090#305407

Thanks
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/08/2007 :  15:34:03  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Not that I can think of .... I believe you have no choice but to make two passes. perhaps google has the right idea, since it just estimates the total row count to perhaps save the huge cost of counting.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/09/2007 :  08:56:52  Show Profile  Reply with Quote
"estimates the total row count"

Bit of a challenge in T-SQL, isn't it?

SELECT COUNT(*)
FROM
(
    SELECT TOP 1000 MyPKColumn
    FROM MyTable
    WHERE ... complicated & slow stuff ...
) AS X

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/09/2007 :  09:16:53  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
It is a challenge, I'm sure they have an algorithm or something.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 06/18/2007 :  10:13:22  Show Profile  Reply with Quote
Is this method any faster than creating a table variable?
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 06/18/2007 :  10:32:25  Show Profile  Reply with Quote
Do you need a COUNT(*) [e.g. for the "Page 1, 2, 3, ... 999" links]?

If you just need data for "Page 5" this should be quicker.

We use Temp Table stuff to get all PKs, that gives us the COUNT(*) for the Total Page Numbers and from the Temp Table we get the data for "Page 5".

I haven't tested it on SQL 2005 yet, so its possible that:

SELECT COUNT(*)
FROM MyTable JOIN LotsOfOtherTables
WHERE VeryComplexStuff

plus

SELECT FancySQL2005SelectForJustPage5
FROM MyTable JOIN LotsOfOtherTables
WHERE VeryComplexStuff

is faster than

INSERT INTO @TempTable
SELECT M.MyPK
FROM MyTable AS M JOIN LotsOfOtherTables
WHERE VeryComplexStuff

and then doing the Paging Stuff.

Perhaps I should do a little test ...

Kristen
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 01/14/2008 :  12:57:49  Show Profile  Reply with Quote
wouldn't it be faster by JUST putting the rowNumber and PKID in the CTE, and then doing an inner join on the actual table? (basically keeping the CTE as lean as possible).

Graz?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.81 seconds. Powered By: Snitz Forums 2000