How to make efficient pagination with total count

Question

We have a web application which helps organizing biological experiments (users describe experiment and upload experiment data). In the main page, we show first 10 experiments and then below Previous Next 1 2 3 .. 30.

I bugs me how to make efficient total count and pagination. Currently:

select count(id) from experiments; // not very efficient in large datasets

but how does this scale when dealing with large datarecords > 200.000. I tried to import random experiments to table, but it still performs quite ok (0.6 s for 300.000 experiments).

The other alternative I thought about is to add addtional table statistics (column tableName, column recordsCount). So after each insert to table experiments I would increase recordsCount in statistics (this means inserting to one table and updating other, using sql transaction of course). Vice versa goes for delete statement (recordsCount--).

For pagination the most efficient way is to do where id > last_id as sql uses index of course. Is there any other better way?

In case results are to be filtered e.g. select * from experiment where name like 'name%', option with table statistics fails. We need to get total count as: select count(id) from experiment where name like 'name%'.

Application was developed using Laravel 3 in case it makes any difference.

I would like to develop pagination that always performs the same. Records count must not affect pagination nor total count of records.

Answer 1

Please have the query like below:

 CREATE PROCEDURE [GetUsers]    
(    
 @Inactive  Bit = NULL,    
 @Name   Nvarchar(500),    
 @Culture   VarChar(5) = NULL,    
 @SortExpression VarChar(50),    
 @StartRowIndex Int,    
 @MaxRowIndex Int,  
 @Count INT OUTPUT      
)    
AS    
BEGIN    



  SELECT ROW_NUMBER()    
   OVER    
   (    
    ORDER BY    

    CASE WHEN @SortExpression = 'Name' THEN [User].[Name] END,    
    CASE WHEN @SortExpression = 'Name DESC' THEN [User].[Name] END DESC    

   ) AS RowIndex, [User].*    
   INTO #tmpTable   
   FROM [User] WITH (NOLOCK)    
   WHERE (@Inactive IS NULL OR [User].[Inactive] = @Inactive)       
   AND (@Culture IS NULL OR  [User].[DefaultCulture] = @Culture)    
   AND [User].Name LIKE '%' + @Name + '%'     



 SELECT *    
 FROM #tmpTable WITH (NOLOCK)    
 WHERE  #tmpTable.RowIndex > @StartRowIndex     
 AND #tmpTable.RowIndex < (@StartRowIndex + @MaxRowIndex + 1)    

 SELECT @Count = COUNT(*) FROM #tmpTable             

 IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL  DROP TABLE #tmpTable;    
END