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

chen_hh2 2010-07-12 09:28:19
我使用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()并没有太大性能问题,所以我怀疑是我上面的语句可能写法上存在不合理,导致性能太慢;

只有这么多分数了,请大侠指教!
...全文
2113 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
lyhabc桦仔 2012-06-29
  • 打赏
  • 举报
回复
表 'Medicine'。扫描计数 15,逻辑读取 25805 次
逻辑读次数还是这麽高?
chen_hh2 2010-07-13
  • 打赏
  • 举报
回复
采用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 行受影响)
chen_hh2 2010-07-13
  • 打赏
  • 举报
回复
谢谢各位,按照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
黄_瓜 2010-07-12
  • 打赏
  • 举报
回复



ELECT AuthorizedCode FROM(
SELECT AuthorizedCode,NameCN FROM dbo.Medicine WHERE AuthorizedCode!='' GROUP BY AuthorizedCode,NameCN
) tmp GROUP BY AuthorizedCode HAVING COUNT(*) > 1))

这一段放入到一个临时表中

最后主表和临时表做个连接。

分步来搞
情殇无限 2010-07-12
  • 打赏
  • 举报
回复
选中语句按下ctrl+L查看再条语句的执行计划,比较一下就会有所发现的
永生天地 2010-07-12
  • 打赏
  • 举报
回复
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

去掉一个嵌套,试试
obuntu 2010-07-12
  • 打赏
  • 举报
回复
看下执行计划,或者IO统计信息,就知道问题在哪里了。不然你也可以用
set statistics profile on
set statistics io on
然后把结果贴出来看看。

这样的分析才会比较有针对性。
guguda2008 2010-07-12
  • 打赏
  • 举报
回复
不是ROW_NUMBER的问题,是两个GROUP BY的问题
obuntu 2010-07-12
  • 打赏
  • 举报
回复

表 'T_Medicine'。扫描计数 208920,逻辑读取 1998404 次,物理读取 0 次,预读 12 次,lob 逻辑读取 0
次,lob 物理读取 0 次,lob 预读 0 次。


呵呵,这个就是问题啦。扫描了那么多次。

参考5楼的试试看。
chen_hh2 2010-07-12
  • 打赏
  • 举报
回复
加入后显示的结果:
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;

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧