如何用总计数进行高效分页

Question

我们有一个web应用程序，帮助组织生物实验（用户描述实验和上传实验数据）。在主页中，我们展示了前10个实验，然后在上一个下一个1 2 3..30.

我虫子我如何使有效的总计数和分页。目前：

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

我想开发分页，总是执行相同的。记录计数不得影响分页或记录总数。

Answer 1

请进行如下查询：

 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