uber1024's WebLog

It's not hot wings and beer, but it's still okay

Stored Proc that does paging and sorting for you

This was a solution I came up for paging and sorting with that I use pretty much all over now.  Let's face it, sorting and (especially) paging with a DataGrid is for chumps.

A little intro to this proc ... you pass it a table/view name, the primary key (used for paging), and then the info that you would need to generate the SQL (page size, which page you want, how you want to sort it, and a WHERE clause), and it returns the exact data you want.  It figures out whether it needs to page and then builds the SQL that it will execute. 

My next post will be the base class that I derive from that wraps this stored proc call.

CREATE PROCEDURE GetSortedPage(
  @TableName VARCHAR(500),
  @PrimaryKey VARCHAR(25),
  @SortField VARCHAR(100) = NULL,
  @PageSize INT = 99999,
  @PageIndex INT = 1,
  @QueryFilter VARCHAR(3000) = 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 @PageIndex = 1 AND @PageSize = 99999 AND @QueryFilter IS NOT NULL AND @QueryFilter <> ''
 BEGIN
  IF @SortField IS NULL
  BEGIN
   EXEC('SELECT * FROM ' + @TableName + ' WHERE  ' + @QueryFilter )
  END
  ELSE
  BEGIN
   EXEC('SELECT * FROM ' + @TableName + ' WHERE  ' + @QueryFilter + ' ORDER BY  ' + @SortField )
  END
  EXEC('SELECT 1 AS PageCount')
 END
ELSE
 BEGIN
  IF @QueryFilter IS NULL OR @QueryFilter = ''
  BEGIN
  
     -- if the sort is null then don't sort - the view might have a sort on it
     IF @SortField IS NULL
       BEGIN
   EXEC(
      'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
        (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
          (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ')
                  )'
                        )
       END  
  
     ELSE
  
       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
     ) 
       END   
  
       EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)
   
  END
  ELSE
   BEGIN
  
     IF @SortField IS NULL
  
   BEGIN
       EXEC(
      'SELECT * FROM ' + @TableName + ' WHERE  ' + @QueryFilter + ' AND  ' + @PrimaryKey + ' IN
        (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
          (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter +  ')
        )'
               )
               END
   
     ELSE
    
       BEGIN 
  
     EXEC(
     'SELECT * FROM ' + @TableName + ' WHERE  ' + @QueryFilter + ' AND  ' + @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
     )
       END
  
   
       EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)
  
  END
 END
GO

For what it's worth, I typically don't bother with the dozens of near unreadable emails attempting to explain my shortcomings that I get with every post.  This time, I'm going to do something a little different.  I'm going to take the worst email I get and post it. 

Posted: Jul 19 2005, 09:59 AM by uber1024 | with 5 comment(s)
Filed under:

Comments

Samboy LIms said:

I also tried the various approaches to this problem before. Testing on a really large data (such as hundreds of thousand records), I came into the conclusion that the 'cursor' approach is the best compromise in terms of speed and complexity. The other solutions (temporary tables, etc) become really slow specially if the page being requested is near the end of result set. With a little tweak to enforce that my primary key is set always to a int (identity) value, I am now happy with the speed of the cursor approach and use it in most of by large-sized datagrids.
# July 19, 2005 11:08 AM

Alex Papadimoulis said:

This is a *very* bad of way doing this. It's way too complicated, involves dynamic SQL, and assumes that every table has a single-column PK. It's probably pretty slow under lots of data too.

There are really better ways to do this. You can translate this to .NET fairly easily: http://aspfaq.com/show.asp?id=2120

As Samboy mentioned, use a cursor.
# July 19, 2005 11:32 AM

uber said:

For the record, the site gets about a million users a month from all over the world, runs on 1 database server, and contains 27 gigs of data ... yet has never had a problem with this "*very* bad" stored proc that drives 90% of the data access in the 3 years its been around.

Damn you people are picky. Also, do you *TRY* any of the code you suggest?

Another question for the peanut gallery:

Do you work on websites? I mean ... REAL websites? Ever work on an enterprise level site that has a few hundred to a few thousand files? I bet you haven't.
# July 19, 2005 4:50 PM

Erik Porter said:

Yah, don't mind everybody else. I get yelled at for *bad* things all the time, don't let it bother you. Use what works! I think what you've got is pretty neat! I would be interested to see what the performance difference with the data you're using would be with a cursor version of your SP though (just for kicks).
# July 19, 2005 5:35 PM

Jeff Gonzalez said:

Pretty similar to what a guy at work came up with. It doesn't have any of the problems that Alex mentions, although it does seem to take the approach that most people will really not page through hundreds of paged results.

The only other thing I would suggest is using sp_executesql instead of exec and I would use parameterized queries instead of just concatination. This allows the query plan to be cached and reused. Plus it has the side benefit of being more resilient to sql injection. We usually generate 90% of our sql code (shameless CodeSmith plug), so we use the relationships and indexes on the tables to create parameters for our sproc input parameters. We then default all of the parameters to specific value so that they are optional.

You do lose a slight bit of flexibility with your queries, but you are probably indexing the fields ppl search on for the most part anyways (hopefully) and it works out from a performance point of view.

I wouldn't worry about anyone who tells you to use a cursor instead and then goes on to boast about their exploits using a datagrid (hello performance problems)

Everything (with the exception of inline editing, i just havent tried) can be done with repeaters and some highly reusable code.


# July 19, 2005 7:34 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)