Posts
19
Comments
28
Trackbacks
1
Pagination using Stored Procedure

There are different approach on how to do pagination, but the I personally uses the dynamic sql pagination using subquery, and this I think is very efficient when doing a pagination in which all the fields to be filtered and sorted are all in the same table:

EXEC  ( 'SELECT ' + @sqlQuery +
    ' WHERE [ID] IN ' +
   '  (SELECT TOP ' + @strPageSize + ' [ID] FROM ' + @strBaseTable + @strFilter +
             @Connector +  '  [ID] NOT IN ' + '
                (SELECT TOP ' + @strSkippedRoW + ' [ID] FROM ' + @strBaseTable + @strFilter + @SortBy + ') '
   + @SortBy + ') ' + @SortBy)


Example we have our SalesOrderTable which comprises of SalesID, Date, ItemID, WholeSalerID, CustomerID and   the other table we have are Item Table, Wholesaler Table, CustomerTable . In this example lets take in mind these are the number of records per table:

SalesOrderTable = 2,300,000  |  ItemTable = 670,000  |  WholeSaler Table = 310,000 |  Customer Table = 900,000

What if the user wanted the data sorted using ItemName, CustomerName, WholeSaler Name which is on different table, in order to achive this we are going to use JOINS right??.  using the dynamic subquery will take forever.  I came up with more optimized approach which runs less than 8 secs..

 

1. Checks the orderby statement. If the sorting is based on fields that is on the same table or fields that are located in different table.

SET @strBaseTable =  ( CASE
   WHEN (@SortBy IS NOT NULL AND @SortBy!='') THEN
    (CASE
    WHEN    (     @SortByFilter='ID' 
              OR @SortByFilter='SalesOrderTable.WholeSalerTableCode' 
              OR @SortByFilter= 'SalesOrderTable.HPCode' 
              OR @SortByFilter='SalesOrderTable.ITEM'
              OR @SortByFilter='QTY'
              OR @SortByFilter='OEDate' )
     THEN ' SalesOrderTable'  
    WHEN @SortByFilter=  'a.CNAME' THEN ' SalesOrderTable INNER JOIN  dbo.CustomerTable a ON dbo.SalesOrderTable.WholeSalerTableCode = a.CustomerTable  '
    WHEN @SortByFilter=  'a.CustomerTablePRV' THEN ' SalesOrderTable INNER JOIN  dbo.CustomerTable a ON dbo.SalesOrderTable.WholeSalerTableCode = a.CustomerTable '
    WHEN @SortByFilter= 'a.CustomerTableCITY' THEN ' SalesOrderTable INNER JOIN  dbo.CustomerTable a ON dbo.SalesOrderTable.WholeSalerTableCode = a.CustomerTable '
    WHEN @SortByFilter= 'b.CNAME' THEN ' SalesOrderTable INNER JOIN dbo.CustomerTable b ON dbo.SalesOrderTable.HPCode = b.CustomerTable  '  
    WHEN @SortByFilter= 'b.CustomerTablePRV' THEN ' SalesOrderTable INNER JOIN dbo.CustomerTable b ON dbo.SalesOrderTable.HPCode = b.CustomerTable  '  
    WHEN @SortByFilter=  'b.CustomerTableCITY' THEN ' SalesOrderTable INNER JOIN dbo.CustomerTable b ON dbo.SalesOrderTable.HPCode = b.CustomerTable  '  
    WHEN @SortByFilter=  'ITEMDES' THEN ' SalesOrderTable     INNER JOIN     dbo.ItemTable ON dbo.SalesOrderTable.ITEM = dbo.ItemTable.ITEM '
    ELSE ' SalesOrderTable '
    END)  
   ELSE  ' SalesOrderTable '
             END )

2. Inserts only the ID with proper sorting to the temp table. I use #temp table because the filtering is dynamic.

 --- Inserts all ID to #TempTable with sorting
 SET @sql1 =   'INSERT INTO #TempTable ([ID])' +
             ' SELECT SalesOrderTable.[ID] FROM ' + @strBaseTable  + @strFilter + @SortBy

  EXEC @sql1

SET @rowCount = @@rowcount

3. Finally Get the proper data by linking all tables + temp table's ID and ROW

 --- Retrieves All Information Needed
           SELECT
    @rowCount AS RecordCount,
    dbo.SalesOrderTable.[ID],
                                         a.CustomerTablePRV AS WholeSalerTablePrv,
                                         a.CustomerTableCITY AS WholeSalerTableCity,
                                         dbo.SalesOrderTable.WholeSalerTableCode,
                                         a.CNAME AS WholeSalerTableName,
                                         b.CustomerTablePRV AS HPPrv,
                                         b.CustomerTableCITY AS HPCity,
                                         dbo.SalesOrderTable.HPCode,
                                         b.CNAME AS HPName,
                                        dbo.SalesOrderTable.ITEM,
                                        dbo.ItemTable.ITEMDES,
                                        dbo.SalesOrderTable.QTY,
                                        dbo.SalesOrderTable.OEDate,
                                        dbo.SalesOrderTable.CFM,
                                        dbo.SalesOrderTable.SalesID,
               dbo.SalesOrderTable.InpDate
              FROM      dbo.SalesOrderTable
   INNER JOIN     dbo.CustomerTable a ON dbo.SalesOrderTable.WholeSalerTableCode = a.CustomerTable
               INNER JOIN     dbo.CustomerTable b ON dbo.SalesOrderTable.HPCode = b.CustomerTable
               INNER JOIN     dbo.ItemTable ON dbo.SalesOrderTable.ITEM = dbo.ItemTable.ITEM
  INNER JOIN    #TempTable t ON dbo.SalesOrderTable.[ID] = t.[ID]
 WHERE (t.ROW >= @Start) AND (t.ROW <= @END)

Conclusion:

I do many test with the given scenario and I think this is the fastest approach I've ever had.

 

 

 

posted on Thursday, June 23, 2005 1:18 PM Print