| 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, ColumnBto something like:SELECT MID N M ColumnA, ColumnB -- Return M records starting at NYukon 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. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-11 : 12:07:32
|
| It's already there inselect top M * from (select top M+N ColumnA, ColumnB ... order by fld) a order by fld descIs 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. |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 dateWITH OrderedOrders AS (select SalesOrderID, OrderDate,Row_Number() OVER (order by OrderDate)as RN from SalesOrderHeader ) SELECT * from OrderedOrders WHERE RN between 50 and 60 |
|
|
|
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 !!! |
|
|
|
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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-04-11 : 10:39:49
|
| Who's crying? ADO and ADO.Net handle paging very nicely. |
|
|
|
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? |
|
|
|
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. |
|
|
|
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 |
|
|
|
jasper_smith
SQL Server MVP & 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 ? |
|
|
|
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, FirstNameFrom MyBigFatTableWhere LastName = 'Smith' and Number > @RowStart and Number < @RowEndIf 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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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] |
|
|
|
jasper_smith
SQL Server MVP & 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 YukonHTHJasper Smith |
|
|
|
00kevin
Yak Posting Veteran
78 Posts |
Posted - 2004-04-30 : 10:53:41
|
| this feature is available in MySql. there is a LIMIT clauseselect * FROM table LIMIT start, limit sql server should just add the LIMIT clause. |
|
|
|
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)ASI'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. |
|
|
|
Next Page
|
Does Yukon support paging recordsets