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)