Paging Table
Greg Larson wrote a really great article on how to page through data in SQL Server using techniques that were available in different versions of SQL Server http://www.databasejournal.com/features/mssql/paging-data-with-tsql.html. I really enjoyed the walk down memory lane, and appreciated the new feature added to SQL Server 2012 making paging much easier.
The goal is to return to the client only a set of data from what may potentially be a large result set, allowing the user to provide offsets, resulting in a few records at a time. This is great when you have very large datasets such that you are quite sure a user will most likely never view most of them.
Think about a Google search; it tells you that you have thousands or even millions of entries meeting some aspect of your search criteria. Would you want to wait for all of them to return to your browser before you could read even the first one?
Paging is a necessary evil in many instances. My problem is that using all the implementations available today as demonstrated by Greg, you must run the base query over and over as you select the next set of data.
I have found that in situations where the query is quite large, and the user is likely to page the results of a query frequently, that it is more efficient to use a paging table. A paging table consists of a unique set identifier (ie. what user created this batch of records or query results), the display order, and the primary key (I always include a system assigned sequence (IDENTITY) column in every table as either a primary or alternate key).
CREATE TABLE Paging
(
BatchID INT NOT NULL
,RowID INT NOT NULL
,ForeignKeyID INT NOT NULL
,CONSTRAINT PK_Paging PRIMARY KEY CLUSTERED (BatchID, RowID)
)
Using this generic structure I can join it to any table having an INT unique key, returning the results in a pre-determined order, and not have to re-compute the display order every time I request a new page. In order to make this table perform quickly I create a clustered index on the set identifier and display order columns.
Say you had a Users table containing thousands of users you wanted to query, sorted by last name, first name, and wanted to page in sets of 10 persons at a time. You could page from that table by running a query and sorting every time. Or, You could run a query once, caching the sort order in the Paging table with a query like this:
INSERT INTO Paging (BatchID, RowID, ForeignKeyID)
SELECT @BatchID
,ROW_NUMBER OVER (PARTITION BY '' ORDER BY Users.LastName, Users.FirstName) AS RowID
,Users.UserID
FROM Users
You paging SP could look like the following:
CREATE PROCEDURE Users_Get_PageByBatch_SP
(
@BatchID INT
,@LastRow INT
,@Rows INT
)
AS
SELECT Top (@Rows)
Users.UserID
,Users.FirstName
,Users.LastName
FROM Paging
JOIN Users ON Paging.ForeignKeyID = Users.UserID
WHERE Paging.RowID > @LastRow
This query will not stumble if a record is deleted from the Users table because it won’t join on missing records. Since you are returning a top (N) it will return the correct number of rows. You could modify the query to page backward as well. Another cool thing about this technique is that it doesn’t matter what sorting or filtering criteria is required for your paging. That is a separate step, getting the data into the paging table. Once there, a single query working with the required resultset columns works regardless of the filter or sort. That reduces the amount of code you have to write when paging.
Another benefit of this methodology is that it is not Engine specific. At least from a paging perspective. Once you have your batch data in the Paging table a generic SQL join works in any SQL engine.
When the batch is no longer in scope, simply delete the batch results.
DELETE Paging WHERE BatchID = @BatchID
I hope this little tip can save you some time and optimize those situations where you find you have to do paging in your database engine. Do you have other nagging problems like these? Drop me an email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Proxy Authentication Via Roles
Proxy authentication via roles in Oracle Database gives you greater flexibility when you want to realize the principle of least privilege. You don’t grant privileges directly to users but to roles. Also, when connecting through another user, you’ll be able to enable only those roles that are needed to get the job done.
Featured White Paper(s)
Putting Data to Work for Mid-Market Companies
read more)
Featured Script
dba3_TriggerDemo_SystemUser_HostName_GetDate_Audit
Demonstrates a tamper resistant update audit trigger implementation suitable for single or multiple row updates (and insertio… (read more)