My sql server pagination script

Question

please have a look at the code and tell me my below sql pagination script give best performance when dealing with large data. i am using sql server 2008 R2

ALTER PROCEDURE USP_GETData
(
    -- Pagination
    @PageNbr            INT = 1,
    @PageSize           INT = 10,
    @SortColumn NVARCHAR(20) = 'ProductName',
    @SortOrder NVARCHAR(4)= 'ASC',    
    
    -- Optional Filters for Dynamic Search
    @ProductID          INT = NULL, 
    @ProductName          NVARCHAR(50) = NULL
)
AS
BEGIN
    DECLARE
        @lProductID				INT, 
        @lProductName			NVARCHAR(50),
        @lSortCol				NVARCHAR(20)
     
    DECLARE
        @lPageNbr   INT,
        @lPageSize  INT,
        @lFirstRec  INT,
        @lLastRec   INT,
        @lTotalRows INT

	SET @lSortCol		=		LTRIM(RTRIM(@SortColumn)) 
    SET @lProductID		=		@ProductID
    SET @lProductName	=		LTRIM(RTRIM(@ProductName))
 
    SET @lPageNbr   = @PageNbr
    SET @lPageSize  = @PageSize
     
    SET @lFirstRec  = ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec   = ( @lPageNbr * @lPageSize + 1 ) 
    SET @lTotalRows = @lFirstRec - @lLastRec + 1
 
	; WITH CTE_Results
		AS (
		SELECT ROW_NUMBER() OVER (ORDER BY
			CASE WHEN (@lSortCol = 'ProductID' AND @SortOrder='ASC')
						THEN ProductID
			END ASC,
			CASE WHEN (@lSortCol = 'ProductID' AND @SortOrder='DESC')
					   THEN ProductID
			END DESC,

			CASE WHEN (@lSortCol = 'ProductName' AND @SortOrder='ASC')
					  THEN ProductName
			END ASC,
			CASE WHEN @lSortCol = 'ProductName' AND @SortOrder='DESC'
					 THEN ProductName
			END DESC

	   ) AS ROWNUM,
	   Count(*) over () AS TotalCount,
	   ProductID,
	   ProductName
	 FROM Products
	 WHERE
		 (@lProductID IS NULL OR ProductID = @lProductID)
	 AND (@lProductName IS NULL OR ProductName LIKE '%' + @lProductName + '%')
    )
    
    SELECT
        ProductID, 
        ProductName
    FROM CTE_Results AS CPC
    WHERE
        ROWNUM > @lFirstRec 
    AND ROWNUM < @lLastRec
 
END
GO

-- USP_GETData 1,5, 'ProductName', 'DESC'

Answer 1

Instead of applying conditional logic at the time of RowNumber generation you may be better off populating temp table with an identity column based on your parameter values first and then use it directly in the pagination query

To check compare execution plan in both the cases and see the difference in cost

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

I suggest to add OPTION (RECOMPILE) to your query. For similar requirements I prefer to generate SQL statement dynamically and then execute using sp_executeSQL procedure passing parameters correctly.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 3

most of the people suggest dynamic sql performance is not good when dealing with data.

are you talking about this kind of store procedure for pagination with dynamic sql?

CREATE PROCEDURE getSelectedPage
    @TableOrView nvarchar (50),             
    @SelectedPage int,
    @PageSize int,
    @Columns nvarchar(500),
    @OrderByColumn nvarchar(100),
    @OrderByDirection nvarchar(4),
    @WhereClause nvarchar(500)
AS
SET NOCOUNT ON
DECLARE @ReturnedRecords int, @SqlQuery nvarchar(1000), @ConOrderByDirection nvarchar(4), @TotalPages int, @TotalRecords int
 
-- Finds total records
SET @SqlQuery = N'SELECT @RecCount = COUNT(*) FROM ' + @TableOrView
EXEC sp_executesql @SqlQuery, N'@RecCount int OUTPUT', @RecCount = @TotalRecords OUTPUT
 
-- Checks order direction
IF Upper(@OrderByDirection) = 'ASC'
  BEGIN
    SET @ConOrderByDirection = 'DESC'
  END
ELSE
  BEGIN
    SET @ConOrderByDirection = 'ASC'
  END

-- checks if WHERE clause is needed
IF @WhereClause <> ''
  BEGIN
    SET @WhereClause = ' WHERE ' + @WhereClause
  END

-- Finds number of pages
SET @ReturnedRecords = (@PageSize * @SelectedPage)
SET @TotalPages = @TotalRecords / @PageSize
IF @TotalRecords % @PageSize > 0
  BEGIN
    SET @TotalPages = @TotalPages + 1
  END
 
-- Checks if current page is last page
IF @SelectedPage = @TotalPages
  BEGIN
    -- Current page is last page
    SET @SqlQuery = N'SELECT * FROM
    (SELECT TOP ' + CAST((@TotalRecords % @PageSize) as varchar(10)) + ' *  FROM
      (SELECT TOP ' + CAST(@ReturnedRecords as varchar(10)) + ' ' + @Columns +
        ' FROM ' + @TableOrView + @WhereClause + '
        ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection + ') AS T1
      ORDER BY ' + @OrderByColumn + ' ' + @ConOrderByDirection + ') AS T2
    ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection
  END
ELSE
  BEGIN
    -- Current page is not last page
    SET @SqlQuery = N'SELECT * FROM
    (SELECT TOP ' + CAST(@PageSize as varchar(10)) + ' *  FROM
      (SELECT TOP ' + CAST(@ReturnedRecords as varchar(10)) + ' ' + @Columns +
        ' FROM ' + @TableOrView + @WhereClause + '
        ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection + ') AS T1
      ORDER BY ' + @OrderByColumn + ' ' + @ConOrderByDirection + ') AS T2
    ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection    
  END
 
-- executes query to get selected page
EXEC(@SqlQuery)
SET NOCOUNT OFF

Answer 4

will post code like what you are trying to say..........sorry just do not understand what you want me to do. if possible discuss with sample code. i want a store proc whose performance has to be best when dealing with large data.

Answer 5

In this case you put everything into dynamic SQL and for this you don't need SQL Server, you can as well generate that thing from an application.

I was talking more in line of something like the following:

-- Description:     Returns invoices with their balances (one page of data)
-- Example Call: DECLARE @TotalRows INT; execute dbo.siriussp_GetInvoicesWithPaging @acctName = '*GUESTS*',
---                                      @guestNo = 0, @Finalized = 1, @TotalRows = @TotalRows OUTPUT
-- =============================================
ALTER procedure [dbo].[siriussp_GetInvoicesWithPaging] (
    @acctName char(10) = '',
    @guestNo numeric(17, 0) = 0,
    @Finalized bit = 0,
    @PageSize int = 15,
    @PageNumber int = 1,
    @SearchText varchar(max) = '',
    @OrderByColumn varchar(20) = '',
    @SortOrder varchar(4) = 'ASC',
    @TotalRows int output)
as
begin
    set nocount on;

    if object_id('TempDb..#InvoicesList', N'U') is not null
        drop table #InvoicesList;

    create table #InvoicesList (
        invoice_no numeric(17, 0) not null
      , Descrip varchar(25) collate DATABASE_DEFAULT not null
      , hidden bit not null
      , finalized bit not null
      , Created datetime null
      , Balance money
      , Rn int
    )

    declare @Sql nvarchar(max);

    set @Sql = 'select i.invoice_no, i.descrip1 as Descrip, i.hidden, i.finalized, i.date_time as Created, ' +
               'coalesce(Bal.Balance,0) as Balance, row_number() over (order by ' 
               + case when @OrderByColumn = 'InvoiceNo' or @OrderByColumn = '' then 'i.invoice_no'
                      when @OrderByColumn = 'Descrip' then 'i.descrip1'
                      when @OrderByColumn = 'Balance' then 'COALESCE(Bal.Balance,0)'
                      when @OrderByColumn = 'Created' then 'i.date_time'
                 end
               + case when @SortOrder = 'desc' then ' desc' else '' end 
               + case when @OrderByColumn = 'InvoiceNo' or @OrderByColumn = '' then '' else ', i.invoice_no' end 
               +') as Rn from dbo.invoices i outer apply (select sum(extension) as Balance from dbo.transact t
                   where t.invoice_no = i.invoice_no) Bal where ' 
               + case when @guestNo <> 0 then 'i.guest_no = @guestNo ' else 'i.account = @acctName ' end 
               + case when @Finalized = 0 then '' else ' AND i.finalized = 1' end 
               + case when coalesce(@SearchText, '') = '' then '' 
                      else case when isnumeric(@SearchText) = 1 
                                then ' AND (CAST(i.invoice_no AS VARCHAR(17)) LIKE @SearchText + ''%''
                                       OR i.descrip1 LIKE ''%'' + @SearchText + ''%''
                                       OR CAST(Bal.Balance AS VARCHAR(20)) LIKE @SearchText + ''%'')'
                                else ' AND i.descrip1 LIKE ''%'' + @SearchText + ''%''' end end;

    --PRINT @SQL;

    insert into #InvoicesList (invoice_no, Descrip, hidden, finalized, Created, Balance, Rn)
    execute sp_executeSQL @Sql
                        , N'@acctName CHAR(10), @guestNo NUMERIC(17,0), @SearchText VARCHAR(max)'
                        , @acctName = @acctName
                        , @guestNo = @guestNo
                        , @SearchText = @SearchText;

    set @TotalRows = @@rowcount;

    select invoice_no as invoiceno
         , @acctName as account
         , Descrip
         , Hidden
         , Finalized
         , Created
         , Balance
    from #InvoicesList
    where Rn >= ((@PageNumber - 1) * @PageSize + 1)
    and Rn <= @PageNumber * @PageSize
    order by Rn;

end
/* Test
DECLARE @TotalRows INT = 0
execute dbo.siriussp_GetInvoicesWithPaging @acctName = '*GUESTS*', @Finalized = 0,
@TotalRows = @TotalRows OUTPUT, @SearchText = '', @SortOrder = 'ASC', @OrderByColumn = 'Balance'
 
execute dbo.siriussp_GetInvoicesWithPaging @guestNo = 28000001, @Finalized = 0,
@TotalRows = @TotalRows OUTPUT, @SearchText = '', @SortOrder = 'ASC', @OrderByColumn = 'Balance'
 
 
SELECT @TotalRows
 
*/

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 6

One point inspired by one article from Erland's site.

To get more efficient execution plans, avoid using approaches similar to below demo, instead use the parameter directly in the select statement.

CREATE PROCEDURE someProc @param varchar(10) AS
   DECLARE @changedParam varchar(10)
   SET @changedParam = @param --May do some modification when assign
   SELECT * FROM .. WHERE somecol = @changedParam

Besides, when executing dynamic sql, use sp_executesql instead of EXEC.

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support

Answer 7

most of the people suggest dynamic sql performance is not good when dealing with data.

Not always

Make sure you read this

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page