title: How to optimize SQL Server query with Filters and Pagination?
body: <p>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</p>
  
  <p>any ideas? </p>
  
  <pre><code>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 &gt; '+ cast(((@CurrentPage -1) * @PageSize) as varchar(max)) +'  
    AND RowNumber &lt;= '+ 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  
  </code></pre>