June 19, 2009 at 6:47 am
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.
June 19, 2009 at 8:25 am
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
June 20, 2009 at 10:08 am
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_idINTEGERNOT NULL,
codeVARCHAR(10) NOT NULL,
[description]VARCHAR(50) NOT NULL,
priceMONEY NOT NULL,
lotINTEGER NOT NULL,
uomVARCHAR(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
--
INSERTdbo.Product
(product_id, code, [description], price, lot,uom)
SELECTTOP (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())) ' or ' ' 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
EXECUTEsp_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
SQLkiwi blog
@SQL_Kiwi
June 22, 2009 at 4:05 am
ALTER PROC PagingAndSorting
(
@SortColumnNVARCHAR(255),
@SortDirectionNVARCHAR(4) = 'asc' ,
@rowsPerPageINT = 10,
@pageNumINT = 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
June 22, 2009 at 6:20 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply