| Author |
Topic |
|
slacker
Posting Yak Master
115 Posts |
 Posted - 2003-02-03 : 05:09:11
|
| This is mainly just a rant. I know that you need to use a temp table to retrieve the rownumber from a result set. But there is something that just really really bothers me about this. Im no super computer programmer person who codes kickass rdbms systems in assembler from scratch. But how hard would it be to simply add a variable and count that sucker and allow a variable like oracles rownum... I mean both mysql, and oracle support this feature... (mysql has limit)It just seems to me that microsoft is forcing the client software to do the paging of results. Which makes absolutely no sense in a server side app like asp where performance is critical. If you have 10000 or more records you need to page... Why force a client side cursor to handle it? Why not just pass the maxrecords and pagenumber to a stored proc and let the db return only the results you need. This saves so much on both ends... Coding wise and performance wise. I know you can use a temp table... but then you have unnecessary work... and performance will suffer with lots and lots of records. |
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-02-03 : 05:16:33
|
| I guess I would be happy if there were some real reason it didnt support it. Like it used some optimization feature or query caching deal that made it impossible for the programmers to implement a rownum. I see the other rdbms's with it... and I just dont see why ms-sql doesnt. Its almost like its politics. Like they did it on purpose so that the client machines would be forced to handle the paging and we would have to buy beefier machines from intel in order to do it. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-03 : 08:35:50
|
| I'm not a mind reader, but I assume they "did it" because they are trying to adhere to relational database standards, which DO NOT describe any concept of numbering or position of rows returned in a query. A set is a set, it is NOT a collection of rows. Rows are part of a set. Data is identified by its values, nothing else. Any product that does provide a row number is ignoring these tenets, and may also be ignoring others as well. It would help if you didn't think of your data as being presented to you in a spreadsheet format that you can browse at leisure (even though that is how you're using it) The data does not change no matter what particular order you put it in. As an exercise, open a spreadsheet of data in Excel, go to the Options tab, and hide the row and column headings. Sort the data a few times. Does anything change from being sorted, and not having a row number? No, it doesn't.Think of a game of musical chairs: the chairs are the same, no matter how they are arranged; and the players are the same people, no matter which chair they happen to sit in. It would be absurd if a player's name changed depending on which chair they sat in, it would be equally absurd for your data to have a different meaning just because it is in the 10th row instead of the 11th.And you can have your web server page a recordset for display, ADO makes this very easy, and you don't need a temp table if you don't want to use one. Search SQL Team for "paging" and you'll find a number of links. Also look for "What's after TOP?", it has a method for simulating LIMIT in SQL Server. There are rather quite a number of ways of doing this in fact, far more than are available in Oracle or MySQL.And yes, paging/displaying records IS a client function. Always will be. Any argument to the contrary is on the same level as "Why doesn't SQL Server have built-in reports and data entry forms like Access does?" |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-02-03 : 17:37:30
|
| I agree with your analagy with the musical chairs bit. The rownumber has absolutely nothing to do with the result set. Ive read the article you gave me and several other to try and achieve this. In fact this is the site where i got the temporary table idea for the rownumber. ( Btw.. This site is great. I learn new tricks here all the time. ). The problem is... once you get alot of records all of these workarounds end up being too much for ms-sql.ADO cursors.. They are great.. But they are slow as hell. I have created a much faster solution using rs.getrows and rs.move(). Its a pain to do but it makes the paging much faster. Im coding asp.net now and things are much better. Caching is available and most of the paging stuff is handled for me quite nicely. But still. On a database that gets really large performace will still suffer. The client has to retrieve unecessary records. Even if its only using 30 records.Example: Say you want page 9000 and your displaying 30 at a time. The webserver has to download 18000 records just to get to that page. At first glance this may not seem much... but if you start gettings lots of hit the resources will add up. So why not have ms-sql just return the 30 records... It saves network resources and client resources. Ms-sql is still going to have to process the 18000 records anyways... why not only return the ones that are needed. |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-02-03 : 17:47:29
|
| sorry not 18000, 180000. lol. |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-02-03 : 17:48:35
|
| Man im really stupid.. I should just quit while im ahead. ( breaks out ms calc. )9000 x 30 = 270000 |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-02-03 : 18:03:42
|
| Im sorry for all these posts... But I just keep thinking of new stuff to add....You say that ms is trying to adhere to the relational database standards... Well ms-sql has many options that do not adhere to the ANSI standards that are like switches you can turn on and off. They do this to make extra features for the programmers. Like the cursors... they have have an ansi standard cursor and they also have their custom ms-sql cursors you can declare. Rownumbers IMHO should be a "feature"... even though it doesnt fully comply it makes perfect sense to do so for performance reasons and development reasons. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-02-03 : 18:18:09
|
| Say you have rows numbered 1 to 100.You display 10 rows at a time.What happens if someone inserts a row that should be row number 1, or edits a row so that it becomes "row 1"What do you do? That's what always bothered me about paging.Maybe that's why SQL Server doesn't have rownumbering, because some folks would want to get the "new" row 1, others would want to keep the "old row 1" because that the "snapshot" of data you starting looking at.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-02-03 : 18:30:41
|
| You would still have the same problem if the client sorts it. Thats more of an application design issue and what you want your users to see. If you add aaron smith to a name and your sorting by firstname then when you refresh the data the user would expect aaron smith to show up because thats how they are sorting. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-02-03 : 19:10:48
|
quote:
You say that ms is trying to adhere to the relational database standards... Well ms-sql has many options that do not adhere to the ANSI standards that are like switches you can turn on and off
The ANSI SQL standard, AFAIK, is not "properly" implemented in any commericial DBMS. And to make matters worse, the standard allows non-relational "features" ie NULL, Duplicates etc..Personally, I'd be happy with just the relational features.Perhaps you could simulate the other products "row number", by adding your own attribute.DavidM"SQL-3 is an abomination.." |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2003-02-03 : 19:44:51
|
quote:
Example: Say you want page 9000 and your displaying 30 at a time. The webserver has to download 18000 records just to get to that page. At first glance this may not seem much... but if you start gettings lots of hit the resources will add up. So why not have ms-sql just return the 30 records... It saves network resources and client resources. Ms-sql is still going to have to process the 18000 records anyways... why not only return the ones that are needed.
MS-SQL will return just the 30 (or whatever # of records) to the webserver. Here is just one example of many ways to page with SQL Server (this is based on the "Whats After Top" that Rob previously mentioned) that only returns the final resultset to the client. @CurrentPage int, @Pages int, @Remainder int, @Where nvarchar(2000)asset nocount ondeclare @SQL nvarchar(4000)select @SQL = case when @CurrentPage = 1 then 'select top 25 TheatreID, Company, TheatreName, PCity, PState, PZip, PriPhone from dba0082.TheatresAndContacts ' + @Where + ' order by TheatreID' when @CurrentPage > 1 and @CurrentPage <= @Pages then 'select top 25 * from (select top 25 * from (select top ' + cast((25 * @CurrentPage) as varchar(8)) + ' TheatreID, Company, TheatreName, PCity, PState, PZip, PriPhone from dba0082.TheatresAndContacts ' + @Where + ' order by TheatreID asc) t1 order by TheatreID desc) t2 order by TheatreID asc' when @CurrentPage = @Pages + 1 and @Remainder > 0 then 'select top ' + cast(@Remainder as varchar(5)) + ' * from (select top ' + cast(@Remainder as varchar(5)) + ' TheatreID, Company, TheatreName, PCity, PState, PZip, PriPhone from dba0082.TheatresAndContacts ' + @Where + ' order by TheatreID desc) t3 order by TheatreID asc' endexec sp_executesql @SQL quote:
I mean both mysql, and oracle support this feature... (mysql has limit)
Yes, because they are both glorified ISAMs.JustinExpect 0x80040106Edited by - justinbigelow on 02/03/2003 19:46:08 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-03 : 21:18:55
|
quote:
You would still have the same problem if the client sorts it. Thats more of an application design issue and what you want your users to see. If you add aaron smith to a name and your sorting by firstname then when you refresh the data the user would expect aaron smith to show up because thats how they are sorting.
You may not know it, but you just answered your own question about why SQL Server does not have a row number "feature" (the red part especially, and the bold too) It is entirely an application issue, and as others have pointed out, there are enough issues with trying to do this kind of feature on the server side that it most likely cause as many problems as it solves. And as Justin pointed out, Oracle and MySQL really aren't relationally oriented databases either, certainly less so than SQL Server, for all its faults and limitations.No offense intended, but it's pretty pointless to complain about a feature SQL Server doesn't have that other products do. Either use those other products, or work with what SQL Server CAN do and make it work for you. |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-02-03 : 22:41:11
|
quote:
No offense intended, but it's pretty pointless to complain about a feature SQL Server doesn't have that other products do. Either use those other products, or work with what SQL Server CAN do and make it work for you.
None taken you are right I dont mean to sound hostile. It just bugs me. Surely they must have thought of adding it as the other rdbm's did ( Btw.. I wouldnt consider oracle second rate. ) I love ms-sql and I wouldnt use any other. And with asp.net I can use caching to take care nearly all performance issue's related to database. Its just when the result sets get really large. And then you add client side sorting on top of it. Then I have the problem.quote:
there are enough issues with trying to do this kind of feature on the server side that it most likely cause as many problems as it solves.
Do you know this for sure? This is what im trying to find out. JustinBigelow:If you read my previous posts we covered those examples. There are many ways to "simulate" rownumbers but they become problematic for performance when you get large tables to sort and page. No matter if you do on the client or the server. Thats why the rownumber feature would be useful.Anyways. Like I said... I dont really care... this is an old issue for me. Ive been coding asp for three years and I have found many workarounds. Except of course when the db gets too large. Then we gotta buy more hardware.</rant> |
|
|
|
|
RowNumber in a select query. (Rant)