SQL Server Side Paging

Friday, February 29th, 2008

I was extremely excited about GridViews and DataGrids when I first started working with .NET. The thought of having controls with built in paging and sorting just blew me away. Needless to say, this excitement was short lived. By the end of my first project in .NET, I found that this built in sorting and paging had some major performance issues. It was too much data to send back and forth between the web server and the SQL server; not to mention that .NET just isn't as optimized to sort that much data as fast as SQL Server.

The alternative was to have SQL Server sort and page the data and only send back the data that was needed to the web server. After some research and some time to combine the ideas I found into my own solution I came up with:

 
CREATE PROCEDURE dbo.ReturnPage
(
  @SelectStmt varchar(1000), --Select Statement with Where Clause
  @OrderBy varchar(1000), --Order By Clause 'ASC' is required
  @StartRow int, --First Row of Page
  @EndRow int, --Last Row of Page
  @totRows int output --Output: Total Rows in Set
)
AS
 
DECLARE @ColList varchar(2000)
DECLARE @WhereCls varchar(2000)
DECLARE @i int
DECLARE @i2 int
DECLARE @tmp varchar(1000)
DECLARE @dec varchar(1000)
DECLARE @f varchar(100)
DECLARE @d varchar(100)
DECLARE @Symbol char(2)
DECLARE @SQL varchar(4000)
DECLARE @Sort varchar(1000)
DECLARE @tmpSQL NVARCHAR(4000)
 
--Assign the total number of rows available to @totRows for output
SET @SQL = N'SELECT @totRows = COUNT(*) FROM (' + @SelectStmt + ') tbl'
SET @tmpSQL = '' + @SQL
EXEC sp_executesql  @tmpSQL , N'@totRows int OUTPUT',
     @totRows = @totRows OUTPUT
 
SET @Sort = @OrderBy + ', '
SET @dec = ''
SET @WhereCls  = ''
SET @SQL = ''
 
SET @i = charindex(',' , @Sort)
 
--Pull apart the sort by clause for declarations
WHILE (@i != 0)
BEGIN
  SET @tmp = LEFT(@Sort,@i-1)
  SET @i2 = charindex(' ', @tmp)
  SET @f = ltrim(rtrim(LEFT(@tmp,@i2-1)))
  SET @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))
  SET @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
  SET @i = charindex(',', @Sort)
  SET @symbol = case when @d = 'ASC' then '>' else '< ' end +
          case when @i=0 then '=' else '' end
 
  SET @dec = @dec + 'declare @' + @f + ' sql_variant; '
  SET @ColList = isnull(REPLACE(REPLACE(@colList,'>','='),'< ','=') +
    ' and ','') + @f + @Symbol + ' @' + @f
  SET @WhereCls = @WhereCls + ' OR (' + @ColList + ') '
  SET @SQL = @SQL + ', @' + @f + '= ' + @f
 END
 
--Put together Transact SQL for final output
SET @SQL = @dec + ' ' +
  'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
  'SELECT ' + substring(@SQL,3,7000) + ' from ('
  + @SelectStmt + ') a ORDER BY ' +  @OrderBy + '; ' +
  'SET ROWCOUNT ' +
  convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
  'select * from (' + @SelectStmt + ') a WHERE ' +
  substring(@WhereCls,4,7000) + ' ORDER BY ' + @OrderBy +
  '; SET ROWCOUNT 0;'
 
EXEC(@SQL)
GO
 

What's it doing?

The procedure works by turning a simple query like:

 
SELECT col1, col2, col3, col4 FROM TABLE ORDER BY col1 ASC
 

and turning it into the following to add the paging.

 
DECLARE @col1 sql_variant
SET ROWCOUNT = @StartRow
SELECT @col1 = col1 FROM TABLE ORDER BY col1 ASC
SET ROWCOUNT = (@EndRow - @StartRow) + 1
SELECT col1, col2, col3, col4 FROM tbl WHERE col1 > @col1
  ORDER BY col1 ASC
 

Simple enough right?

Disclaimer

While this procedure does work with more complex queries containing SUB-SELECTS, JOINS and WHERE clauses, it does have its limitations. GROUP BY clauses do not work in this version. Maybe in the future.

I wouldn't suggest calling this procedure directly as that would mean you're building your query in-code, and probably not doing necessary escaping to prevent security issues. Creating a stored procedure that builds the original query from passed in WHERE and ORDER BY Parameters and then calls the ReturnPage procedure will be much more efficient and secure.

I hope to post a complete sample that will illustrate the changes to the .NET code and the wrapping procedure soon.

Posted in Development, Programming, SQL Server | Add a Comment »