Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Does Yukon support paging recordsets

Author  Topic 

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-10 : 17:54:03
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

15732 Posts

Posted - 2004-01-10 : 18:10:37
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

12543 Posts

Posted - 2004-01-11 : 12:07:32
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

3467 Posts

Posted - 2004-01-11 : 16:52:44
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

3467 Posts

Posted - 2004-01-11 : 17:11:48
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

846 Posts

Posted - 2004-01-12 : 17:57:22
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

212 Posts

Posted - 2004-04-06 : 06:09:04
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

71 Posts

Posted - 2004-04-11 : 03:30:36
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

15732 Posts

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

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-11 : 10:47:55
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

15732 Posts

Posted - 2004-04-11 : 14:10:07
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

71 Posts

Posted - 2004-04-12 : 10:59:01
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 &amp; SQLTeam MVY

846 Posts

Posted - 2004-04-12 : 18:50:40
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

71 Posts

Posted - 2004-04-12 : 19:53:23
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 - 2004-04-13 : 12:34:35
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.




Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-04-13 : 12:47:24
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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-14 : 06:40:32
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.
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts

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

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

Go to Top of Page

jasper_smith
SQL Server MVP &amp; SQLTeam MVY

846 Posts

Posted - 2004-04-16 : 12:39:15
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 - 2004-04-30 : 10:53:41
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 - 2004-04-30 : 11:01:29
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
    Next Page

- Advertisement -