使用SQL Server ROW_NUMBER（）进行分页时性能不佳

debugcn 发表于 Dev

伊卡博特

我正在尝试建立一个分页机制。我正在使用创建SQL的ORM，如下所示：

SELECT * FROM 
    (SELECT t1.colX, t2.colY
         ROW_NUMBER() OVER (ORDER BY t1.col3) AS row 
     FROM Table1 t1
     INNER JOIN Table2 t2
     ON t1.col1=t2.col2
    )a 
WHERE row >= n AND row <= m

表1的行数超过50万，表2的记录数超过1万

我直接在SQL Server 2008 R2 Management Studio中执行查询。子查询执行需要2-3秒的时间，但整个查询需要> 2分钟的时间。

我知道SQL Server 2012接受该OFFSET .. LIMIT ..选项，但是我无法升级该软件。

谁能帮助我改善查询性能，或建议其他可通过ORM软件强加的分页机制。

Update:

测试Roman Pekar的解决方案（请参阅解决方案注释）证明ROW_NUMBER（）可能不是性能问题的原因。不幸的是，问题仍然存在。

谢谢

米卡尔·埃里克森（Mikael Eriksson）

据我了解，您的表结构来自注释。

create table Table2
(
  col2 int identity primary key,
  colY int
)

create table Table1
(
  col3 int identity primary key,
  col1 int not null references Table2(col2),
  colX int
)

这意味着从返回的行Table1永远不能被join过滤，Table2因为Table1.col1is not null。联接不能Table2将行添加到结果中，因为Table2.Col2它是主键。

然后，您可以重写查询以Table1在加入之前生成行号Table2。并且在联接之前还应用了where子句，Table2这意味着您将只查找Table2实际上属于结果集的行。

select T1.colX,
       T2.colY,
       T1.row
from
    (
    select col1,
           colX,
           row_number() over(order by col3) as row
    from Table1
    ) as T1
  inner join Table2 as T2
    on T1.col1 = T2.col2
where row >= @n and row <= @m

SQL小提琴

我不知道是否可以让您的ORM（由Mindscape编写的Lightspeed）来生成这样的分页查询，而不是现在的查询。

来自此答案的查询计划：在此处输入图片说明