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.