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.
Estimated Execution Plan