mssql 2008 分页查询优化

背景：

旧系统 只采用了ROW_NUMBER() OVER(order by xxx desc)方式分页。且多表关联查询，即使建立了索引，性能也不乐观。

总记录数在20w+，分有1万+以上。第一页查询就很慢，远超10s

sql如下：

 select * from (
        select
         ROW_NUMBER() OVER(ORDER BY a.id desc) AS rowNum,
        a.*,b.ss
from table a left join table b on b.id= a.bbid
        ) as myTable
        where rowNum between #rowNumStart# and #rowNumEnd#

优化如下:

 select * from (
        select
        top $rowNumEnd$ ROW_NUMBER() OVER(ORDER BY a.id desc) AS rowNum,
        a.*,b.aaid from tablea a,tableb b on b.xxx=a.yy
        ) as myTable
        where rowNum between #rowNumStart# and #rowNumEnd#

优化后的结果：在几百也分页后就出现明显的性能下滑，执行时间大概4s+

转载于:https://my.oschina.net/loyin/blog/3101838