Windowing offset based on time stamp

I'm writing a query that will be used to page results for a social feed. The concept is the mobile app will request N items, and provide a starting datetime which I have called @CutoffTime below. The purpose of the cutoff time is to establish when the paging window should start. The reason why we are using a time stamp instead of a row offset is the time stamp will let us page from a consistent place when getting older posts even if newer social content is added.

Since the social feed items can be from yourself or your friends I'm using a UNION to combine the results from those two groups. Originally I tried the TheQuery_CTElogic without the UNION and it was dog slow.

This is what I have done (including pertinent table schema):

```
CREATE TABLE [Content].[Photo]
(
[PhotoId] INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
[Key] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
[FullResolutionUrl] NVARCHAR(255) NOT NULL,
[Description] NVARCHAR(255) NULL,
[Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
);

CREATE TABLE [Content].[UserPhotoAssociation]
(
[PhotoId] INT NOT NULL,
[UserId] INT NOT NULL,
[ShowInSocialFeed] BIT NOT NULL DEFAULT 0,

CONSTRAINT [PK_UserPhotos] PRIMARY KEY ([PhotoId], [UserId]),
CONSTRAINT [FK_UserPhotos_User] FOREIGN KEY ([UserId])
REFERENCES [User].User,
CONSTRAINT [FK_UserPhotos_Photo] FOREIGN KEY ([PhotoId])
REFERENCES [Content].Photo
);

CREATE TABLE [Content].[FlaggedPhoto]
(
[FlaggedPhotoId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[PhotoId] INT NOT NULL,
[FlaggedBy] INT NOT NULL,
[FlaggedOn] DATETIME2(0) NOT NULL DEFAULT SYSDATETIME(),
[FlaggedStatus] INT NOT NULL DEFAULT 1,
[ReviewedBy] INT NULL,
[ReviewedAt] DATETIME2(0) NULL

CONSTRAINT [FK_Photos_PhotoId_to_FlaggedPhotos_PhotoId] FOREIGN KEY ([PhotoId])
REFERENCES [Content].Photo,
CONSTRAINT [FK_FlaggedPhotoStatus_FlaggedP

I suggest you go down a different path with this. It looks like a pattern of select top() .... order by should be sufficient.

select top(@NumberOfItems)
  P.PhotoId,
  P.[Key],
  P.Created
from Content.Photo as P
  inner join Content.UserPhotoAssociation as UPA
    on P.PhotoId = UPA.PhotoId
where
  -- Older than CutoffTime
  P.Created < @CutoffTime and
  (
  -- My photos
  UPA.UserId = @UserId or
  -- Photos by someone monitored by me
  UPA.UserID in (
                select CC.Monitored
                from [User].CurrentConnections as CC
                where CC.Monitor = @UserId
                )
  )
order by P.Created desc, P.[Key]

To handle cases where Created happens to be equal you need to include the Key value in the cutoff handling. Perhaps a where clause something like this.

P.Created  @CutoffKey)

StackExchange Database Administrators Q#112173, answer score: 5