使用行号时，SQL中的分页速度非常慢

Question

我正在尝试批量获取数据。这一次进行得很快：

select top 10 * from Table1 where (subject like '%Recap%' or subject like '% CP %') and Body like '%Details:%' and  ToAddr like '[e-mail]%'

现在，当尝试批量获取这些数据时，大约需要两分钟。查询非常：

SELECT * FROM ( 
select*, ROW_NUMBER() OVER (ORDER BY name) as row from Table1 where (subject like '%Recap%' or subject like '% CP %') and Body like '%Details:%' and  ToAddr like '[e-mail]%'
) a WHERE a.row > 10 and a.row <= 20 ​ 

我是不是拿错批次了？

---

刚刚发现，即使这很慢：

select top 10 *, ROW_NUMBER() OVER (ORDER BY id) as row from Table1 where (subject like '%Recap%' or subject like '% CP %') and Body like '%Details:%' and  ToAddr like '%xxx%'​ 

Answer 1

将其放在ORDER BY子句后面的末尾：

OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

另外，花点时间阅读https://use-the-index-luke.com/sql/partial-results/fetch-next-page

Answer 2

根据注释。你可以试试这种

declare
@pagesize     int=10,
@pagenumber   int=2;
select * 
from Table1 
where (subject like '%Recap%' or subject like '% CP %') 
and Body like '%Details:%' 
and  ToAddr like '[e-mail]%'
order by [name]
offset @pagesize * (@pagenumber - 1) rows
fetch next @pagesize rows only;