title: How to improve large table paging speed in SQL Server 2008
body: <p>I have a table called <code>Users</code> with 10 million records in it. This is the table structure:</p>
  
  <pre><code>CREATE TABLE [dbo].[Users](
      [UsersID] [int] IDENTITY(100000,1) NOT NULL,
      [LoginUsersName] [nvarchar](50) NOT NULL,
      [LoginUsersPwd] [nvarchar](50) NOT NULL,
      [Email] [nvarchar](80) NOT NULL,
      [IsEnable] [int] NOT NULL,
      [CreateTime] [datetime] NOT NULL,
      [LastLoginTime] [datetime] NOT NULL,
      [LastLoginIp] [nvarchar](50) NOT NULL,
      [UpdateTime] [datetime] NOT NULL,
   CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
  (
      [UsersID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>I have a nonclustered index on the <code>UpdateTime</code> column.</p>
  
  <p>The paging sql:</p>
  
  <pre><code>;WITH UserCTE AS (
     SELECT * FROM 
      (SELECT 
          ROW_NUMBER() OVER (ORDER BY UpdateTime DESC) AS row,UsersID as rec_id -- select primary key only
      FROM 
          dbo.Users WITH (NOLOCK)
      ) A WHERE row BETWEEN 9700000 AND 9700020
  )
  SELECT
     *
  FROM
      dbo.Users WITH (NOLOCK) WHERE UsersID IN (SELECT UserCTE.rec_id FROM UserCTE)
  </code></pre>
  
  <p>The query above:</p>
  
  <pre><code>SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 3 ms.
  (21 row(s) affected)
  
  SQL Server Execution Times:
  CPU time = 2574 ms,  elapsed time = 3549 ms.
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/3rvNu.jpg" alt="enter image description here"></p>
  
  <p>Anyone give me some suggests about how to improve paging speed will appreciate. Thanks!</p>