等待“hyqok”的关于SQL分页代码效率低的回复

Question

感谢“hyqok”对本帖http://topic.csdn.net/u/20091022/08/39ce3a13-2b36-4637-ba6a-6a34a4478cac.html?1693427579的回答，等待您的最佳答案，并开本帖另外再送分。
另外请其它高手一起参与这个帖子。期待SQL高手的回答。

Answer 1

加上这个索引试试：create index ix_01 on view_product(pagerRank0,isPassAudit)

Answer 2

我发现主要的问题是ROW_NUMBER()这个函数用来计算行标号进行分页。而这个函数需要对整个表进行扫描。这是引起性能低下的主要原因。因此解决方案我想有两种：
一种是取消这个函数，另行找分页算法。
还有一种是把整个view_product直接保存和驻留到内存中，这样就无需进行大量的IO磁盘读取了。就可以大大加快速度

Answer 3

你的判断是错误的，目前ROW_NUMBER()函数是分页的最 佳选择。只是你建立的索引没有起作用。

Answer 4

目前的视图 表索引没有起作用，尤其用到了范围查询。调整如下：
第一步：tb_Product 表是主表  把主键聚集索引取消，保存表结构更改。
第二步：tb_Product 表 上的releasetimeandpagerrank0索引 设置成聚集索引，保存表结构更改。
第三步：现在关不缓存测试 现有查询11秒。
第四步：不用视图直接写查询：
--清空数据缓存和执行计划缓存 
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE  --查看IO和Time 
set statistics io on 
set statistics time onSELECT b.rownum, a.* 
   FROM tb_Product a,
(SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, 
     pagerrank0,releaseTime,productid
      FROM tb_Product  WHERE isPassAudit='true' and pagerRank0>0 ) AS b
    WHERE b.pagerrank0 =a.pagerrank0 and b.releaseTime =a.releaseTime and b.productid=a.productid 
AND b.rownum BETWEEN 159985 AND 160000 
ORDER BY b.rownum
执行时间不到1秒
第五步：主表查询出来后再去连接附表，这步省略。

Answer 5

这样加索引试试：
create index ix_02 on view_product(isPassAudit,pagerRank0,releaseTime)加了上面的索引，试试这面的sql速度：SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, isPassAudit
FROM view_product WHERE isPassAudit='true' and pagerRank0>0 ) AS D 
WHERE rownum BETWEEN 159985 AND 160000 ORDER BY pagerrank0 DESC , releaseTime desc

Answer 6

ROW_NUMBER() 的效率不高是因为要进行范围查询 ，聚集索引全覆盖是必须的。如果还有疑问，请继续与我联系。

Answer 7

SELECT b.rownum, a.*
  FROM tb_Product a,
(SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum,
   *
      FROM tb_Product  WHERE isPassAudit='true' and pagerRank0>0 ) AS b
    WHERE b.pagerrank0 =a.pagerrank0 and b.releaseTime =a.releaseTime and b.productid=a.productid
AND b.rownum BETWEEN 159985 AND 160000
ORDER BY b.rownum 改成*,这样速度如何,io如何？

Answer 8

(16 行受影响)
表 'tb_Product'。扫描计数 1，逻辑读取 20544 次，物理读取 12 次，预读 22032 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

Answer 9

这是优化前的IO 
(16 行受影响) 
表 'View_Product'。扫描计数 1，逻辑读取 490452 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 

Answer 10

我懂你的意思了。就是不要在视图VIEW_PRODUCT上查询而是在tb_product上查询。我回去实验下，谢谢

Answer 11

to hyqok:
SELECT b.rownum, a.*  FROM tb_Product a, (SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, *  FROM tb_Product WHERE isPassAudit='true' and pagerRank0>0 ) AS b WHERE b.pagerrank0 =a.pagerrank0 and b.releaseTime =a.releaseTime and b.productid=a.productid AND b.rownum BETWEEN 10 AND 20 ORDER BY b.rownum这个读page如何？

Answer 12

 CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。(11 行受影响)
表 'tb_Product'。扫描计数 12，逻辑读取 45 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:
   CPU 时间 = 16 毫秒，占用时间 = 2 毫秒。

Answer 13

-- BETWEEN 159985 AND 160000SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 34 毫秒。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。SQL Server 执行时间:
   CPU 时间 = 31 毫秒，占用时间 = 27 毫秒。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。(16 行受影响)
表 'tb_Product'。扫描计数 17，逻辑读取 20345 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:
   CPU 时间 = 156 毫秒，占用时间 = 180 毫秒。

Answer 14

呵呵 改成*速度肯定是下降啊 ，row_number()的算法你怎么理解的。我没明天楼上怎么理解的。

Answer 15

利用您的代码。
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE   --查看IO和Time 
set statistics io on 
set statistics time on SELECT b.rownum, a.* 
   FROM tb_Product a, 
(SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, 
     pagerrank0,releaseTime,productid 
      FROM tb_Product  WHERE isPassAudit='true' and pagerRank0>0 ) AS b 
    WHERE b.pagerrank0 =a.pagerrank0 and b.releaseTime =a.releaseTime and b.productid=a.productid 
AND b.rownum BETWEEN 159985 AND 160000 
ORDER BY b.rownum 
最后的运行结果如下：
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 31 毫秒，占用时间 = 56 毫秒。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。SQL Server 执行时间:
   CPU 时间 = 16 毫秒，占用时间 = 13 毫秒。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。(16 行受影响)
表 'tb_Product'。扫描计数 17，逻辑读取 21768 次，物理读取 1 次，预读 22464 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(1 行受影响)SQL Server 执行时间:
   CPU 时间 = 281 毫秒，占用时间 = 9179 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
也就是总共用时：９秒
不知道你的测试环境的配置是什么，我的是双核２．２，３G内存的笔记本上测试的。而你的测试是1秒以内，怎么会有如此大的差异的？

Answer 16

tb_Product 表 上的releasetimeandpagerrank0索引 设置成聚集索引，在原有两个字段基础上增加productid字段 。

Answer 17

 上午着急漏了这点。我的笔记本上午开了一直没有关，现在时7s 。上午测试的IO不是有吗。你等你的本冷却一下试试 。

Answer 18

那么我还有一个问题：为何我在VIEW_PRODUCT视图上建立相同的三个列的聚集索引，然后对VIEW_Product进行查询，速度却慢很多呢

Answer 19

这是你把索引结构修改成原来的 估计就不是1分21秒 了 可能是 5分钟以上。
和你机器的内存，cpu性能和使用情况（当前温度） 有关。

Answer 20

看几S看不出什么来，因为缓存数据的原因。
对于简单的t-sql写法，看io更有意义。继续关注。

Answer 21

楼上能说的具体点吗，我一直使用下面的语句试图清空缓存，使用发现还是很有效果的。
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE --查看IO和Time 
set statistics io on 
set statistics time on 

Answer 22

这个很容易证明：
比如你现在第一次执行：
select * from tbname
看一下io的读写，预读几乎等于逻辑读，这说明数据是从io上读取的.现在你
 DBCC DROPCLEANBUFFERS
 DBCC FREEPROCCACHE
之后再select * from tbname
那么再看一下io的读写,预读应该远小于逻辑读，甚至为0，这说明
 DBCC DROPCLEANBUFFERS
 DBCC FREEPROCCACHE
无法清除已缓存在内存中的数据。
DBCC DROPCLEANBUFFERS :
使用 DBCC DROPCLEANBUFFERS 测试包含冷缓存的查询，而不用关闭和重新启动服务器。
若要从缓冲池中删除清除缓冲区，请首先使用 CHECKPOINT 生成一个冷缓存。这可以强制将当前数据库的全部脏页写入磁盘，然后清除缓冲区。完成此操作后，便可发出 DBCC DROPCLEANBUFFERS 命令来从缓冲池中删除所有缓冲区。DBCC FREEPROCCACHE
使用 DBCC FREEPROCCACHE 清除过程缓存。释放过程缓存将导致系统重新编译某些语句（例如，即席 SQL 语句），而不重用缓存中的语句。这会导致查询性能暂时性地突然降低。之前在做调优时我也经常会使用这两个选项，事实上效果不明显。

Answer 23

首先使用 CHECKPOINT 生成一个冷缓存 这里能具体点吗 ？CHECKPOINT 的用法。举个例子。

Answer 24

CHECKPOINT 用在这里不合适吧 有谁做调试每次CHECKPOINT ：）

Answer 25

to hyqok:你看这样优化试试呢？我这没数据假设productid 为主键且为聚集索引
再建一个非聚集索引
create index ix_01 on tb_Product(isPassAudit desc,pagerRank0 desc,releaseTime desc)那么先看一下，下面这个查询的io是多少？
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum,
pagerrank0,releaseTime,productid FROM tb_Product WITH(index=ix_01)
WHERE isPassAudit='true' and pagerRank0>0
) AS B WHERE b.rownum BETWEEN 159985 AND 160000待测试结果。

Answer 26

16 行受影响) 
表 'View_Product'。扫描计数 1，逻辑读取 490452 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 

Answer 27

从理论角度上来讲：
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum,
pagerrank0,releaseTime,productid FROM tb_Product WITH(index=ix_01)
WHERE isPassAudit='true' and pagerRank0>0
) AS B WHERE b.rownum BETWEEN 159985 AND 160000 
在建索引：
create index ix_01 on tb_Product(isPassAudit desc,pagerRank0 desc,releaseTime desc) 
后，只读这个非聚集索引的page.
而且这个page是按照查询条件排过序的，理论上row_number()应该是很快的。手头上没有数据，以上只是推断。如果这样设置为了还是几W个page,我无语。

Answer 28

我刚才试验了一下你的方法多建立了一个索引，发现对磁盘的逻辑读的次数确实又大大减少了。速度得到了提高。谢谢大家的关心和支持！现在散分！对于perfectaction 我另开一帖给分。以表我的谢意！