使用SQLServer的ROW_NUMBER()实现分页查询性能问题

Question

我使用SQL语句查询出我想要的数据，使用了SQLServer的ROW_NUMBER()实现分页，但是遇到了性能问题，请大侠指点
 
数据量说明：
1）查询的T_Medicine表中20万左右的记录；
2）最终查询出符合条件的记录数为仅26条
3）每页显示20条记录
4）表T_Medicine中AuthorizedCode有建立非聚集索引
 语句如下（使用了SQL的ROW_NUMBER()来实现分页）
SELECT * from (
SELECT ROW_NUMBER() over(order by AuthorizedCode Desc) as rowId,MedicineId,NameCN,AuthorizedCode from T_Medicine 
WHERE AuthorizedCode IN (
SELECT AuthorizedCode FROM(
SELECT AuthorizedCode,NameCN FROM dbo.Medicine WHERE  AuthorizedCode!='' GROUP BY AuthorizedCode,NameCN
) tmp GROUP BY AuthorizedCode HAVING COUNT(*) > 1)) as t执行我上面的语句的结果：
1）不加分页语句（如上语句），可正确查询出26条记录，时间为2秒左右
2）如果我查询第一页--即在上面语句最后加上where rowId between 1 and 20，正确查询出20条记录，大概要4秒
3）如果我查询第一页--即在上面语句最后加上where rowId between 21 and 40，正确查询出6条记录，不过时间要23秒左右
 
刚开始我怀疑是SQLServer的ROW_NUMBER()性能存在问题，于是我做了个小实验,查询表中20万以后的20条记录，语句如下：
SELECT ROW_NUMBER() over(order by AuthorizedCode Desc) as rowId,MedicineId,NameCN,AuthorizedCode from T_Medicine 
WHERE rowId between 20000 and 20020 
 
执行后只需要2秒钟左右，也就说明ROW_NUMBER()并没有太大性能问题，所以我怀疑是我上面的语句可能写法上存在不合理，导致性能太慢；
 
只有这么多分数了，请大侠指教！

Answer 1

不是ROW_NUMBER的问题，是两个GROUP BY的问题

Answer 2

看下执行计划，或者IO统计信息，就知道问题在哪里了。不然你也可以用
set statistics profile on 
set statistics io on
然后把结果贴出来看看。这样的分析才会比较有针对性。

Answer 3

SELECT * from 
(
SELECT ROW_NUMBER() over(order by AuthorizedCode Desc) as rowId,MedicineId,NameCN,AuthorizedCode 
from T_Medicine 
WHERE AuthorizedCode IN 
(
SELECT AuthorizedCode,NameCN 
FROM dbo.Medicine 
WHERE  AuthorizedCode!='' 
GROUP BY AuthorizedCode,NameCN
having count(AuthorizedCode)>1
)
) as t
去掉一个嵌套，试试

Answer 4

选中语句按下ctrl+L查看再条语句的执行计划，比较一下就会有所发现的

Answer 5

把ELECT AuthorizedCode FROM(
SELECT AuthorizedCode,NameCN FROM dbo.Medicine WHERE  AuthorizedCode!='' GROUP BY AuthorizedCode,NameCN
) tmp GROUP BY AuthorizedCode HAVING COUNT(*) > 1))这一段放入到一个临时表中最后主表和临时表做个连接。分步来搞

Answer 6

加入后显示的结果：
set statistics profile on  
set statistics io on表 'MedicineCategory'。扫描计数 0，逻辑读取 61 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T_Medicine'。扫描计数 208920，逻辑读取 1998404 次，物理读取 0 次，预读 12 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 22062 毫秒，占用时间 = 22509 毫秒。注意:'T_Medicine'是个视图，关联了一个类型表MedicineCategory；

Answer 7

表 'T_Medicine'。扫描计数 208920，逻辑读取 1998404 次，物理读取 0 次，预读 12 次，lob 逻辑读取 0 
次，lob 物理读取 0 次，lob 预读 0 次。
呵呵，这个就是问题啦。扫描了那么多次。参考5楼的试试看。

Answer 8

谢谢各位，按照5楼的方法已经解决了SELECT * 
FROM   (SELECT Row_number() OVER(ORDER BY a.authorizedcode DESC) AS rowid, 
MedicineId,MillId,NameCN,AliasCN,a.AuthorizedCode,StandardCodeNo,InputAccountName,SerialCode,Standard,TrocheType,IsMedicare,IsPrescription,NamePath
FROM   view_medicine a RIGHT JOIN 
        (SELECT authorizedcode FROM   (SELECT authorizedcode, namecn 
  FROM   dbo.medicine WHERE  active = 1 AND authorizedcode IS NOT  NULL 
  AND authorizedcode != '' GROUP  BY authorizedcode,namecn) tmp 
 GROUP  BY authorizedcode HAVING COUNT(*) > 1) AS b
        ON a.authorizedcode = b.authorizedcode
        WHERE  a.active = 1 
) AS t 
WHERE  rowid BETWEEN 21 AND 40

Answer 9

采用5楼的方法后，IO统计信息：
表 'Medicine'。扫描计数 15，逻辑读取 25805 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'MedicineCategory'。扫描计数 0，逻辑读取 96 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(23 行受影响)

Answer 10

表 'Medicine'。扫描计数 15，逻辑读取 25805 次
逻辑读次数还是这麽高？