sql-server - 是否可以在恒定时间内在 SQL Server (2012) 中进行数据分页？

Question

我一直在研究我负责的系统性能问题的解决方案，我认为至少部分问题是由数据库查询性能引起的。我们使用存储过程以非常标准的方式查询数据“页面”。但是，当数据集变大时，这种分页的成本似乎更高。

鉴于这个简单的表格填充了示例数据:

create table Data (
Value uniqueidentifier not null,
constraint PK_Data primary key clustered (Value)
)

insert into Data 
-- SeedTable has ~2M rows
select newid() from SeedTable 

这个存储过程返回分页数据:(这显然需要 Sql2012，尽管使用 ROW_NUMBER() 的 Sql2008 风格表现相同):

create proc
GetDataPage @Offset int, @Count int
as

select Value
from Data
order by Value
offset @Offset rows
fetch next @Count rows only

然后我用这段 C# 代码测试了这个存储过程的性能:

const int PageSize = 50;
const int MaxCount = 50000;

using (var conn = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Integrated Security=true;")) {

  conn.Open();
  int a = 0;
  for (int i = 0; ; i += PageSize) {
    using (var cmd = conn.CreateCommand()) {
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
      cmd.CommandText = "GetDataPage";
      var oid = cmd.CreateParameter();

      var offset = cmd.CreateParameter();
      offset.Value = i;
      offset.ParameterName = "Offset";
      cmd.Parameters.Add(offset);

      var count = cmd.CreateParameter();
      count.Value = PageSize;
      count.ParameterName = "Count";
      cmd.Parameters.Add(count);

      var sw = Stopwatch.StartNew();
      int c = 0;
      using(var reader = cmd.ExecuteReader()) {
        while (reader.Read()) {
          c++;
        }              
      }
      a += c;

      sw.Stop();
      Console.WriteLine(sw.ElapsedTicks + "\t" + a);

      if (c < PageSize || a >= MaxCount)
        break;
    }
  }
}

当我绘制此代码的输出图表时，我得到以下信息:

我原以为在 SQL 中像这样的分页会具有恒定的时间性能，或者在最坏的情况下可能是对数的，但从图表中可以清楚地看出性能是线性的。

是否有任何特殊技巧(提示)可以使这项工作更好？

是否有另一种可能更快的方法？

其他数据库的行为方式是否相同？

---

更改实验代码以使用 Kevin Suchlicki 建议的“page from”技术，结果如下:

印象深刻。这种表现看起来更像我所期望/想要的。现在我只需要弄清楚我是否可以将其应用于我的实际问题。潜在的问题是它不允许对数据进行“随机访问”，而是只允许向前访问类似游标的访问。我知道我正在做的事情看起来一定违反了良好数据库设计的每一个概念。

Answer 1

最明显的可能性在于应用设计本身。为您的用户提供过滤条件。用户通常对他们要查找的内容有所了解，并且不愿意翻阅 1000 页的返回结果。您在 Google 搜索中多久翻到第 10 页？

话虽如此，您可以尝试存储上一页返回的最后一行的 id(聚簇索引值)，并在您的 SQL where 子句中使用它。如果您需要允许对不同的键(例如姓氏)进行排序，则存储聚簇索引 id 值和上一页的最终姓氏。然后像这样编写你的 SQL(你总是需要对你的键字段和聚集的 id 值进行排序，以便在重复键值的情况下确定地对记录进行排序):

select top (@count) Id, LastName, FirstName
from Data
where LastName >= @previousLastName and Id > @previousId
order by LastName, Id

您还想为所有可能是排序键的字段建立索引。不确定上面的执行方式，但我希望对索引字段的搜索执行 O(log n)。

另一种选择可能是在每次源数据更改时，在幕后按顺序使用行值持久保存完整列表，并让应用从持久化的表中提取数据。

好问题...请告诉我们结果如何!