如何在SQL中有效返回记录数？

Question

我在 Windows Server 2008 Enterprise 上使用 SQL Server 2008 Enterprise。我使用以下代码返回查询的一部分数据，以在我的网络应用程序上实现分页（即向上/向下翻页以在每个页面上显示部分结果，如 Google 搜索结果分页）（我使用 pageCount 作为数字每页显示的结果数，startPos 为结果的起始编号）。例如，pageCount 10 表示每页显示 10 个结果，startPos = 0 表示第一页，startPos = 1 表示第二页，等等。

我的问题是如何在我的场景中有效地获取结果总数？我主要关心的是如何实现分页（即只触摸结果的一部分）并同时检索结果总数？

SELECT *
 FROM   (SELECT
    t.foo, t.goo, ROW_NUMBER() OVER (order by t.zoo DESC ) AS rowNum
   FROM
    dbo.mycorp  t

   WHERE
    (t.foo LIKE '%'+@search+'%'
    or t.foo  LIKE '%'+@search+'%'
    )
    ) tt
    WHERE  tt.rowNum between @startPos and  @pageCount + @startPos-1

提前致谢， 乔治

Answer 1

这将意味着冗余列，每行具有相同的值：

WITH cte AS (
    SELECT t.foo, 
           t.goo, 
           ROW_NUMBER() OVER (ORDER BY t.zoo DESC ) AS rowNum,
           (SELECT COUNT(*)
              FROM dbo.mycorp
             WHERE t.foo LIKE '%'+@search+'%'
                OR t.foo  LIKE '%'+@search+'%') AS total_count
      FROM dbo.mycorp  t
     WHERE t.foo LIKE '%'+@search+'%'
        OR t.foo  LIKE '%'+@search+'%')
SELECT c.foo,
       c.goo,
       c.total_count
  FROM cte c
 WHERE c.rowNum BETWEEN @startPos 
                    AND @pageCount + @startPos-1

搜索文本的更有效方法是使用 SQL Server 的全文搜索 (FTS) 功能。

Answer 2

如果您的数据访问技术支持的话，我会考虑使用多个记录集（我知道 ADO.NET 确实支持这一点）。

在查询后包含以下内容

SELECT COUNT(*) AS TotalRecordCount
 FROM   dbo.mycorp  t 
   WHERE 
    (t.foo LIKE '%'+@search+'%' 
    or t.foo  LIKE '%'+@search+'%' 
    ) 
    ) tt

在数据访问层中，您需要在处理搜索结果后切换到下一个记录集才能检索总计数。使用 ADO.NET，这需要调用 dataReader.NextResult()。

最重要的是衡量绩效。我发现在某些情况下，使用单独的 select 语句而不是子查询可以提高性能，因为查询优化器能够更好地优化查询。无论哪种方式，您都需要在搜索列上建立适当的索引。