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
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Does Yukon support paging recordsets
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 6

SamC
White Water Yakist

USA
3459 Posts

Posted - 01/10/2004 :  17:54:03  Show Profile
Top N is sorta primitive and leads to complexity in stored procedures when the Nth page of M records is needed.

Seems like an easy thing to improve by extending

SELECT TOP N ColumnA, ColumnB

to something like:

SELECT MID N M ColumnA, ColumnB -- Return M records starting at N

Yukon got anything like that? And why didn't Microsoft add it to SQL 2000?

Sam

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 01/10/2004 :  18:10:37  Show Profile  Visit robvolk's Homepage
Paging records is a function of client-side presentation, not data retrieval or processing. There's plenty of support for paging in almost every data access methodology used today. And since they are almost always database-independent I think there's little reason or benefit for the database to do something like it natively. As for "why" MS didn't include it, you'd have to ask them, but any method that provides paging will by necessity compromise performance, especially when you want to present the entire set of results.

Yukon will have support for Common Table Expressions (CTE's) that might make paging records easier, but whether it will be more efficient than client-side paging will be highly dependent on the nature of the application and the data.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/11/2004 :  12:07:32  Show Profile  Visit nr's Homepage
It's already there in

select top M * from (select top M+N ColumnA, ColumnB ... order by fld) a order by fld desc

Is anything else really needed?
I'm not in favour of adding functionality to the language unless it adds something to what's already available.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 01/11/2004 :  16:52:44  Show Profile
Hi Rob,

Are you suggesting that the right method of paging is to return the entire recordset to the client and let the client side find records from N through N+M? I thought it was generally recognized that this was inefficent for large recordsets.

Sam
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 01/11/2004 :  17:11:48  Show Profile
Hi Nigel,

Your suggestion isn't too bad at all for a single sort field, but it gets much more comples if there are a number of optional sort columns. In my worse case, I have a query that supports about 12 orderable columns, all are optional and can be ascending or descenting too. 3 ORDERED columns can be specified in a single query. The dynamic ORDER BY is pretty extensive as it is.

I'll experiment with it to see how it looks relative to the temporary table solution.

Sam
Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 01/12/2004 :  17:57:22  Show Profile  Visit jasper_smith's Homepage
You can combine a CTE with one of the new ranking functions e.g. to return orders between 50 and 60 ordered by date
WITH OrderedOrders AS 
(select SalesOrderID, OrderDate,
Row_Number() OVER (order by OrderDate)as RN  
from SalesOrderHeader ) 

SELECT * from OrderedOrders WHERE RN between 50 and 60
Go to Top of Page

xpandre
Posting Yak Master

199 Posts

Posted - 04/06/2004 :  06:09:04  Show Profile
yeah..

I would love to see sql server with all those Oracle analytical functions..rank,lag,lead..etc..n even GROUPING BY !!!
Go to Top of Page

Billpl
Yak Posting Veteran

USA
71 Posts

Posted - 04/11/2004 :  03:30:36  Show Profile
How about simple little RowNumb() function like Oracle, is that too much to ask? Then MS won't have to hear all of us mortals cry about paging.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 04/11/2004 :  10:39:49  Show Profile  Visit robvolk's Homepage
Who's crying? ADO and ADO.Net handle paging very nicely.
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 04/11/2004 :  10:47:55  Show Profile
I understood that retrieving 15 records out of a 15 million recordset was inefficent in ADO and ADO.NET. Isn't that what paging on the server side resolves?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 04/11/2004 :  14:10:07  Show Profile  Visit robvolk's Homepage
Yeah, it is. But as I said, paging is a client presentation issue, not a server issue. The real problem is that you're trying to find a subset of data from another set you define, and it's not based upon values in the set but on their ordinal position. Since relational data doesn't care about ordinal position, you are left with a) refining the query to retrieve fewer rows, or b) getting all of that data to a client that can process it and display only the rows it needs.

I'm not trying to be pedantic, but there's no magic bullet for this. You can certainly use any of the row numbering techniques you can find on SQL Team to make the server side a little more efficient, but that only masks the real problem. Frankly, why would you be returning 15 million rows to a client anyway? Don't say that's only for the purposes of the example. Unless you have some elaborate caching going on, if you are searching a large resultset for another page of the same data, paging it on the server means running the same query again. This will ultimately be less efficient than getting however much of the data the query will deliver and having another mechanism slice it up.

The point is, it seems everyone is trying to get smaller and smaller slices of enormous datasets when they should probably look at reducing the number of rows being returned in the first place. Sorry, but no one in their right mind is actually gonna search through even 10,000 results from a query, no matter how you page them. Doing a plain old SELECT TOP n or SET ROWCOUNT n to limit the rows is perfectly valid.
Go to Top of Page

Billpl
Yak Posting Veteran

USA
71 Posts

Posted - 04/12/2004 :  10:59:01  Show Profile
Are you trying to say?

An auditor won't browse through 400 pages of a petty cash ledger searching for a $12,000 mistake?
A police detective won't scan 5500 MO's for a armed robbery suspect?
A college student doesn't need to look though 3500 descriptions of articles for a term paper?
A Telemarketing system doesn't queue a couple of thousand phone numbers at a time?
A collections agents wouldn't bother to check up on 4000 credit card transactions trying to find a fraud pattern?
A mom wouldn't look trough 30 pages of sales on roller skates to find the best deal?
How about a 2700 post thread on a forum?
What about Yahoo, Google, Amazon or E-bay?
How many time have you seen: "first << 1,2,3,4,5,6,7,8,9,10...450, last >>" on a web page?

Sorry guys I'm just not buying into this at all. IMHO the only reason MS stayed away from paging was to separate SQL itself from the DBase styled "Pick and Roll" browsing applications that dominated PC's in the late 80's and early 90's. True, relational data doesn't care about ordinal positioning but users do.

Oh and good for ADO.Net, but that's a poor excuse, "... gee, the server and the wire are smoking but at least ADO.net can fire out a quick read...?!?" What kind of answer is that? Not to mention, what if you're using HttpXML or Cold Fusion with Flash Remoting or Java web services? Then you're back to rigging the SP's with silly little paging schemes just because MS doesn't "feel" right about a little RowNumb() function because it doesn't "fit" in with their relational theology. Give me a break....

Times have changed and developers need the tools to get the job done. I've yet to see a business system that doesn't have some kind of paging requirements and since SQL is at the heart of the data, it should be able to do the job.

(next thing you know, you guys will want to get rid of "Select * From..." ) hee, hee

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 04/12/2004 :  18:50:40  Show Profile  Visit jasper_smith's Homepage
But Yukon will have a Row_Number() function as I demonstrated in my earlier post. Is this not what you want ?
Go to Top of Page

Billpl
Yak Posting Veteran

USA
71 Posts

Posted - 04/12/2004 :  19:53:23  Show Profile
I'm not familiar with CTE. Isn't the WITH statement part Analysis Services? I haven't had the need to use Analysis Services and would think it's a stretch for just getting a limited return set, but hey... if it can float my boat?

I'm looking for simple stuff that Oracle can do like:

Select
RowNumb() as number,
LastName,
FirstName
From MyBigFatTable
Where LastName = 'Smith'
and Number > @RowStart
and Number < @RowEnd


If I can do that my job will be done in the morning and I can take the kids to Chuck-E-Cheez in the afternoon.
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 04/13/2004 :  12:34:35  Show Profile
quote:
relational data doesn't care about ordinal position


Between '@@rowcount' and 'ORDER BY' I think it has a passing affiliation with it.

quote:
Paging records is a function of client-side presentation, not data retrieval or processing.


In what way is asking for the second 10 records in a result set not a function of data retrieval? 'SELECT TOP 10' is in T-SQL so why not 'SELECT SECOND 10'?

Personally, as well as being able to request a particular page of data, I'd also like it to snap the retrieved page to the nearest non-empty set if I ask for a page of data that doesn't exist. (So it gives page 3 if I ask for page 5 and there's only 3 pages.)

Also, I'd like to be given information as to the total number of records in all the pages and I'd like all this to be done with miraculous efficiency.

Many new features in Microsoft's products are about absorbing common tasks into a pre-built framework. Paging is an obvious candidate. If relational theory prohibits this then the theory is inappropriate.





Edited by - X-Factor on 04/13/2004 12:52:23
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 04/13/2004 :  12:47:24  Show Profile
quote:
I'm not in favour of adding functionality to the language unless it adds something to what's already available.



But sometimes its useful to be able to do something with a simpler syntax.

For example, 'BETWEEN' and 'IN' can both be replaced with primitive comparison operators.
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 04/14/2004 :  06:40:32  Show Profile
in robs defence (and he's big and brave enough not to need anybody to stand up for him on this one)....

"An auditor won't browse through 400 pages of a petty cash ledger searching for a $12,000 mistake?
A police detective won't scan 5500 MO's for a armed robbery suspect?
A college student doesn't need to look though 3500 descriptions of articles for a term paper?
A Telemarketing system doesn't queue a couple of thousand phone numbers at a time?
A collections agents wouldn't bother to check up on 4000 credit card transactions trying to find a fraud pattern?
A mom wouldn't look trough 30 pages of sales on roller skates to find the best deal?
How about a 2700 post thread on a forum?
What about Yahoo, Google, Amazon or E-bay? "


The answer to all these....is no.....they'll review the 1st 1/2%....and then revise their original query to be more precise.....remember these are people looking to be efficient.
searching "through 400 pages of a petty cash ledger searching for a $12,000 mistake?" manually is not efficient....it may be a 12k mistake....but if it costs 500 to find it, the boss isn't going to be too pleased with the accountant's work practices.

also....how many students do you know will read 3500 descriptions for a term paper....?
or mom's who will read 30 pages of sales on roller skates to save the final 25cents?
and has anybody ever gone and reviewed the each and every page returned on a google search term....?

Nobody has the time to do these efficiently in the manner your proscribe and time in this case has a direct correlation on effectiveness....so what happens is that the large datasets get respecified....which is exactly what rob is advising gets done.



Rob's key point is the ordinal position of the returned data .... and as SQL has a (current) basic tenet of not being concerned with the ordinal of the records of any input data set....putting an ordinal on the output a query....would imply that it should apply an ordinal on the input...(as for example derived tables are an input to other queries)....which clashes with the principle of relational data that SQL is following.

Edited by - AndrewMurphy on 04/14/2004 06:43:47
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 04/14/2004 :  11:35:53  Show Profile
There's a new method on the ado.net v2 datareader called 'ExecutePageReader'.

Here's an example ... http://www.ipona.com/samples/8391/ch02/paging-datareader.aspx

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 04/16/2004 :  12:39:15  Show Profile  Visit jasper_smith's Homepage
quote:
I'm not familiar with CTE. Isn't the WITH statement part Analysis Services?
Nope CTE (Common Table Expression) can be thought of as a named result set or temporary view that exists for the lifetime of the batch. The query I posted is just straight TSQL. The Row_Number() function just adds a new column to the CTE with the order defined in the OVER clause which can then be used in the same batch. You'll also find RANK,DENSE_RANK and NTILE functions are available in Yukon

HTH
Jasper Smith
Go to Top of Page

00kevin
Yak Posting Veteran

78 Posts

Posted - 04/30/2004 :  10:53:41  Show Profile
this feature is available in MySql. there is a LIMIT clause
select * FROM table LIMIT start, limit

sql server should just add the LIMIT clause.
Go to Top of Page

00kevin
Yak Posting Veteran

78 Posts

Posted - 04/30/2004 :  11:01:29  Show Profile
I have developed a sql server sp to handle paging
ALTER PROCEDURE dbo.spGetPage
(
@Page bigint = 1,
@RowsPerPage bigint, -- a value of -1 will return all the records
@KeyField varchar(50), -- unique key field required for sorting and IN clause
@SelectFields varchar(2000), -- do not pass 'SELECT' it will be prepended
@TableName varchar(50),
@Where varchar(2000), -- do not pass 'WHERE' it will be prepended
@OrderBy varchar(2000) -- do not pass 'ORDER BY' it will be prepended
)
AS


I'm not going to give you the rest though as it is my partner in crime and I use it for all my applications. It is super fast too.

all I have to say is that paging is a MAJOR issue with every application I have built thus far. All my clients want paging functionality. Because of this Yukon should come with a built in method to hanlde paging and it should be damn fast.

Go to Top of Page
Page: of 6 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.47 seconds. Powered By: Snitz Forums 2000