SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pagination with ordering in SQL 2005


Pagination with ordering in SQL 2005

Author
Message
pxt_ce
pxt_ce
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 361
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.
Al-279884
Al-279884
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 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



Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33784 Visits: 11359
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
Paging.zip (23 views, 2.00 KB)
ningaraju.n
ningaraju.n
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 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


pxt_ce
pxt_ce
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 361
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search