Reduce query time for higher offset in sql server

5 votes

1 answer

11993 views

sql-server performance pagination offset-fetch query-performance

                          Currently, I have table base_voter with data around 100M of dummy data. I have stored procedure as follows:

    CREATE Procedure [dbo].[spTestingBaseVoter]    
       @SortColumn    NVARCHAR(128) = N'name_voter',    
       @SortDirection VARCHAR(4)    = 'asc',    
       @offset INT,      
       @limit INT    
    As    
    Begin    
      SET NOCOUNT ON;    
         
      -- reject any invalid sort directions:    
      IF LOWER(@SortDirection) NOT IN ('asc','desc')    
      BEGIN    
        RAISERROR('Invalid parameter for @SortDirection: %s', 11, 1, @SortDirection);    
        RETURN -1;    
      END     
         
      -- reject any unexpected column names:    
      IF LOWER(@SortColumn) NOT IN (N'name_voter', N'home_street_address_1', N'home_address_city')    
      BEGIN    
        RAISERROR('Invalid parameter for @SortColumn: %s', 11, 1, @SortColumn);    
        RETURN -1;    
      END     
         
      --SET @SortColumn = QUOTENAME(@SortColumn);    
         
      DECLARE @sql NVARCHAR(MAX);    
         
      SET @sql = N'SELECT id, name_voter, home_street_address_1, home_address_city    
                 FROM dbo.base_voter    
        WITH(NOLOCK)    
        WHERE deleted_at IS NULL'    
                     
      SET @sql = @sql + N' ORDER BY ' + @SortColumn + ' ' + @SortDirection +    
        ' OFFSET @OF ROWS     
        FETCH NEXT @LIM ROWS ONLY ';    
         
        
        
      EXEC sp_executesql @sql,    
         N'@OF int,@LIM int',      
         @OF=@offset, @LIM=@limit     
    End

To make query faster, I have also created index as :

    CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
      ON dbo.base_voter (name_voter asc,home_street_address_1, home_address_city)
      WHERE deleted_at IS NULL ;

By creating this non-clustered index I have reduced the query time drastically. However, it does not applied same for the query with higher offset.

For example with:

    Execute spTestingBaseVoter name_voter,asc,9999950,50

Is there something I am doing wrong, which is causing this performance issue? Or, it is more compulsory to create another index in descending order.

Let, me know if there is any better way to tackle this situation, which might decrease query time drastically.

**Update:**

Estimated Execution Plan


                        

Asked by Saroj Shrestha (195 rep)

Oct 17, 2018, 07:58 AM
Last activity: Dec 12, 2018, 11:34 AM

Reduce query time for higher offset in sql server

Related Questions