A
Anonieko Ramos
In ASPNET 1.1
Is there a good way to page/sort VERY large Result set in datagrid
" Here's a stored procedure that I use for sorting and paging large
recordsets in SQL Server, as opposed to using the more common and
terribly inefficient entire dataset approach in .NET. It certainly
doesn't matter much in the little classroom examples of a few hundred,
or even thousands, of records, but working with larger recordsets with
datasets is just bad. This is even more true with ASP.NET, since the
entire dataset is usually saved in viewstate, and since the web server
is doing the processing for many users. This stored procedure is
certainly not the only way to do sorting and paging in SQL Server, but
it is probably the most efficient, although proper indexing is still
required to avoid table scans. Note that this does not use temporary
tables, which are convenient but not as optimal -- this also means you
could modify this to be dynamic SQL for Access or other databases!
The parameters are the name of the table, the name of the primary key
(necessary for the little bit of optimization included), the name of
the sort field (or sort fields, with or without ASC/DESC), and the
page size (number of records). It also allows optional parameters for
the page index (defaults to 1st page) and a query filter (defaults to
null) so you can sort and page through filtered records only! Note
that it returns two recordsets -- the first is the results you expect,
and the second recordset is a scalar with the number of total pages so
you can better define the GUI pager for a grid. This is relatively
easy to integrate with the ASP.NET datagrid if you use custom paging
and sorting, and it will minimize both your load on the server and the
amount of data sent to the client's browser! By the way, this code is
just modified from some I found on the net, and there are certainly
some minor optimizations that can be done, like using different sql
for the first page.
"
CREATE PROCEDURE GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON
DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)
SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' +
@TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' +
@TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount
FROM ' + @TableName)
END
ELSE
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' +
@TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT
IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' +
@TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount
FROM ' + @TableName + ' WHERE ' + @QueryFilter)
END
RETURN 0
GO
posted on Friday, October 10, 2003 9:04 AM
Comments
# re: Sorting and Paging Recordsets in SQL Server
Darrell
Posted @ 10/10/2003 10:15 AM
* Just taking a quick look, you could change the "SELECT Count(*)"
to "SELECT Count(@PrimaryKey)" and that should give you a speed boost.
Reducing the number of columns in the select statement always speeds
things up, even for aggregate operations like Count. Very interesting
though; I will have to see how well it works for complex queries.
# re: Sorting and Paging Recordsets in SQL Server
Torbjörn Axelsson
Posted @ 10/27/2003 11:14 AM
Very interesting! Right now I'm using a solution based on
creating a temporary table in the stored procedure for selecting rows
from a table containing over a million rows. I will test to implement
this solution and see if it boosts performance, which would be very
welcome
In my project I will have to make an inner join to retrieve User
names from another table, but since I only have to do this on the
outermost SELECT-statement (after TableName), and I have a good
indexed structure, that shouldn't have a big impact on performance.
Any thoughts?
*
# Recordset sorting in SQL Server
Jamie Jones
Posted @ 11/16/2003 11:05 AM
*
# O/R Mappers: Simple Database Features ?
Paul Wilson's .NET Blog
Posted @ 1/9/2004 3:54 PM
*
# re: Sorting and Paging Recordsets in SQL Server
David Portas
Posted @ 1/23/2004 5:42 PM
See also Aaron Bertrand's comprehensive survey of techniques for
paging a result set in SQLServer:
http://www.aspfaq.com/show.asp?id=2120
*
# re: Sorting and Paging Recordsets in SQL Server
HELP!
Posted @ 2/11/2004 7:34 PM
what's the asp supposed to look like?
*
# re: Sorting and Paging Recordsets in SQL Server
Mark
Posted @ 3/6/2004 8:49 AM
I was wondering if you had an example of using this code with an
asp.net datagrid, complete with paging and sorting. email:
(e-mail address removed)
*
# re: Sorting and Paging Recordsets in SQL Server
Paul Wilson
Posted @ 3/6/2004 11:28 AM
I use it internally at work on a project, but that's not an
example I can share. I also use this, but wrapped up in my
WilsonORMapper, on my site, but there I use repeaters since I don't
like datagrids. There are lots of other examples that show you the
asp.net code that you want, so I haven't bothered to recreate one yet
again -- the only thing I'm pointing out is how to set up the stored
procedure in a better way.
*
# re: Sorting and Paging Recordsets in SQL Server
babu
Posted @ 4/2/2004 5:26 AM
quite helpful - babu
*
# re: Sorting and Paging Recordsets in SQL Server
Bob
Posted @ 4/5/2004 5:08 AM
Sorry, I'm new to sql. Why the extra "SELECT * FROM ~ IN ~"
statement?
Why not just do a "SELECT TOP ' + @SizeString + ' * FROM ..."
and make the second select the top select (if you see what I mean).
Wouldn't this have the same affect?
*
# re: Sorting and Paging Recordsets in SQL Server
SANDESH
Posted @ 4/6/2004 3:25 AM
GOOD ONE
*
# re: Sorting and Paging Recordsets in SQL Server
(e-mail address removed)
Posted @ 4/17/2004 7:47 PM
Hi! Excellent fantastic work! You've made my day as I was
experiencing with paging scripts but the only solution I came up with
supporting DESC and filtering doesn't perform very well. I probably
would have spend the next days coming up with something like yours.
Thanks very much!
*
# re: Sorting and Paging Recordsets in SQL Server
AcidJazz
Posted @ 4/29/2004 8:11 PM
Great stuff!
I spent last couple of hours looking for something similar and I
could see this proc working very well in my project.
Thanks!
*
# re: Sorting and Paging Recordsets in SQL Server
senkwe
Posted @ 7/8/2004 9:34 AM
Has anybody tried this for tables with a few million rows yet?
Thanks.
*
# re: Sorting and Paging Recordsets in SQL Server
Paul Wilson
Posted @ 7/8/2004 9:42 AM
I actually just got done reading
http://codeproject.com/aspnet/paginglarge.asp -- and I may switch what
I use. I've never heard any complaints, but it does look like there
are better solutions.
*
# re: Sorting and Paging Recordsets in SQL Server
Jonny
Posted @ 7/21/2004 7:29 AM
I believe I read somewhere the sp_executesql will compile the
dynamic query and create an execution plan for it. This may speed this
up a bit if the same exact query is executed.
*
# Taking the value of PageCount
Resa
Posted @ 7/27/2004 1:59 AM
Excuse me but i have never used a stored procedure before, and
although your code works great (thanks a lot by the way) i want to
ask how can i get the PageCount value from the second table generated
by ur stored procedure? Thank you very much in advance
*
# re: Sorting and Paging Recordsets in SQL Server
Paul Wilson
Posted @ 7/27/2004 5:59 AM
Assuming you're using .NET and a DataReader then the NextResult
method is what you want. If you are using a .NET DataSet then you will
look at the second table, i.e. .Tables[1].
*
# re: Sorting and Paging Recordsets in SQL Server
Resa
Posted @ 8/2/2004 2:06 AM
I'm using sql server, and the NextResult method doesn't work, is
there another way, i mean using sql server? thanks
Is there a good way to page/sort VERY large Result set in datagrid
" Here's a stored procedure that I use for sorting and paging large
recordsets in SQL Server, as opposed to using the more common and
terribly inefficient entire dataset approach in .NET. It certainly
doesn't matter much in the little classroom examples of a few hundred,
or even thousands, of records, but working with larger recordsets with
datasets is just bad. This is even more true with ASP.NET, since the
entire dataset is usually saved in viewstate, and since the web server
is doing the processing for many users. This stored procedure is
certainly not the only way to do sorting and paging in SQL Server, but
it is probably the most efficient, although proper indexing is still
required to avoid table scans. Note that this does not use temporary
tables, which are convenient but not as optimal -- this also means you
could modify this to be dynamic SQL for Access or other databases!
The parameters are the name of the table, the name of the primary key
(necessary for the little bit of optimization included), the name of
the sort field (or sort fields, with or without ASC/DESC), and the
page size (number of records). It also allows optional parameters for
the page index (defaults to 1st page) and a query filter (defaults to
null) so you can sort and page through filtered records only! Note
that it returns two recordsets -- the first is the results you expect,
and the second recordset is a scalar with the number of total pages so
you can better define the GUI pager for a grid. This is relatively
easy to integrate with the ASP.NET datagrid if you use custom paging
and sorting, and it will minimize both your load on the server and the
amount of data sent to the client's browser! By the way, this code is
just modified from some I found on the net, and there are certainly
some minor optimizations that can be done, like using different sql
for the first page.
"
CREATE PROCEDURE GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON
DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)
SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' +
@TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' +
@TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount
FROM ' + @TableName)
END
ELSE
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' +
@TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT
IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' +
@TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount
FROM ' + @TableName + ' WHERE ' + @QueryFilter)
END
RETURN 0
GO
posted on Friday, October 10, 2003 9:04 AM
Comments
# re: Sorting and Paging Recordsets in SQL Server
Darrell
Posted @ 10/10/2003 10:15 AM
* Just taking a quick look, you could change the "SELECT Count(*)"
to "SELECT Count(@PrimaryKey)" and that should give you a speed boost.
Reducing the number of columns in the select statement always speeds
things up, even for aggregate operations like Count. Very interesting
though; I will have to see how well it works for complex queries.
# re: Sorting and Paging Recordsets in SQL Server
Torbjörn Axelsson
Posted @ 10/27/2003 11:14 AM
Very interesting! Right now I'm using a solution based on
creating a temporary table in the stored procedure for selecting rows
from a table containing over a million rows. I will test to implement
this solution and see if it boosts performance, which would be very
welcome
In my project I will have to make an inner join to retrieve User
names from another table, but since I only have to do this on the
outermost SELECT-statement (after TableName), and I have a good
indexed structure, that shouldn't have a big impact on performance.
Any thoughts?
*
# Recordset sorting in SQL Server
Jamie Jones
Posted @ 11/16/2003 11:05 AM
*
# O/R Mappers: Simple Database Features ?
Paul Wilson's .NET Blog
Posted @ 1/9/2004 3:54 PM
*
# re: Sorting and Paging Recordsets in SQL Server
David Portas
Posted @ 1/23/2004 5:42 PM
See also Aaron Bertrand's comprehensive survey of techniques for
paging a result set in SQLServer:
http://www.aspfaq.com/show.asp?id=2120
*
# re: Sorting and Paging Recordsets in SQL Server
HELP!
Posted @ 2/11/2004 7:34 PM
what's the asp supposed to look like?
*
# re: Sorting and Paging Recordsets in SQL Server
Mark
Posted @ 3/6/2004 8:49 AM
I was wondering if you had an example of using this code with an
asp.net datagrid, complete with paging and sorting. email:
(e-mail address removed)
*
# re: Sorting and Paging Recordsets in SQL Server
Paul Wilson
Posted @ 3/6/2004 11:28 AM
I use it internally at work on a project, but that's not an
example I can share. I also use this, but wrapped up in my
WilsonORMapper, on my site, but there I use repeaters since I don't
like datagrids. There are lots of other examples that show you the
asp.net code that you want, so I haven't bothered to recreate one yet
again -- the only thing I'm pointing out is how to set up the stored
procedure in a better way.
*
# re: Sorting and Paging Recordsets in SQL Server
babu
Posted @ 4/2/2004 5:26 AM
quite helpful - babu
*
# re: Sorting and Paging Recordsets in SQL Server
Bob
Posted @ 4/5/2004 5:08 AM
Sorry, I'm new to sql. Why the extra "SELECT * FROM ~ IN ~"
statement?
Why not just do a "SELECT TOP ' + @SizeString + ' * FROM ..."
and make the second select the top select (if you see what I mean).
Wouldn't this have the same affect?
*
# re: Sorting and Paging Recordsets in SQL Server
SANDESH
Posted @ 4/6/2004 3:25 AM
GOOD ONE
*
# re: Sorting and Paging Recordsets in SQL Server
(e-mail address removed)
Posted @ 4/17/2004 7:47 PM
Hi! Excellent fantastic work! You've made my day as I was
experiencing with paging scripts but the only solution I came up with
supporting DESC and filtering doesn't perform very well. I probably
would have spend the next days coming up with something like yours.
Thanks very much!
*
# re: Sorting and Paging Recordsets in SQL Server
AcidJazz
Posted @ 4/29/2004 8:11 PM
Great stuff!
I spent last couple of hours looking for something similar and I
could see this proc working very well in my project.
Thanks!
*
# re: Sorting and Paging Recordsets in SQL Server
senkwe
Posted @ 7/8/2004 9:34 AM
Has anybody tried this for tables with a few million rows yet?
Thanks.
*
# re: Sorting and Paging Recordsets in SQL Server
Paul Wilson
Posted @ 7/8/2004 9:42 AM
I actually just got done reading
http://codeproject.com/aspnet/paginglarge.asp -- and I may switch what
I use. I've never heard any complaints, but it does look like there
are better solutions.
*
# re: Sorting and Paging Recordsets in SQL Server
Jonny
Posted @ 7/21/2004 7:29 AM
I believe I read somewhere the sp_executesql will compile the
dynamic query and create an execution plan for it. This may speed this
up a bit if the same exact query is executed.
*
# Taking the value of PageCount
Resa
Posted @ 7/27/2004 1:59 AM
Excuse me but i have never used a stored procedure before, and
although your code works great (thanks a lot by the way
) i want toask how can i get the PageCount value from the second table generated
by ur stored procedure? Thank you very much in advance
*
# re: Sorting and Paging Recordsets in SQL Server
Paul Wilson
Posted @ 7/27/2004 5:59 AM
Assuming you're using .NET and a DataReader then the NextResult
method is what you want. If you are using a .NET DataSet then you will
look at the second table, i.e. .Tables[1].
*
# re: Sorting and Paging Recordsets in SQL Server
Resa
Posted @ 8/2/2004 2:06 AM
I'm using sql server, and the NextResult method doesn't work, is
there another way, i mean using sql server? thanks