Pagination solution increases the logical reads but decreases execution time

Question

I'm working on a solution for pagination and I came across this article by Aaron Bertrand: https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch.

The article suggests a CTE approach wherein instead of having all the columns in the select while sorting only sort with a single column in the select statement and then join the results back to all the tables to get all the required columns. The basic idea is to locate the page you're on first and then join all the necessary columns. This article was immensely helpful (thanks, Aaron!) and helped me reduce the execution time of my stored procedure (the current procedure has all the columns needed in the select while sorting) in cases where the users had a lot of rows. For users, who did not have many rows I did not see much improvement in performance. One of the things I noticed is that the number of logical reads increased when I took this approach and understandably so since we are hitting the same table twice (once to locate the ids and then joining to the same table to get all the other columns). Technically, shouldn't an increase in the number of logical reads increase the execution time?

If I take this approach, would it slow down the application for users with fewer rows because now I'm hitting the table twice? I did a few tests for users who less number of rows and the time difference between the current procedure and the CTE approach wasn't much but the logical reads in the CTE method almost doubled. For the users who had more rows, the CTE approach performed better than my current procedure but the CTE approach had more logical reads. Any thoughts? Thanks!

CREATE TABLE [dbo].[Customers_I]
(
  [CustomerID] [int] IDENTITY(1,1) NOT NULL,
  [FirstName] [nvarchar](64) NOT NULL,
  [LastName] [nvarchar](64) NOT NULL,
  [EMail] [nvarchar](320) NOT NULL,
  [Active] [bit] NOT NULL DEFAULT ((1)),
  [Created] [datetime] NOT NULL DEFAULT (sysdatetime()),
  [Updated] [datetime] NULL,
  CONSTRAINT [C_PK_Customers_I] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO
CREATE NONCLUSTERED INDEX [C_Active_Customers_I] 
  ON [dbo].[Customers_I]
  ([FirstName] ASC, [LastName] ASC, [EMail] ASC)
  WHERE ([Active] = 1);
GO
CREATE UNIQUE NONCLUSTERED INDEX [C_Email_Customers_I] 
  ON [dbo].[Customers_I]
  ([EMail] ASC);
GO
CREATE NONCLUSTERED INDEX [C_Name_Customers_I] 
  ON [dbo].[Customers_I]
  ([LastName] ASC, [FirstName] ASC)
  INCLUDE ([EMail]);
GO

Insert INto Customers_I values ('ABC','XYZ','abc@gmail.com',1,getdate(),getdate())
                                ,('ABC1','XYZ1','ABC1@gmail.com',1,getdate(),getdate())
                                ,('ABC2','XYZ2','ABC2@gmail.com',1,getdate(),getdate())
                                ,('ABC3','XYZ3','ABC3@gmail.com',1,getdate(),getdate())
                                ,('ABC4','XYZ4','ABC4@gmail.com',1,getdate(),getdate())
                                ,('ABC5','XYZ5','ABC5@gmail.com',1,getdate(),getdate())
                                ,('ABC6','XYZ6','ABC6@gmail.com',1,getdate(),getdate())
                                ,('ABC7','XYZ7','ABC7@gmail.com',1,getdate(),getdate())
                                ,('ABC8','XYZ8','ABC8@gmail.com',1,getdate(),getdate())
                                ,('ABC9','XYZ9','ABC9@gmail.com',1,getdate(),getdate())
                                ,('ABC10','XYZ10','ABC10@gmail.com',1,getdate(),getdate())

Go
Declare @PageNumber INT = 1,
  @PageSize   INT = 100

--Without CTE
    SELECT CustomerID, FirstName, LastName,
      EMail, Active, Created, Updated
    FROM dbo.Customers_I
      ORDER BY lastName, firstName
    OFFSET @PageSize * (@PageNumber - 1) ROWS
    FETCH NEXT @PageSize Rows Only
Go

--With CTE
Declare @PageNumber INT = 1,
  @PageSize   INT = 100
  
  ;WITH pg AS
  (
    SELECT CustomerID
      FROM dbo.Customers_I
      ORDER BY lastName, firstName
      OFFSET @PageSize * (@PageNumber - 1) ROWS
      FETCH NEXT @PageSize ROWS ONLY
  )
  SELECT c.CustomerID, c.FirstName, c.LastName,
      c.EMail, c.Active, c.Created, c.Updated
  FROM dbo.Customers_I AS c
  WHERE EXISTS (SELECT 1 FROM pg WHERE pg.CustomerID = c.CustomerID)
      ORDER BY lastName, firstName

statisics time, io:

without CTE:
Table 'Customers_I'. Scan count 1, logical reads 24, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 52 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(11 rows affected)

with CTE:
Table 'Customers_I'. Scan count 2, logical reads 46, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 40 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Answer 1

Ran into a similar problem. A semi-complex application screen with pagination that led to GBs of data being sorted.

Selecting and paginating only the PK led to a massive drop in data being sorted, and while it happened (not sure if always) that logical reads were higher, sorting only a few MBs of data was faster.

Remember, logical reads are nice, and often lead to a better solution. But in the end, CPU, memory, and time are what's more important.