2

When users are browsing pages of data where the data keeps changing, what is the best way to control this so the user experience is not impacted?

If I browse a catalog of 100 records with 10 per page, that is 10 pages; however, as new records are added or removed it would impact the page number the user could be on in the next page load.

I am thinking the first query would be to get a list of all ids, store those in a separate table and then do a join on that. This way new records or ones marked as deleted would not affect this. After a few hours I would delete these ids from the new table so they are not hanging around forever.

Is there a better way to achieve this? Consider this just a standard relational db structure using 3rd normal form.

1

You could use SQL Server's built-in row-versioning functionality to ensure you only see rows that haven't been touched since the start of your session. This is test code, so we'll run it in tempdb:

USE tempdb;

IF OBJECT_ID(N'dbo.CatalogList', N'U') IS NOT NULL
DROP TABLE dbo.CatalogList;
CREATE TABLE dbo.CatalogList
(
    ListKey int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT PK_CatalogList
        PRIMARY KEY CLUSTERED
    , rv rowversion NOT NULL /* rowversion is a binary(8) value */
    , ItemName varchar(30) NOT NULL
);

The table above is a very simplified product catalog, with a rowversion column named rv. rowversion is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. See rowversion-transact-sql for more details.

The rv column can be used to eliminate newly added/updated rows from being seen my the client. To show how that works, we'll add some initial data to the catalog list, and create a stored procedure to give us paginated results:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item1')
    , ('Item2');

GO
IF OBJECT_ID(N'dbo.GetCatalogItems', N'P') IS NOT NULL
DROP PROCEDURE dbo.GetCatalogItems;
GO
CREATE PROCEDURE dbo.GetCatalogItems
(
    @rowversion binary(8) = NULL
    , @PageNumber int = 1
    , @PageSize int = 100
)
AS
BEGIN
    SET NOCOUNT ON;
    IF @rowversion IS NULL SET @rowversion = @@DBTS;
    SELECT cl.*
    FROM dbo.CatalogList cl
    WHERE cl.rv <= @rowversion
    ORDER BY cl.ListKey
    OFFSET @PageSize * (@PageNumber -1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
    RETURN @rowversion;
END
GO

Next, we'll run the stored procedure to show the first result:

DECLARE @rv binary(8);
EXEC @rv = dbo.GetCatalogItems @PageNumber = 1, @PageSize = 1;

@rv is used to save the current rowversion value, for use in subsequent calls.

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 1       ║ 0x00000000000007EE ║ Item1    ║
╚═════════╩════════════════════╩══════════╝

Now, we'll add another row to the table, and show the table contents:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item3');

SELECT *
FROM dbo.CatalogList;

Table contents:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 1       ║ 0x00000000000007EE ║ Item1    ║
║ 2       ║ 0x00000000000007EF ║ Item2    ║
║ 3       ║ 0x00000000000007F0 ║ Item3    ║
╚═════════╩════════════════════╩══════════╝

Now, we'll re-run the stored procedure, passing in the @rv obtained by the initial call:

EXEC @rv = dbo.GetCatalogItems @rowversion = @rv, @PageNumber = 2, @PageSize = 1;

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 2       ║ 0x00000000000007EF ║ Item2    ║
╚═════════╩════════════════════╩══════════╝

And one more time, to see if we can see the third row:

EXEC @rv = dbo.GetCatalogItems @rowversion = @rv, @PageNumber = 3, @PageSize = 1;

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
╚═════════╩════════════════════╩══════════╝

As you can see, nothing is returned by the third call, since we're limited to rows that existed at the start of the initial call to the stored procedure.

Using rowversion like this will have the sideffect of removing updated rows from the result set. This might not be desirable, however if we replace the rowversion column with datetime columns to record creation and modification times, we can easily get around this limitation.

IF OBJECT_ID(N'dbo.CatalogList', N'U') IS NOT NULL
DROP TABLE dbo.CatalogList;
CREATE TABLE dbo.CatalogList
(
    ListKey int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT PK_CatalogList
        PRIMARY KEY CLUSTERED
    , ItemName varchar(30) NOT NULL
    , CreateDate datetime NOT NULL
        CONSTRAINT DF_CatalogList_CreateDate
        DEFAULT (GETDATE())
    , UpdateDate datetime NOT NULL
        CONSTRAINT DF_CatalogList_UpdateDate
        DEFAULT (GETDATE())
);
GO

Here, I'll add a simple trigger to the table to ensure the UpdateDate column is always updated whenever any data in the table is updated:

CREATE TRIGGER dbo.CatalogListTrigger
ON dbo.CatalogList
AFTER UPDATE
AS
BEGIN
    ;WITH src AS (
        SELECT cl.*
        FROM dbo.CatalogList cl
            INNER JOIN inserted i ON cl.ListKey = i.ListKey
    )
    UPDATE src
    SET UpdateDate = GETDATE();
END
GO

Initial test data:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item1')
    , ('Item2');

The new stored procedure:

IF OBJECT_ID(N'dbo.GetCatalogItems', N'P') IS NOT NULL
DROP PROCEDURE dbo.GetCatalogItems;
GO
CREATE PROCEDURE dbo.GetCatalogItems
(
    @InitialDate datetime
    , @PageNumber int = 1
    , @PageSize int = 100
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT cl.*
    FROM dbo.CatalogList cl
    WHERE cl.CreateDate <= @InitialDate
    ORDER BY cl.ListKey
    OFFSET @PageSize * (@PageNumber -1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
END
GO

The first run of the stored procedure:

DECLARE @d datetime = GETDATE();
EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 1, @PageSize = 1;
╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Item1    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

Inserting an additional row:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item3');

SELECT *
FROM dbo.CatalogList;
╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Item1    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
║ 2       ║ Item2    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
║ 3       ║ Item3    ║ 2017-07-27 14:31:35.353 ║ 2017-07-27 14:31:35.353 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝
EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 2, @PageSize = 1;
╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 2       ║ Item2    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

The extra row is not returned by the stored procedure:

EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 3, @PageSize = 1;
╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

We update Item1:

UPDATE dbo.CatalogList
SET ItemName = 'Updated Item1'
WHERE ListKey = 1;

And go back to page 1 in the results, we see the updated item:

EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 1, @PageSize = 1;
╔═════════╦═══════════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName      ║ CreateDate              ║ UpdateDate              ║
╠═════════╬═══════════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Updated Item1 ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.700 ║
╚═════════╩═══════════════╩═════════════════════════╩═════════════════════════╝


For a better fetch/offset solution, see this post by Aaron Bertrand

0

This is a bit of a rant but I had an application that could have 100 people making edits and loading over 100,000 rows during working hours. It was smoking fast. I only gave them a next prev and they insisted on a page number and I told them page number was typically only valid for a few seconds when the system was active. They said but our old system did. I told them your old system took a static snapshot of 100,000 and a user did not see any updates. The said we need a goto page so I did it. Sure enough a user blew up when goto page 60 was not the same after lunch.

I just take the values of the sort for the last row and then fetch the next X.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.