How to optimize SQL Server query with Filters and Pagination?

Question

I need optimization recommendations (Don't have access to modify index) for this query, I ommited some variable names for security reason, but this is basically my query, it fluctuates around 300 to 900 milliseconds to complete, when I use c# it's a little faster, but I want to get below the 100 milliseconds. SQL SERVER 2008

any ideas?

CREATE PROCEDURE [dbo].MyProcedure (  
    PARAMETERS!!!
  AS   
 BEGIN  

SET NOCOUNT ON;   


  create  TABLE #SearchTable  
  (  
  TEMP TABLE PARAMETERS!!!
  )  

   insert into #SearchTable (COLUMNS!!!)  

    SELECT DISTINCT  MYDATA!!!
      FROM VIEW!!!  
     WHERE ID =    @ID    
            AND ((( @Variable is  null)            or (VARIABLE LIKE @Variable + '%' ) )
            AND ((( @Variable is  null)            or (VARIABLE LIKE @Variable + '%' ) )
            AND ((( @Variable is  null)            or (VARIABLE LIKE @Variable + '%' ) )
            AND ((( @Variable is  null)            or (VARIABLE LIKE @Variable + '%' ) )
            AND ((( @Variable is  null)            or (VARIABLE LIKE @Variable + '%' ) )
            AND ((( @Variable is  null)            or (VARIABLE LIKE @Variable + '%' ) )



 declare @sql NVARCHAR(max)  
 set @sql = '  
  select * from (  
   select ROW_NUMBER() over (order by '+ @orderBy +') as RowNumber,*   
    from #SearchTable) table
    WHERE (RowNumber > '+ cast(((@CurrentPage -1) * @PageSize) as varchar(max)) +'  
  AND RowNumber <= '+ cast( ((@CurrentPage) * @PageSize) as varchar(max)) +'  
OR ' +  cast( (@PageSize) as varchar(max)) +' = 0)' 

 EXEC sp_executesql @sql

 --exec(@sql)

  select @totalCount = count(*) from #SearchTable  
   print @totalCount

Dave · Accepted Answer · 2018-02-08 14:30:28Z

0

Sorry...needed to edit ... that's what you get for "top of head"...

Off the top of the head for pagination, You've already got dynamic sql in place so would go with 'select TOP ' + CAST(@NumberItemsPerPage AS nvarchar(10)) + ' * FROM...WHERE [RowCount] >= ' + CAST((@NumberItemsPerPage * @PageCount) AS nvarchar(10)) + ' AND [RowCount] < ' + CAST((@NumberItemsPerPage * @PageCount)+@NumberItemsPerPage AS nvarchar(10)) as long as you can make sure your order of items is preserved...that should give you the paging aspect.

As for performance, welcome to the wonderful world of trial and error. Indexes, keys and the architecture all come into play so my best advice would be to try different methods of the query, use query analyzer to see if any additional indexes would work... Dave

edited Feb 8, 2018 at 14:30

answered Feb 8, 2018 at 14:24

Dave

7401 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3044096 Over a year ago

Yeah, there's no other option, trial and error is the only way I guess

Collectives™ on Stack Overflow

How to optimize SQL Server query with Filters and Pagination?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related