SQL Server stored procedures to page large tables or queries

By: Michelle Gutzait   |   Comments (4)   |   Related: > Paging

Problem

I need to query a large amount of data to my application window and use paging to view it.  The query itself takes a long time to process and I do not want to repeat it every time I have to fetch a page.  Also, the number of rows in the result set could be huge, so I am often fetching a page from the end of the result set.  I can't use the default paging because I wait a long time until I get the data back.  What are my options?

Solution

There are few possible solutions out there for paging through a large result set.  In this tip, I am going to focus on three examples and compare the performance implications.  The examples are:

• Example 1 - I use a temporary table (#temp_table) to store the result set for each session.
• Example 2 - I use a Common Table Expression (CTE) to page through the result set.
• Example 3 - I populate a global temporary table to store the complete result set.

The first two examples are similar to some of the most commonly used paging stored procedure options, the third example is my own extension which I wanted to show for comparison in this specific case of a complex query with a large large result set.

In this stored procedure, I create the temporary table and insert only the relevant rows into it based on the input parameters:

In this example, I use a CTE with the ROW_NUMBER() function to fetch only the relevant rows:

CREATE PROCEDURE dbo.proc_Paging_CTE
(
@Page int,
@RecsPerPage int
)
AS
-- The number of rows affected by the different commands
-- does not interest the application, so turn NOCOUNT ON
SET NOCOUNT ON


-- Determine the first record and last record 
DECLARE @FirstRec int, @LastRec int

SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1);

WITH TempResult as
(
SELECT ROW_NUMBER() OVER(ORDER BY Popularity DESC) as RowNum,
s.Title, m.Publisher, s.AuthorNames, l.LanguageName,
m.FirstLine, m.CreationDate, m.PublishingDate, m.Popularity 
FROM dbo.Articles m
INNER JOIN dbo.Content s 
ON s.ArticleID = m.ID
LEFT OUTER JOIN dbo.Languages l 
ON l.ID = m.LanguageID
)
SELECT top (@LastRec-1) *
FROM TempResult
WHERE RowNum > @FirstRec 
AND RowNum < @LastRec



-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO

Example #3 - Using a global temporary table to hold the whole result

In this example, I use a global temporary table to store the complete result set of the query.  In this scenario, this temporary table will be populated during the first execution of the stored procedure. All subsequent executions of the stored procedure will use the same temporary table.  The idea behind this approach is that, when using a Global temporary table, other sessions can also use the same table (if they are aware of the GUID and need the same data). In order to drop the temporary table, you will have to either drop it explicitly or disconnect the session.

If this approach does not work for you, you could use the same technique method to create "temporary" tables in your user defined database with a unique extension.  One specific scenario when this technique could be useful is when the tempdb database is already being a bottleneck. If that is the case, with this approach you can always create a dedicated database for these tables. Just do not forget to drop the temporary objects when they are not required.

CREATE PROCEDURE dbo.proc_Paging_GlobalTempTable
(
@Page int,
@RecsPerPage int,
@GUID uniqueidentifier = null OUTPUT -- will output the extension of the table.
-- This parameter should be sent by the application:
-- First time it should be NULL and after, it should be
-- populated by the value that was sent back from the SP.
)
AS
-- The # of rows affected ny the different commands
-- does not interest the application, so turn NOCOUNT ON
SET NOCOUNT ON

-- Determine the first record and last record 
DECLARE @FirstRec int, @LastRec int, @cmd varchar(2000)

SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- If the GUID is null (first execution) -
-- The global table is created, otherwise it will be queried only:
IF @GUID IS NULL
BEGIN

SET @GUID = NEWID()

SET @cmd = 'SELECT RowNum=IDENTITY(INT,1,1),
s.Title, m.Publisher, s.AuthorNames, l.friendlyName,
m.FirstLine, m.CreationDate, m.PublishingDate, m.Popularity 
INTO [##tmp_' + CONVERT(VARCHAR(40),@GUID) + ']
FROM dbo.Abstracts m
INNER JOIN dbo.AbstractsContentSearch s ON s.AbstractID = m.ID
LEFT OUTER JOIN dbo.Languages l on l.ID = m.LanguageID
ORDER BY Popularity DESC;
CREATE UNIQUE INDEX [IDX_##tmp_' + CONVERT( VARCHAR(40),@GUID) + ']
ON [##tmp_' + CONVERT(VARCHAR(40),@GUID) + '] (RowNum)'
EXEC (@cmd)
END

-- Fetch the rows of the desired page
SET @cmd = 'SELECT top (' + CONVERT(VARCHAR(20),@LastRec-1) + ') *
FROM [##tmp_' + CONVERT(VARCHAR(40),@GUID) + ']
WHERE RowNum > ' + CONVERT(VARCHAR(20),@FirstRec) + 
' AND RowNum < ' + CONVERT(VARCHAR(20),@LastRec)
EXEC (@cmd)

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO

In an effort to gather average execution metrics for the stored procedures, I ran them three times and monitored their statistics (averages reads, writes, CPU, duration, etc.) via SQL Server Profiler.  In my tests, I assumed that a standard page contains around 50 rows and I executed the stored procedures to return the following pages:

• Page 1
• Page 10
• Page 100
• Page 1,000
• Page 10,000
• Page 100,000

Here is a sample execution for page 1 and page 10:

-- Page 1
-- dbo.proc_Paging_TempTable
exec dbo.proc_Paging_TempTable 1,50
go
-- dbo.proc_Paging_CTE
exec dbo.proc_Paging_CTE 1,50
go
-- dbo.proc_Paging_GlobalTempTable
declare @GUID uniqueidentifier
exec dbo.proc_Paging_GlobalTempTable 1,50, @GUID OUTPUT
SELECT @GUID
go
----------------------------------
-- Page 10
-- dbo.proc_Paging_TempTable
exec dbo.proc_Paging_TempTable 10,50
go
-- dbo.proc_Paging_CTE
exec dbo.proc_Paging_CTE 10,50
go
-- dbo.proc_Paging_GlobalTempTable
declare @GUID uniqueidentifier
-- Using the GUID from previous execution:
SET @GUID = '{6A71D5E2-691D-46B2-867C-8969DADB732B}'
exec dbo.proc_Paging_GlobalTempTable 10,50, @GUID OUTPUT
go

Test results

Test Analysis

Based on the test cases and the sample data, the following generalities can be made:

• The CTE appears to out perform the local and global temporary tables in most cases.
• The CTE is probably the best option when your application or users are not paging much.
• Since the CTE was introduced in SQL Server 2005, using this coding technique may be an improvement over SQL Server 2000 code that was ported directly to SQL Server 2005 or 2008 without being tuned.
• If your application is constantly paging or if your users are fetching the same data constantly, the global temporary table does offer performance enhancements after the first execution.

Next Steps

• Create your own samples in your environment and test them. Compare performance and choose the right solution for your application and users.
• If performance is still bad, consider using other methods such as: Indexed Views, Table Partitioning, summary tables, etc.
• Keep in mind that you can always use more than one method for different scenarios in your application. 
• The above examples were designed for the 'common' applications. If you have other ideas on how to better implement paging when performance is critical, please feel free to post your experiences in the MSSQLTips forum below.

About the author

Michelle Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips