SQL Server 2005 Paging Performance Tip


By Robbe Morris
Printer Friendly Version
  

This quick tip demonstrates how to get the total rows as part of the paging query as well as how avoid a common coding error with joins that can harm performance.


I've seen the following technique in several beginner code samples for demonstrating SQL Server 2005's ability to return paged results.

I've added the TotalRows = Count(*) OVER() line to demonstrate how return the total rows returned above and beyond the row count for the paged set.  This removes the need for a second query to get the total rows available for paging techniques in your application.  In your application, just check to make sure your resultset has records, then just grab the first record and retrieve its TotalRows column value.

Notice that in this query, the JOIN between the Orders table and the Users table is being run across all records that are found NOT just the records returned in the paged set.

  declare @StartRow int
  declare @MaxRows int

  select @StartRow = 1
  select @MaxRows = 10

   select *
     from
     (select o.*,u.FirstName,u.LastName,
         TotalRows=Count(*) OVER(),
         ROW_NUMBER() OVER(ORDER BY o.CreateDateTime desc) as RowNum
         from Orders o , Users u
   WHERE o.CreateDateTime > getdate() -30
        AND (o.UserID = u.UserID)
     )
     WHERE RowNum BETWEEN @StartRow AND (@StartRow + @MaxRows) -1

If you adjust your query as follows, you will see a substantial boost in performance.  Notice this query only performs the join on the returned resultset which is much, much smaller.

SELECT MyTable.*,u.FirstName,u.LastName
FROM
   (SELECT o.*,
       TotalRows=Count(*) OVER(),
       ROW_NUMBER() OVER(ORDER BY o.CreateDateTime desc) as RowNum
    FROM Orders o
    WHERE o.CreateDateTime > getdate() -30
   ) as MyTable, Users u
WHERE RowNum BETWEEN @StartRow AND (@StartRow + @MaxRows) -1
  and (MyTable.UserID = u.UserID)


Biography
Robbe is a 2004-2008 Microsoft MVP for C# and the .NET Evangelist for Alinean Inc..  He is also the co-founder of EggHeadCafe. Robbe enjoys scuba diving with the folks at wet-n-fla.

button
 
Article Discussion: SQL Server 2005 Paging Performance Tip
  Robbe Morris posted at 16-May-08 11:22
Original Article