使用SQLServer的ROW_NUMBER()实现分页查询性能有关问题
使用SQLServer的ROW_NUMBER()实现分页查询性能问题
我使用SQL语句查询出我想要的数据,使用了SQLServer的ROW_NUMBER()实现分页,但是遇到了性能问题,请大侠指点
数据量说明:
1)查询的T_Medicine表中20万左右的记录;
2)最终查询出符合条件的记录数为仅26条
3)每页显示20条记录
4)表T_Medicine中AuthorizedCode有建立非聚集索引
语句如下(使用了SQL的ROW_NUMBER()来实现分页)
执行我上面的语句的结果:
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条记录,语句如下:
执行后只需要2秒钟左右,也就说明ROW_NUMBER()并没有太大性能问题,所以我怀疑是我上面的语句可能写法上存在不合理,导致性能太慢;
只有这么多分数了,请大侠指教!
------解决方案--------------------
不是ROW_NUMBER的问题,是两个GROUP BY的问题
------解决方案--------------------
看下执行计划,或者IO统计信息,就知道问题在哪里了。不然你也可以用
set statistics profile on
set statistics io on
然后把结果贴出来看看。
这样的分析才会比较有针对性。
------解决方案--------------------
我使用SQL语句查询出我想要的数据,使用了SQLServer的ROW_NUMBER()实现分页,但是遇到了性能问题,请大侠指点
数据量说明:
1)查询的T_Medicine表中20万左右的记录;
2)最终查询出符合条件的记录数为仅26条
3)每页显示20条记录
4)表T_Medicine中AuthorizedCode有建立非聚集索引
语句如下(使用了SQL的ROW_NUMBER()来实现分页)
- SQL code
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条记录,语句如下:
- SQL code
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()并没有太大性能问题,所以我怀疑是我上面的语句可能写法上存在不合理,导致性能太慢;
只有这么多分数了,请大侠指教!
------解决方案--------------------
不是ROW_NUMBER的问题,是两个GROUP BY的问题
------解决方案--------------------
看下执行计划,或者IO统计信息,就知道问题在哪里了。不然你也可以用
set statistics profile on
set statistics io on
然后把结果贴出来看看。
这样的分析才会比较有针对性。
------解决方案--------------------
- SQL code
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
------解决方案--------------------
选中语句按下ctrl+L查看再条语句的执行计划,比较一下就会有所发现的
------解决方案--------------------
把
ELECT AuthorizedCode FROM(
SELECT AuthorizedCode,NameCN FROM dbo.Medicine WHERE AuthorizedCode!='' GROUP BY AuthorizedCode,NameCN
) tmp GROUP BY AuthorizedCode HAVING COUNT(*) > 1))
这一段放入到一个临时表中
最后主表和临时表做个连接。
分步来搞
------解决方案--------------------
- SQL code
表 'T_Medicine'。扫描计数 208920,逻辑读取 1998404 次,物理读取 0 次,预读 12 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。