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.
| Author |
Topic |
|
merry2
Starting Member
3 Posts |
 Posted - 2006-09-07 : 09:34:59
|
| Hi,I'm trying to return a resultset from a stored proc which could potentially produce hundreds or thousands of rows. In the UI we'll only show say 20 rows at a time. To prevent sending all the rows back to the client, I'm doing something like:SELECT sub1.* from (select col1, col2, col3, ROW_NUMBER() OVER (ORDER BY col1) AS 'rownum' from table1) sub1 WHERE sub1.rownum BETWEEN 30 AND 49where the BETWEEN values are passed into the SP as params.I want to somehow get the count of original rows prior to limiting the set so that in the UI I can say 'Rows 30-39 of 1000', but don't want to execute the sql twice or anything.I tried a count in the inner select but it gets a value of 1, where a max(rownum) in the outer select gets the rownum of that row, not the max for the whole set.Any ideas much appreciated.Thanks,Nick |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
merry2
Starting Member
3 Posts |
Posted - 2006-09-07 : 14:05:58
|
| Thanks for that, made for interesting reading, but unless I'm missing something, none of it gives me a total row count? Anyone else? |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-09-07 : 23:51:39
|
| I'd say don't do it. As you point out, you need to run a modified version of the query to obtain the count so essentially you're doubling the load on your server to achieve this.The other thing to bear in mind is that any on reasonably used system the rowcounts are changing by the second anyway, although I agree sometimes an indication of what you're up against would be nice.You could always get a page size of say 100, display only the first 10 or whatever and then simply display "x-y of over 100" if you get 100 back or "x-y of z" if you get fewer. |
|
|
|
merry2
Starting Member
3 Posts |
Posted - 2006-09-08 : 04:27:20
|
| Unfortunately that's not an option. The user needs to know how many rows there are in total as they can apply operations to a single selected row or to the entire result set. Anyone else? |
|
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2006-09-08 : 12:54:47
|
| Give these options a try - option 1Modify the stored proc to include one more resultset containing total count of records. Something like this - SELECT sub1.* from (select col1, col2, col3, ROW_NUMBER() OVER (ORDER BY col1) AS 'rownum' from table1) sub1 WHERE sub1.rownum BETWEEN 30 AND 49SELECT count(col1)FROM table1Bind the first resultset to table. Use the second result set for "Rows 30-39 of 1000".You can use nextresultset() method to get the second resultset. Or if you are using datasets, i think it should be another datatable.Option 2 select col1, col2, col3, ROW_NUMBER() OVER (ORDER BY col1) AS 'rownum' from table1 compute count(col1) gives the total count in second resultset. But we cannot have the outer query with this. If interested do some research on this. |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-09-08 : 16:58:07
|
| ROW_NUMBER() OVER doesn't nesseserely run through all the rows of the table if the ORDER BY can use an index (and it should). You never get something for nothing, not even with SQL, so if you need the count you have to do the count.The optimum solution (= the least work for the server) is to ask for the count (wether you like it or not) and then do the query you are already doing (and pray that no one is adding rows in the meantime). The paging logic you use is fairly expensive anyway (but unachievable otherwise), and a SELECT COUNT() is cheap.-- The Heisenberg uncertainty principle also applies when debugging |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-09-10 : 19:17:43
|
quote:
Originally posted by merry2
Unfortunately that's not an option. The user needs to know how many rows there are in total as they can apply operations to a single selected row or to the entire result set. Anyone else?
Then you'll have to count them. Of course what I said still stands - the number you put on the screen and the number when they actually perform the operation will not necessarily be the same in a multi user environment. |
|
|
|
|
|
|
|
|
Total count for restricted rowset