NHibernate 与 SQL Server 分页

Question

当使用 SetFirstResult(start) 和 SetMaxResults(count) 方法实现分页时，我注意到生成的查询仅执行 select top count * from some_table< /code> 并且它不考虑 start 参数，或者至少不考虑数据库级别。看起来，如果我指示 NHibernate 执行以下查询：

var users = session.CreateCriteria<User>()
                   .SetFirstResult(100)
                   .SetMaxResults(5)
                   .List<User>();

105 条记录将在数据库服务器和应用程序之间传输，应用程序将小心地删除前 100 条记录。对于包含许多行的表，这可能是一个问题。

我已经验证，使用 SQLite 数据库 NHibernate 可以利用 OFFSET 和 < code>LIMIT 关键字在数据库级别过滤结果。我知道 SQL Server 2000 中没有与 OFFSET 关键字和 Oracle 的 ROWNUM 等效的关键字，但是有什么解决方法吗？ SQL Server 2005/2008 怎么样？

Answer 1

T-SQL 是 Microsoft SQL Server 使用的 SQL 语言的变体，没有 limit 子句。它有一个 select top {...} 修饰符，您可以看到 NHibernate 在 SQL Server 2000 中利用了该修饰符。

在 SQL Server 2005 中，Microsoft 引入了 Row_Number() over (order by {. ..}) 函数，它可以用作 limit 子句的替代，您可以看到 NHibernate 在 SQL Server 2005/2008 中利用了该功能。

SQLite 的查询可能看起来像，

select c.[ID], c.[Name]
from [Codes] c
where c.[Key] = 'abcdef'
order by c.[Order]
limit 20 offset 40

而 SQL Server 2005 的类似查询可能看起来像

select c.[ID], c.[Name]
from (
    select c.[ID], c.[Name], c.[Order]
        , [!RowNum] = Row_Number() over (order by c.[Order])
    from [Codes] c
    where c.[Key] = 'abcdef'
) c
where c.[!RowNum] > 40 and c.[!RowNum] <= 60
order by c.[Order]

，或者使用公共表表达式，它可能看起来像

with
    [Source] as (
        select c.[ID], c.[Name], c.[Order]
            , [!RowNum] = Row_Number() over (order by c.[Order])
        from [Codes] c
        where c.[Key] = 'abcdef'
    )
select c.[ID], c.[Name]
from [Source] c
where c.[!RowNum] > 40 and c.[!RowNum] <= 60
order by c.[Order]

SQL Server 2000 中也有一种方法可以做到这一点

select c.[ID], c.[Name]
from (
    select top 20 c.[ID], c.[Name], c.[Order]
    from (
        select top 60 c.[ID], c.[Name], c.[Order]
        from [Codes] c
        where c.[Key] = 'abcdef'
        order by c.[Order]
    ) c
    order by c.[Order] desc
) c
order by c.[Order]

Answer 2

Nhibernate 足够聪明，可以优化查询。如果您选择前 10 行，它将使用 TOP 语句。如果您选择的不是第一行，那么它将使用 RowNum。

在sql 2000中没有RowNum函数，这就是为什么通常的查询不可能选择所需的行数。据我所知，对于 sql 2000，使用了这样的优化视图。

在 sql 2005/2008 中，查询将仅选择所需的行。

---