Custom Pagination in SQL Server 2005

,

Introduction

Pagination is not a new concept to developers or users.  In

fact, most of us use pagination every day.  Pagination gives users the ability

to view data in small subsets, much like flipping through the pages of a book. 

In fact, many development tools have built-in pagination; however, I believe we

sometimes become too dependent on canned code.  It seems that managers today

give less thought to scalability and performance and more thought to faster development.

I am not saying that auto generated code is bad, but it is fair to say auto

generated code is not always the most efficient.  Sometimes this inefficiency

can directly impact user experience and the overall perspective of application

performance. Pagination is one of these inefficiencies.  While it is easier to

click the check box that says pagination, it is also a lot more costly. 

The Problem

The biggest problem with built-in pagination is the amount

of data returned to the application.  Built-in pagination retrieves all the

data from a subsequent query and performs filtering at the application tier,

not the database tier. This leads to greater overhead and resource consumption,

with no additive benefit.  For example, if you have 100,000 rows returned by a

SQL query and the user wants to view 10 rows at a time, built-in pagination

will return 100,000 rows regardless of the number of rows displayed on the

screen.  From a user’s perspective, the load time should be fast because the

application is only returning 10 rows.  When load times are slow for 10 rows,

users begin to get frustrated and scream that the application is slow.  Users

do not care, nor should they, what is going on in the background.  Users care

about two things: usability and performance.  This article addresses the

database logic that can be used to successfully implement a custom paging

solution, which can translate into faster load times and a better user

experience.

The Solution

SQL 2005 introduced new functionality called Row_Number. 

Row_Number makes pagination a breeze because it gives developers the ability to

assign a sequential number to each row returned.  The numbering is dictated by

the partition and ordering set within the function.  This functionality allows

developers to return only the data that is needed by the application, nothing

more nothing less.   By returning less data to the application, we can directly

increase performance and put less stress on the server.

Let’s start by creating a sample table and some data. 

Note: I created test data by populating empty tables with Adventure

Works data.  You can download a free copy of the AdventureWorks database, on

the codeplex website. http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

 

 

USE

[tempdb]

GO

 

IF EXISTS(SELECT 1 FROM sys.tables WHERE [name] = 'Contacts')

BEGIN

DROP

TABLE Contacts

END

GO

 

CREATE

TABLE Contacts(

ContactID

INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

FirstName

VARCHAR(25),

LastName

VARCHAR(25)

)

GO

 

CREATE

INDEX IXC_LastName ON

dbo.Contacts(LastName) INCLUDE(FirstName)

GO

CREATE

INDEX IXC_FirstName ON

dbo.Contacts(FirstName) INCLUDE(LastName)

GO

 

DECLARE

@max_id INT

SET

@max_id = (SELECT max(contactid)

   FROM

[Adventureworks].[Person].[Contact]

  )

 

INSERT

INTO Contacts

SELECT

TOP 1000000 --<<<--THE

NUMBER OF ROWS!

       

FirstName = (SELECT TOP 1

FirstName

   FROM

adventureworks.person.contact

   WHERE ContactID = 

ABS(CONVERT(BINARY(6),NEWID()) % @max_id) + 1),

       

LastName  = (SELECT TOP 1

LastName 

  

FROM adventureworks.person.contact

   WHERE ContactID = 

ABS(CONVERT(BINARY(6),NEWID()) % @max_id) + 1)

FROM

Master.dbo.SysColumns

t1,

    

Master.dbo.SysColumns

t2

GO

 

Now that we have our base table and data, we can begin to

discuss how to best accomplish the task at hand.  I am sure that most will need

more functionality than paging data, so I have included dynamic sorting and filtering

functionality. Usually, when someone hears the word dynamic they automatically

think dynamic SQL; however, I will demonstrate some ways to achieve dynamic

functionality, without the use of dynamic SQL.  I will also show how to achieve

the same functionality through the use of dynamic SQL.

Note:  I will not go into any great detail about the

performance benefits/costs and potential hazards of using dynamic SQL, but will

show you some techniques to minimize risk. For more information about the use of

dynamic sql, please read the following article by Erland Sommarskog, http://www.sommarskog.se/dynamic_sql.html.  

CREATE

PROCEDURE usp_ContactPaging

(

      @SortCol

VARCHAR(25)='ContactId ASC',

      @FirstName

VARCHAR(25)=NULL,

      @LastName

VARCHAR(25)=NULL,

      @pgSize

INT=25, @pgNbr INT=1

)

AS

BEGIN

 

/*==================================================

 I use the below code to get the total number of

 rows. If you do not need this functionality,

 you can remove this section of code and remove

 @NbrPages in the SELECT statements.

==================================================*/

DECLARE

@NbrPages INT

 

IF

@FirstName IS NULL

AND @LastName IS

NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

END

 

IF

@FirstName IS NOT

NULL AND

@LastName IS NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[FirstName] = @FirstName

END

 

IF

@FirstName IS NULL

AND @LastName IS

NOT NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[LastName] = @LastName

END

 

IF

@FirstName IS NOT

NULL AND

@LastName IS NOT

NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[FirstName] = @FirstName AND

       

[LastName] = @LastName

END

 

;WITH

PagingCTE (Row_ID,ContactId,FirstName,LastName)

AS

(

SELECT

      ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

      ContactId,

      FirstName,

      LastName

FROM

Contacts

WHERE

[FirstName] = COALESCE(@FirstName,FirstName) AND

      [LastName]

= COALESCE(@LastName,LastName)

)

SELECT

      Row_ID,

      ContactId,

      FirstName,

      LastName,

@pgNbr AS PageNumber,

      @NbrPages

AS TotalNbrPages

FROM

PagingCTE

WHERE

Row_ID >= (@pgSize

* @pgNbr) - (@pgSize -1) AND

      Row_ID

<= @pgSize *

@pgNbr

END

GO

 

This stored procedure is using a CTE (Common Table

Expression) to create row numbering.  The variables are sort column, first

name, last name, page number and page size.  The variable names are self

explanatory and I need not go into detail.  The where clause uses a formula

based on page size and the page number to calculate which rows should be returned

to the application.  By submitting values such as @pgSize = 25, @pgNbr = 1, we

are telling the procedure to return the 1st page and each page will

contain 25 rows.

 One of the key things to note in the above stored procedure

is the order by clause. The order by clause sorts the data depending on the

parameter passed in.  As you can see, I used a case expression to filter the

data conditionally based on the value of @SortCol.  We can use this method to dynamically

sort, without the use of dynamic sql.  This still gives us good performance,

costs little overhead and does not subject us to injection attack.  The

predicate is built dynamically using COALESCE.  I am using the COALESCE

function to set the column equal to itself, unless a value has been passed into

the stored procedure.  Often developers will use (WHERE FirstName = @FirstName

OR @FirstName IS NULL) instead.  This is essentially the same thing and should

not result in a differing execution plan.   Can we all agree that this stored

procedure looks good?  Well I disagree.  I used COALESCE to demonstrate what

not to do when dealing with dynamic filters.  The method described above guarantees

that the query will result in a table scan and that no indexes will be used.  I

know this is outside the scope of the article, but should be mentioned.  The

idea is to make data retrieval faster, not slower. 

Have your doubts?  Let’s test the stored procedure.  Note:

Since we are dynamically generating table data, I cannot guarantee you will

have the same first and last names in your table.  Supply a name that is valid

in your table.

EXEC

usp_contactpaging 'contactid DESC','Adam',NULL,25,1

GO

Results:

Execution Plan:

What can we do to make this query index seek?  Well, we

really only have two options.  One is to use dynamic sql to build the predicate

dynamically.  Two is to introduce control flow logic and have differing

statements for each possibility.  I know that using control flow logic can be

tedious and will make the procedure more difficult to manage, but I still prefer

to use this method where possible.  For those in a situation where there are

too many variables to use control flow logic, I will be demonstrating a dynamic

sql method a bit later in the article.

Revised Stored Procedure

ALTER

PROCEDURE usp_ContactPaging

(

      @SortCol

VARCHAR(25)='ContactId ASC',

      @FirstName

VARCHAR(25)=NULL,

      @LastName

VARCHAR(25)=NULL,

      @pgSize

INT=25, @pgNbr INT=1

)

AS

BEGIN

 

/*==================================================

 I use the below code to get the total number of

 rows. If you do not need this functionality,

 you can remove this section of code and remove

 @NbrPages in the SELECT statements.

==================================================*/

DECLARE

@NbrPages INT

 

IF

@FirstName IS NULL

AND @LastName IS

NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

END

 

IF

@FirstName IS NOT

NULL AND

@LastName IS NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[FirstName] = @FirstName

END

 

IF

@FirstName IS NULL

AND @LastName IS

NOT NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[LastName] = @LastName

END

 

IF

@FirstName IS NOT

NULL AND

@LastName IS NOT

NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[FirstName] = @FirstName AND

       

[LastName] = @LastName

END

 

--NO filters, this will always TABLE/INDEX scan

IF

@FirstName IS NULL

AND @LastName IS

NULL

BEGIN

 

      ;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

      AS

      (

      SELECT

            ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

            ContactId,

            FirstName,

            LastName

      FROM Contacts

      )

      SELECT

            Row_ID,

            ContactId,

            FirstName,

            LastName,

            @pgNbr

AS PageNumber,

            @NbrPages

AS TotalNbrPages

      FROM PagingCTE

      WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

            Row_ID

<= @pgSize *

@pgNbr

END

 

--FIRST NAME ONLY

IF

@FirstName IS NOT

NULL AND

@LastName IS NULL

BEGIN

 

      ;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

      AS

      (

      SELECT

            ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

            ContactId,

            FirstName,

            LastName

      FROM Contacts

      WHERE [FirstName] =

@FirstName

      )

      SELECT

            Row_ID,

            ContactId,

            FirstName,

            LastName,

@pgNbr

AS PageNumber,

            @NbrPages

AS TotalNbrPages

      FROM PagingCTE

      WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

            Row_ID

<= @pgSize *

@pgNbr

END

 

--LAST NAME ONLY

IF

@FirstName IS NULL

AND @LastName IS

NOT NULL

BEGIN

 

      ;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

      AS

      (

      SELECT

            ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

            ContactId,

            FirstName,

            LastName

      FROM Contacts

      WHERE [LastName] =

@LastName

      )

      SELECT

            Row_ID,

            ContactId,

            FirstName,

            LastName,

            @pgNbr

AS PageNumber,

            @NbrPages

AS TotalNbrPages

      FROM PagingCTE

      WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

            Row_ID

<= @pgSize *

@pgNbr

END

 

--FIRST AND LAST NAME

IF

@FirstName IS NOT

NULL AND

@LastName IS NOT

NULL

BEGIN

 

      ;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

      AS

      (

      SELECT

            ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

            ContactId,

            FirstName,

            LastName

      FROM Contacts

      WHERE [FirstName] =

@FirstName AND

            [LastName]

= @LastName

      )

      SELECT

            Row_ID,

            ContactId,

            FirstName,

            LastName,

            @pgNbr

AS PageNumber,

            @NbrPages

AS TotalNbrPages

      FROM PagingCTE

      WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

            Row_ID

<= @pgSize *

@pgNbr

END

 

END

GO

As you can see, the stored

procedure just got a lot uglier and longer; however, the optimizer can now

generate a quality execution plan.  Execute the new procedure, using the same

variables.

EXEC

usp_contactpaging 'contactid DESC','Adam',NULL,25,1

GO

Query Results:

Execution Plan:

Note:  If you use the LIKE operator with begins with, you

may see a nested loop join instead of a single seek.  Like uses a nested loop

join to compare a constant value (your parameter value) to an underlying table,

in our case contacts.  This type of join will still yield good performance for

our purposes because we are only seeking the table one time; however, this type

of join is usually slower for large table joins.

By adding control flow logic to the existing stored

procedure we are able to index seek.   The major problem with this method is

maintainability becomes increasingly difficult; however, I believe

manageability takes a backseat to security.  Next I will demonstrate the

parameterized dynamic sql method.

The biggest consideration when building a dynamic SQL query

is how to minimize risk and parameterize the statement. In order to

parameterize dynamic SQL you must execute dynamic SQL using the system stored

procedure sp_executesql.    Parameterized dynamic SQL is less susceptible to

injection attack, in comparison to non parameterized dynamic SQL. 

Parameterized dynamic SQL also has the distinct advantage of query plan reuse. The

optimizer does not usually allow for query plan reuse with non parameterized dynamic

SQL, unless an identical query plan already exists.  For parameterized dynamic

SQL, the optimizer is able to keep parts of an existing query plan and

interchange values, without having to generate a new plan. 

Parameterized Dynamic SQL:

ALTER

PROCEDURE usp_ContactPaging

(

      @SortCol

VARCHAR(25)='ContactId ASC',

      @FirstName

VARCHAR(25)=NULL,

      @LastName

VARCHAR(25)=NULL,

      @pgSize

INT=25, @pgNbr INT=1

)

AS

BEGIN

 

DECLARE

@SQL nvarchar(max),

       

@params nvarchar(100)

 

/*==================================================

 I use the below code to get the total number of

 rows. If you do not need this functionality,

 you can remove this section of code and remove

 @NbrPages in the SELECT statements.

==================================================*/

DECLARE

@NbrPages INT

 

IF

@FirstName IS NULL

AND @LastName IS

NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

END

 

IF

@FirstName IS NOT

NULL AND

@LastName IS NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[FirstName] = @FirstName

END

 

IF

@FirstName IS NULL

AND @LastName IS

NOT NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[LastName] = @LastName

END

 

IF

@FirstName IS NOT

NULL AND

@LastName IS NOT

NULL

BEGIN

SELECT

@NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM

Contacts

WHERE

[FirstName] = @FirstName AND

       

[LastName] = @LastName

END

 

SET

@params = N'@SIZE INT, @nbr

INT, @Pages INT, @Sort VARCHAR(25)'

SET

@SQL = N'

;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

AS

(

SELECT

--YOU CAN REMOVE

THE CASE AND USE THE SORT VARIABLE HERE BUT IT

--INCREASES

SUSCEPTIBILITY

      ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @Sort=''FirstName DESC'' THEN FirstName END

DESC,

             CASE WHEN @Sort=''FirstName ASC''  THEN FirstName END

ASC,

             CASE WHEN @Sort=''LastName ASC''  THEN LastName  END

ASC,

             CASE WHEN @Sort=''LastName DESC''  THEN LastName  END

DESC,

             CASE WHEN @Sort=''ContactID ASC''  THEN ContactId END

ASC,

             CASE WHEN @Sort=''ContactID DESC'' THEN ContactId END

DESC

            ) AS [Row_ID],

      ContactId,

      FirstName,

      LastName

FROM Contacts

' + CASE

      WHEN @FirstName IS NOT NULL AND @LastName IS NULL THEN

      N'WHERE FirstName = ' +

QUOTENAME(@FirstName,'''')

      WHEN @FirstName IS NULL AND @LastName IS NOT NULL THEN

      N'WHERE LastName = ' + QUOTENAME(@LastName,'''')

      WHEN @FirstName IS NOT NULL AND @LastName IS NOT NULL THEN

      N'WHERE FirstName = ' +

QUOTENAME(@FirstName,'''') + ' AND ' +    N'LastName = ' + QUOTENAME(@LastName,'''') 

      END + N'

)

SELECT

      Row_ID,

      ContactId,

      FirstName,

      LastName,

      @Nbr AS PageNumber,

      @Pages AS TotalNbrPages

FROM PagingCTE

WHERE Row_ID >= (@SIZE * @nbr) - (@SIZE -1) AND Row_ID <=

@SIZE * @nbr'

 

--PRINT @sql

 

EXEC

sp_executesql

      @SQL,

      @params,

      @SIZE

= @pgSize,

      @nbr

= @pgNbr,

      @Pages

= @NbrPages,

      @Sort

= @SortCol

 

END

GO

Now

let’s execute the stored procedure

EXEC

usp_contactpaging 'contactid DESC','Adam',NULL,25,1

GO

Query Results:

Execution Plan:

Note: You can remove the Row_Number case statement and use

@SortCol to build the order by clause dynamically; however, you will be subject

to greater security risk and will not greatly increase performance.

Conclusion

In our attempt to cut corners to meet deadlines, we

sometimes miss the key ingredients to making an application as efficient as

possible.  One of these corners is built-in pagination.  While it is much

easier to check the box for pagination, it does not mean this is the best way to

introduce pagination to your environment.  SQL 2005 has given us a simplistic method

to create custom pagination.  The methodology is possible because of a new

function called Row_Number.    By implementing custom pagination functionality,

we can directly increase user satisfaction, have faster page loads, and reduce amount

of data returned to the application.

Links:

·        

Adventure Works DB - http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

·        

Dynamic SQL – http://www.sommarskog.se/dynamic_sql.html

·        

Row_Number() - http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx

·        

Sp_executesql - http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx

Rate

4.53 (32)

Share

Share

Rate

4.53 (32)