Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server stored procedures to page large tables or queries


By:   |   Last Updated: 2009-03-03   |   Comments (4)   |   Related Tips: More > 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.


Example #1 - Using a session temporary table (#temp_table)

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

CREATE PROCEDURE dbo.proc_Paging_TempTable
(
@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)

-- Create a temporary table
CREATE TABLE #TempItems
(RowNum int IDENTITY PRIMARY KEY,
Title nvarchar(100),
Publisher nvarchar(50),
AuthorNames nvarchar(200),
LanguageName nvarchar(20),
FirstLine nvarchar(150),
CreationDate smalldatetime,
PublishingDate smalldatetime,
Popularity int)

-- Insert the rows into the temp table
-- We query @LatRec + 1, to find out if there are more records
INSERT INTO #TempItems (Title, Publisher, AuthorNames, LanguageName,
FirstLine, CreationDate, PublishingDate, Popularity)
SELECT TOP (@LastRec-1)
s.Title, m.Publisher, s.AuthorNames, l.LanguageName,
m.FirstLine, m.CreationDate, m.PublishingDate, m.Popularity
FROM dbo.Articles m
INNER JOIN dbo.ArticlesContent s
ON s.ArticleID = m.ID
LEFT OUTER JOIN dbo.Languages l
ON l.ID = m.LanguageID
ORDER BY m.Popularity desc

-- Return the set of paged records
SELECT *
FROM #TempItems
WHERE RowNum > @FirstRec
AND RowNum < @LastRec

-- Drop the temp table
DROP TABLE #TempItems

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO
 


Example #2 - Using a Common Table Expression (CTE)

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


Test Cases

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

Average Reads                
                 
  Page # 1 10 100 1000 10000 100000 TOTAL
Example #                
# 1   543 6590 58175 621039 6409168 6168499 13264014
# 2   365 3258 33128 354052 4227203 4286209 8904215
# 3   154449 69 69 107 69 7 154770
Average Writes                
                 
  Page # 1 10 100 1000 10000 100000 TOTAL
Example #                
# 1   3 25 260 2695 28333 29972 61288
# 2   0 0 0 0 0 0 0
# 3   27483 0 0 0 0 0 27483
Average CPU                
                 
  Page # 1 10 100 1000 10000 100000 TOTAL
Example #                
# 1   15 16 172 4578 19422 14703 38906
# 2   16 16 93 1797 6230 6547 14699
# 3   15594 0 0 0 0 0 15594
Average Duration                
                 
  Page # 1 10 100 1000 10000 100000 TOTAL
Example #                
# 1   376 2769 5816 58087 67599 42007 176654
# 2   71 107 165 2738 22173 8103 33357
# 3   77420 268 79 208 125 139 78239

 


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.


Last Updated: 2009-03-03


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 21, 2016 - 2:47:45 AM - Savas Ozturk Back To Top

 

In your methods you didnt mention about how to get total rows? But in paging system total rows is a must, thank you


Monday, August 27, 2012 - 10:31:57 AM - Zwer Back To Top

Very good article regarding effective paging could be found here

http://www.4guysfromrolla.com/webtech/042606-1.shtml

 


Thursday, March 05, 2009 - 1:10:26 PM - jerryhung Back To Top

Curious about using ROW_NUMBER() method starting in 2005/2008

should be similar to CTE, but no #temp required


Tuesday, March 03, 2009 - 1:00:29 PM - Sukhotinsky Back To Top

No need to create temp tables with all the needed columns, not to say joined from many tables

Create it only for indexes (that is for primary key of your dbo.Articles)

When you have a set of indexes in the temp table, you can easily fetch all other fields.

 


Learn more about SQL Server tools