| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-04 : 09:26:23
|
| 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
3564 Posts |
Posted - 2007-01-05 : 09:07:52
|
Nice, much simpler than SQL 2k. Thanks for the example as well.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-05 : 09:27:17
|
| Agreed. Thanks for the nice article, Graz -- you beat me to it on that one!- Jeff |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-01-10 : 15:24:23
|
I wrote up an example using Graz's code.The example basically uses a GridView control in ASP.net 2.0and 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 -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
sgtpusmc
Starting Member
2 Posts |
Posted - 2007-01-25 : 15:03:30
|
| 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! |
|
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2007-02-23 : 09:42:37
|
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 |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-08 : 11:30:50
|
| 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? |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-05-08 : 11:49:02
|
| 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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 12:38:11
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 12:43:43
|
| 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 |
|
|
|
sgtpusmc
Starting Member
2 Posts |
Posted - 2007-05-08 : 13:09:08
|
quote:
Originally posted by cas_oWrong 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! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-08 : 14:18:33
|
quote:
Originally posted by sgtpusmc
quote:
Originally posted by cas_oWrong 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 14:54:07
|
"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 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-08 : 15:11:56
|
| 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Kristen
Test
22859 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-08 : 15:34:03
|
| 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 08:56:52
|
"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 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-06-18 : 10:13:22
|
| Is this method any faster than creating a table variable? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-18 : 10:32:25
|
| 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 LotsOfOtherTablesWHERE VeryComplexStuffplusSELECT FancySQL2005SelectForJustPage5FROM MyTable JOIN LotsOfOtherTablesWHERE VeryComplexStuffis faster thanINSERT INTO @TempTableSELECT M.MyPKFROM MyTable AS M JOIN LotsOfOtherTablesWHERE VeryComplexStuffand then doing the Paging Stuff.Perhaps I should do a little test ...Kristen |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2008-01-14 : 12:57:49
|
| 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? |
|
|
|
Next Page
|
Article: Server Side Paging using SQL Server 2005