Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pagination with ordering in SQL 2005 Expand / Collapse
Author
Message
Posted Friday, June 19, 2009 6:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:24 AM
Points: 414, Visits: 360
Hi All ,

I need your help

I want to create one procedure that will order the records and return result set in pagination

following points needs to be considered for implementation

1. Ordering Column will be passed to Proc
2. Order type (I.e. ASC / DESC ) will be passed to Proc.
3. Pagination should work with Ordering ( for more than 500,000 Records
in single table)
4. we should consider performance as a critical point for implementation.


EX.

we have used following solution and applied but it is time consuming and very poor in performance ( Taking approx 40-45 sec for 500,000 records)

Table : Product
---------------------------------------------------------------------

Create procedure dbo.Product_SEL_PD-- 'code','desc',10,1
@SortColumn nvarchar(255),
@SortDirection nvarchar(4) = 'asc' ,
@rowsPerPage int = 10,
@pageNum int = 1
as

print (ltrim(rtrim(@SortColumn)) + ':' + ltrim(rtrim(@SortDirection)))

;WITH PaginatedProduct AS (
SELECT Top(@rowsPerPage * @pageNum)
CASE (ltrim(rtrim(@SortColumn)) + ':' + ltrim(rtrim(@SortDirection)))
WHEN 'ID:desc' THEN ROW_NUMBER() OVER (ORDER BY id desc)
WHEN 'code:desc' THEN ROW_NUMBER() OVER (ORDER BY code DESC)
WHEN 'description:asc' THEN ROW_NUMBER() OVER (ORDER BY description ASC)
WHEN 'description:desc' THEN ROW_NUMBER() OVER (ORDER BY description DESC)
WHEN 'price:desc' THEN ROW_NUMBER() OVER (ORDER BY price DESC)
WHEN 'lot:asc' THEN ROW_NUMBER() OVER (ORDER BY lot ASC)
WHEN 'uom:desc' THEN ROW_NUMBER() OVER (ORDER BY uom DESC)
END AS RowNumber,
id,code,description,price,lot,uom
FROM dbo.Product ORDER BY RowNumber

)
select * from PaginatedProduct WHERE RowNumber > ((@pageNum - 1) * @rowsPerPage)---ORDER BY RowNumber


------------------------------------------------------------------

please suggest other way to implement it so that performance can be improved.

Waiting for Updates..
Thx in advance.
Post #738252
Posted Friday, June 19, 2009 8:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 27, 2012 8:35 AM
Points: 31, Visits: 413
You can remove the big CASE clause within the CTE and build the query dynamically. Something like below...

Create procedure dbo.Product_SEL_PD-- 'code','desc',10,1
@SortColumn nvarchar(255),
@SortDirection nvarchar(4) = 'asc' ,
@rowsPerPage int = 10,
@pageNum int = 100000,
@SQL nvarchar(2000)

/*
some checks here to prevent SQL Injection from nvarchar columns

*/
set @SQL = '
;WITH PaginatedProduct AS (
SELECT Top(@rowsPerPage * @pageNum)
ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ' ' + @SortDirection + ') AS RowNumber,
id,code,description,price,lot,uom
FROM dbo.Product
)
select * from PaginatedProduct WHERE RowNumber > ((@pageNum - 1) * @rowsPerPage) ORDER BY RowNumber
'
EXEC sp_executesql @SQL, N'@rowsPerPage int, @pageNum int', @RowsPerPage = @RowsPerPage, @pageNum = @pageNum
GO


Post #738353
Posted Saturday, June 20, 2009 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 11,192, Visits: 11,087
Hey there,

The problem with ROW_NUMBER and paging in very large tables is that you end up calculating the row number for a very large number of rows.

In your case, it is possible to make some further optimizations. In the example script posted below, a 250K row table is created and then searched in various ways. The slowest run takes 51 ms on my old laptop and the most intensive search uses all of 123 logical reads.

This site sometimes mangles code posted in-line, so it is also in a zipped-up attachment.

Paul

USE tempdb;
GO
-- Drop the test table if it already exists
IF OBJECT_ID(N'dbo.Product', N'U') IS NOT NULL DROP TABLE dbo.Product;

-- Create a procedure stub (makes development easier since ALTER PROCEDURE will always work)
IF OBJECT_ID(N'usp_PagedProducts', N'U') IS NULL EXECUTE ('CREATE PROCEDURE dbo.usp_PagedProducts AS ');
GO
-- Test table
CREATE TABLE dbo.Product
(
product_id INTEGER NOT NULL,
code VARCHAR(10) NOT NULL,
[description] VARCHAR(50) NOT NULL,
price MONEY NOT NULL,
lot INTEGER NOT NULL,
uom VARCHAR(10) NOT NULL
);
--
-- Temporary index to enforce uniqueness on product_id (ignore duplicate rows is on)
-- Necessary since we will be inserting random data and this is an easy way to prevent duplicates
--
CREATE UNIQUE CLUSTERED INDEX [CUQ dbo.Product product_id] ON dbo.Product (product_id ASC) WITH (IGNORE_DUP_KEY = ON, FILLFACTOR = 50);
--
-- 250K random test rows
-- Should take less than ten seconds to create
--
INSERT dbo.Product
(product_id, code, [description], price, lot,uom)
SELECT TOP (250000)
CONVERT(INTEGER, RAND(CHECKSUM(NEWID())) * 10000000),
CHAR(65 + RAND(CHECKSUM(NEWID())) * 26) + RIGHT(CONVERT(BIGINT, RAND(CHECKSUM(NEWID())) * 10000000000), 9),
CONVERT(VARCHAR(50), NEWID()),
CONVERT(MONEY, ROUND(RAND(CHECKSUM(NEWID())) * 100, 2)),
CONVERT(INTEGER, RAND(CHECKSUM(NEWID())) * 1000),
CASE WHEN RAND(CHECKSUM(NEWID())) <= 0.33 THEN 'Single' WHEN RAND(CHECKSUM(NEWID())) <= 0.33 THEN 'Pack' ELSE 'Box' END
FROM master.sys.columns
CROSS
JOIN master.sys.columns C1
GO
--
-- Rebuild the clustered index and remove the ignore duplicate key option
-- Around one second to execute
--
CREATE UNIQUE CLUSTERED INDEX [CUQ dbo.Product product_id] ON dbo.Product (product_id ASC) WITH (IGNORE_DUP_KEY = OFF, FILLFACTOR = 100, DROP_EXISTING = ON);
--
-- Supporting indexes for the search (also gives us fullscan statistics)
-- Should take less than ten seconds in total
--
CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.Product product_id] ON dbo.Product(product_id ASC); --(faster than the clustered index for scanning product_id)
CREATE NONCLUSTERED INDEX [IX dbo.Product code] ON dbo.Product(code ASC);
CREATE NONCLUSTERED INDEX [IX dbo.Product description] ON dbo.Product([description] ASC);
CREATE NONCLUSTERED INDEX [IX dbo.Product price] ON dbo.Product(price ASC);
CREATE NONCLUSTERED INDEX [IX dbo.Product lot] ON dbo.Product(lot ASC);
CREATE NONCLUSTERED INDEX [IX dbo.Product uom] ON dbo.Product(uom ASC);
GO
-- The search procedure
ALTER PROCEDURE dbo.usp_PagedProducts

@sort_column SYSNAME, -- The column to sort by
@column_datatype VARCHAR(50), -- The full datatype of the sort column e.g. VARCHAR(10), DECIMAL(5,2), BIGINT
@sort_direction VARCHAR(4) = 'ASC', -- Sort direction, 'ASC' or 'DESC'
@page_number BIGINT = 1, -- Which page of the paged dataset to return
@page_size BIGINT = 25 -- How many rows on each page

AS
BEGIN

/*
The general strategy is to locate the first row of the requested page as quickly as possible,
and then just return @page_size rows from that point onward.
This method avoids computing ROW_NUMBER over the whole table just to return a small number of rows.
*/

SET NOCOUNT ON;

DECLARE @start_row BIGINT, -- The number of the first row to return, calculated from the paging parameters
@first_row_PK INTEGER, -- The primary key value of the first row to return
@first_column_value SQL_VARIANT, -- The value of the sort-by column in the first row
@sort_operator NCHAR(3), -- ' > ' or ' < ' depending on the sort direction specified. (Used in the dynamic SQL)
@sql NVARCHAR(4000); -- Holds dynamic SQL

-- Calculate the number of the first row to return, from the parameters provided
-- Also ensure that the column name provided is properly delimited for use in dynamic SQL
SELECT @start_row = (@page_number - 1) * @page_size + 1,
@sort_column = QUOTENAME(@sort_column);

-- Find the primary key of the first row on the selected page, and the value of the sort column in that row
-- If we are looking for row 100, we select the values into the holding variables for the TOP 100 rows, ordered as specified
-- This results in the PK and column values for the row we want ending up in the variables.
--
-- The sort column value is held in a SQL_VARIANT because we have to hold the column value in a variable, and the column could be of many types.
-- Using a SQL_VARIANT means we cannot handle text, ntext, image, timestamp, or sql_variant columns (not a big deal!)
--
-- The search will always use an index since the index is sorted by the ORDER BY clause, and
-- covers all the columns selected (product_id is included in the non-clustered indexes since it is the clustering key)
SET @sql =
N'SELECT TOP (@start_row) ' +
N'@first_row_PK = product_id, ' +
N'@first_column_value = ' + @sort_column + N' ' +
N'FROM dbo.Product ' +
N'ORDER BY ' + @sort_column + N' ' + @sort_direction + N';'

-- Execute the dynamic SQL and save the results using output variables
EXECUTE sp_executesql @sql,
N'@start_row BIGINT, @first_column_value SQL_VARIANT OUTPUT, @first_row_PK INTEGER OUTPUT', -- parameter definitions
@start_row, -- passed in
@first_column_value OUTPUT, -- the value of the sort column in the first row of the page we want
@first_row_PK OUTPUT; -- the primary key value for the row we want

-- Now we select a single page of rows, starting from the row we identified as the first on the page
-- The result set comprises all rows with the same column value as that found for the first row
-- (but with a higher PK value) PLUS all rows with a higher (or lower, depending on sort direction) value
-- for the sort column. The TOP operators in the subqueries are to ensure that each part of the UNION knows it
-- has a row-number goal, to promote use of a nonclustered index with key lookup.
-- The OPTION (FAST 1) is there for the same reason.

SELECT @sort_operator = CASE WHEN @sort_direction = 'ASC' THEN N' > ' ELSE N' < ' END,
@sql =
N'SELECT TOP (@page_size) product_id, code, [description], price, lot, uom ' +
N'FROM ' +
N'(' +
-- 1st part: same column value as first row, higher/lower PK (depending on sort direction specified)
-- Note the CONVERTs are ESSENTIAL to use the index! (This is why we need the data type as a parameter)
N'SELECT TOP (@page_size) * FROM dbo.Product ' +
N'WHERE ' + @sort_column + N' = CONVERT(' + @column_datatype + N', @first_column_value) ' +
N'AND product_id ' + @sort_operator + N' @first_row_PK ' +
N'ORDER BY ' + @sort_column + N' ' + @sort_direction + N' ' +
N'UNION ALL ' +
-- 2nd part: higher/lower column value than first row (depending on sort direction specified)
-- Note the CONVERTs are ESSENTIAL to use the index! (This is why we need the data type as a parameter)
N'SELECT TOP (@page_size) * FROM dbo.Product ' +
N'WHERE ' + @sort_column + @sort_operator +
N'CONVERT(' + @column_datatype + N', @first_column_value) ' +
N'ORDER BY ' + @sort_column + N' ' + @sort_direction + N' ' +
N') AS T1 ' +
N'OPTION (FAST 1);'

--PRINT @sql
-- Return a page of sorted results to the client
EXECUTE sp_executesql @sql, N'@page_size BIGINT, @first_column_value SQL_VARIANT, @first_row_PK INTEGER', @page_size, @first_column_value, @first_row_PK;

END;
GO
RETURN;
--
-- TEST with 250,000 row table we created:
--

-- Return page 5 (25 rows per page) ordered ascending by price
EXECUTE dbo.usp_PagedProducts
@sort_column = 'price',
@sort_direction = 'ASC',
@column_datatype = 'MONEY',
@page_number = 5,
@page_size = 25;

-- Total for the above: 110 logical reads, 32 ms execution time (totals from Profiler)

-- Return page 35 (50 rows per page) ordered ascending by product_id
EXECUTE dbo.usp_PagedProducts
@sort_column = 'product_id',
@sort_direction = 'ASC',
@column_datatype = 'INTEGER',
@page_number = 35,
@page_size = 50;

-- Total for the above: 36 logical reads, 51 ms execution time (totals from Profiler)

-- Return page 157 (20 rows per page) ordered descending by description
EXECUTE dbo.usp_PagedProducts
@sort_column = 'description',
@sort_direction = 'DESC',
@column_datatype = 'VARCHAR(50)',
@page_number = 157,
@page_size = 20;

-- Total for the above: 123 logical reads, 46 ms execution time (totals from Profiler)





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Paging.zip (17 views, 2.98 KB)
Post #738846
Posted Monday, June 22, 2009 4:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, Visits: 425



ALTER PROC PagingAndSorting
(
@SortColumn NVARCHAR(255),
@SortDirection NVARCHAR(4) = 'asc' ,
@rowsPerPage INT = 10,
@pageNum INT = 1
)
AS
BEGIN
DECLARE @To AS INT ,@From AS INT
SET @To = @pageNum*@rowsPerPage
SET @From = @To-@rowsPerPage+1;
WITH OrdTab AS(
SELECT Id,Code,Description,Price,Lot,Uom,
ROW_NUMBER() OVER (ORDER BY
(CASE @SortDirection
WHEN 'ASC' THEN
(CASE @SortColumn
/*place the datetime or bigint or int column here*/
WHEN 'Id' THEN Id
END)
END) ASC,
(CASE @SortDirection
WHEN 'ASC' THEN
(CASE @SortColumn
/*place the varchar column here*/
WHEN 'code' THEN [Code]
WHEN 'Description' THEN [Description]
END)
END) ASC,

(CASE @SortDirection
WHEN 'DESC' THEN
(CASE @SortColumn
/*place the datetime or bigint or int column here*/
WHEN 'Id' THEN Id
END)
END) DESC,
(CASE @SortDirection
WHEN 'DESC' THEN
(CASE @SortColumn
/*place the varchar column here*/
WHEN 'code' THEN [Code]
WHEN 'Description' THEN [Description]
END)
END) DESC
)AS [Rows]

FROM [dbo].[Product]
) SELECT Id,Code,Description,Price,Lot,Uom FROM OrdTab WHERE Rows BETWEEN @From AND @To
END

Post #739251
Posted Monday, June 22, 2009 6:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:24 AM
Points: 414, Visits: 360
Hi All thx a lot..

thanks a lot..Paul ..
You are an expert SQL guy.. it really sounds great solution, and exactly what i was looking for.
Post #739320
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse