Getting a custom sized page from a sorted result set

Hi!
I've tried numerous solutions to this classic problem. I know how to do a
pretty scalable solution, but I'd like to hear some more ideas.
What I do now is something like this:
1. Declare a cursor inside an exec statement since order by doesn't support
variables
(I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
code)
2. Create a temporary table identical to the result set
3. Declare variables for all fields
4. Fetch from cursor [pagesize] times from [start] while inserting into
variables and then temporary table
5. Select from the temp table
6. Return total count of the result set for the pager mechanism
The main challenges I've had is how to fetch the correct page. In SQL Server
2005 I thought my troubles were gone since we got the new TOP(@.parameter)
feature, but when it comes to sorting it's still impossible.
Anyway, the demands are
1. Fast browsing of 5000+ rows big result sets
2. Sorting by any column
3. User defined page size
One alternative I'm considering is use a query returning the entire result
set, and then using a reader instead in .net, but the amount of data fetched
will still be too much when you reach the last page. I'm not sure though if
the sql injection removal code, the exec statement and the cursor will use
just as much resources... Should measure it someday, but if anyone allready
did it, I'd appreciate a link. :P
Anyone got a better solution than the one on top?
(Some MySQL fans I know mock me because they've got LIMIT, I want to hit
back.. ;) )
Lars-ErikIf I understand correctly, you want to retrieve N rows, starting from some
point in the result set.
You can solve part of the problem with set rowcount. You can use variables
so it can be passed to the stored procedure.
for example:
create procedure usp_GetPage
(
@.Number int
)
as
set rowcount @.number
select col1, col2
from table1
-- This will return @.Number rows from the table.
Paging is, offcourse more complicated but perhaps this helps a bit? The rest
is entirely up to the way you want to implement ordering and what would be
the criteria for defining pages (starting point and such)
MC
"Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
news:eF4a%23UQ5FHA.2864@.tk2msftngp13.phx.gbl...
> Hi!
> I've tried numerous solutions to this classic problem. I know how to do a
> pretty scalable solution, but I'd like to hear some more ideas.
> What I do now is something like this:
> 1. Declare a cursor inside an exec statement since order by doesn't
> support variables
> (I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
> WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
> code)
> 2. Create a temporary table identical to the result set
> 3. Declare variables for all fields
> 4. Fetch from cursor [pagesize] times from [start] while inserting into
> variables and then temporary table
> 5. Select from the temp table
> 6. Return total count of the result set for the pager mechanism
> The main challenges I've had is how to fetch the correct page. In SQL
> Server 2005 I thought my troubles were gone since we got the new
> TOP(@.parameter) feature, but when it comes to sorting it's still
> impossible.
> Anyway, the demands are
> 1. Fast browsing of 5000+ rows big result sets
> 2. Sorting by any column
> 3. User defined page size
> One alternative I'm considering is use a query returning the entire result
> set, and then using a reader instead in .net, but the amount of data
> fetched will still be too much when you reach the last page. I'm not sure
> though if the sql injection removal code, the exec statement and the
> cursor will use just as much resources... Should measure it someday, but
> if anyone allready did it, I'd appreciate a link. :P
> Anyone got a better solution than the one on top?
> (Some MySQL fans I know mock me because they've got LIMIT, I want to hit
> back.. ;) )
> Lars-Erik
>|||http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||Thanks guys :)
Both relevant info! Didn't know about the @.rowcount setting, and the
measures on the faq page was pretty interresting.
L-E
"Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
news:eF4a%23UQ5FHA.2864@.tk2msftngp13.phx.gbl...
> Hi!
> I've tried numerous solutions to this classic problem. I know how to do a
> pretty scalable solution, but I'd like to hear some more ideas.
> What I do now is something like this:
> 1. Declare a cursor inside an exec statement since order by doesn't
> support variables
> (I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
> WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
> code)
> 2. Create a temporary table identical to the result set
> 3. Declare variables for all fields
> 4. Fetch from cursor [pagesize] times from [start] while inserting into
> variables and then temporary table
> 5. Select from the temp table
> 6. Return total count of the result set for the pager mechanism
> The main challenges I've had is how to fetch the correct page. In SQL
> Server 2005 I thought my troubles were gone since we got the new
> TOP(@.parameter) feature, but when it comes to sorting it's still
> impossible.
> Anyway, the demands are
> 1. Fast browsing of 5000+ rows big result sets
> 2. Sorting by any column
> 3. User defined page size
> One alternative I'm considering is use a query returning the entire result
> set, and then using a reader instead in .net, but the amount of data
> fetched will still be too much when you reach the last page. I'm not sure
> though if the sql injection removal code, the exec statement and the
> cursor will use just as much resources... Should measure it someday, but
> if anyone allready did it, I'd appreciate a link. :P
> Anyone got a better solution than the one on top?
> (Some MySQL fans I know mock me because they've got LIMIT, I want to hit
> back.. ;) )
> Lars-Erik
>