求SQL语句优化

Question

select * 
from t_weblog a where bk_abbPaytime between '2008-01-13' and '2008-01-19' 
and bk_payCode not in(select bk_paycode from t_banklog a 
where bk_abbPaytime between '2008-01-13' and '2008-01-19')每一个查询单独查的话速度都在0秒，就是Not In的时候要几分钟。
求一下语句上面的优化。

Answer 1

select   *   
from   t_weblog   a   where   bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19'   
and not exists (select 1 from t_weblog where bk_payCode=a.bk_payCode and bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19')

Answer 2

把 not in 换成  not exists

Answer 3

上面错了
看下边这个select   *   
from   t_weblog   a   where   bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19'   
and not exists (select 1 from t_banklog where bk_paycode=a.bk_payCode and bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19')

Answer 4

select *  
from t_weblog
where bk_abbPaytime between '2008-01-13' and '2008-01-19'
and not exists (select 1 from t_banklog
    where t_weblog.bk_payCode =t_banklog.bk_paycode 
    and bk_abbPaytime between '2008-01-13' and '2008-01-19')

Answer 5

to 飞天小虫，不行，速度还是没有改观。
你看看我这个怎么样：SELECT * FROM t_weblog LEFT JOIN t_banklog ON t_weblog.bk_payCode = t_banklog.bk_payCode
WHERE t_banklog.bk_abbPaytime between '2008-01-13' and '2008-01-19' 
AND t_weblog.bk_abbPaytime between '2008-01-13' and '2008-01-19' AND t_banklog.ciid is nullciid  是自增长字段。

Answer 6

not in 后面的集合是固定，与父查询没有关联，所以比not exists效率高，还是想想别的办法吧

Answer 7

to   飞天小虫，不行，速度还是没有改观。 
你看看我这个怎么样： SELECT   *   FROM   t_weblog   LEFT   JOIN   t_banklog   ON   t_weblog.bk_payCode   =   t_banklog.bk_payCode 
WHERE   t_banklog.bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19'   
AND   t_weblog.bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19'   AND   t_banklog.ciid   is   null ciid     是自增长字段。---------------
你这个查询和原来的那个结果集都不一样，比什么？

Answer 8

其实最好在 两个表的 bk_abbPaytime 上建索引

Answer 9

我5楼写的语句速度很快，0秒左右，现在要验证结果对不对。
not exists和not in一样，速度提不高。

Answer 10

to dobear_0922，请指教，哪里不一样。

Answer 11

to   dobear_0922，请指教，哪里不一样。
-------------
１.列不一样，前者查 t_weblog表的所有列，后者查t_weblog表和t_banklog表的所有列。
２.结果集的行数可能也会不一样。   

Answer 12

列不一样无所谓，加个前缀就行。
原来的写法行数确实会不一样，现在改正了：SELECT w.* FROM t_weblog w LEFT JOIN t_banklog b ON 
w.bk_busiId = b.bk_busiId AND w.bk_payCode = b.bk_payCode AND w.bk_abbPayTime = b.bk_abbPayTime
WHERE w.bk_abbPaytime between '2007-12-25' and '2007-12-25' AND b.ciid is null这个应该没问题了，和同事确认了。

Answer 13

select   *   
from   t_weblog   a   where   bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19'   
and   bk_payCode   not   in(select   bk_paycode   from   t_banklog   a   
where   bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19') 
--
个人认为你的语句没得什么可优化的了.

Answer 14

to birdie_mas
确实多数据了……to dawugui
如果要改善查询效率应该怎么下手呢？

Answer 15

check完了，是有1个错误，不过这个错误是2007年的数据。
修复完了以后13号到19号的查询时间缩短为2分钟了，还是很慢。
非常奇怪， 查1号到19号的时间都比中间一段13号到19号还快。

Answer 16

重建了。
我用执行计划查看了一下，发现查询1号到19号的语句使用上了聚集索引 bk_abbPaytime 加上另外连个字段（主键），
而13号到19号的却使用了另外一个非聚集索引 bk_abbPaytime。不知道为什么会这样。
是修改索引呢，还是指定使用聚集索引呢？

Answer 17

bk_abbPaytime   加上另外两个字段（3字段做联合主键）

Answer 18

两次查询(1～19 & 13～19)的脚本仅是时间参数值的差异?
不可能吧？
不可理解。
不可思议！

Answer 19

检查索引碎片DBCC SHOWCONTIG(表)逻辑扫描碎片和扩展盘区扫描碎片都非常大，需要对索引碎片进行处理
一般有两种方法解决，一是利用DBCC INDEXDEFRAG整理索引碎片，二是利用DBCC DBREINDEX重建索引。二者各有优缺点。调用微软的原话如下：
DBCC INDEXDEFRAG 命令是联机操作，所以索引只有在该命令正在运行时才可用。而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。重新创建聚集索引将对数据进行重新组织，其结果是使数据页填满。填满程度可以使用 FILLFACTOR 选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不会重新创建该索引。也就是说，要想获得好的效果，还是得用重建索引，所以决定重建索引。
DBCC DBREINDEX(表, 索引名, 填充因子）
第一个参数，可以是表名，也可以是表ID。
第二个参数，如果是''，表示影响该表的所有索引。
第三个参数，填充因子，即索引页的数据填充程度。如果是100，表示每一个索引页都全部填满，此时select效率最高，但以后要插入索引时，就得移动后面的所有页，效率很低。如果是0，表示使用先前的填充因子值。DBCC DBREINDEX(A, '', 100)
重新测试查询速度，飞快。

Answer 20

是啊，我看了。我是不能理解为什么MSSQL会根据参数值的不同而选择不同的索引。不知道是它太智能了还是太弱智了。
理论上，查询时选择什么索引查询者不必干涉，系统会根据表达式来选择优化的方案；但按你说的情况看，系统还要看参数是什么来选择索引？太不可思议了!

Answer 21

建议：如果是资料量大，行列都很多,最好不要直接下SELECT语法,存储过程首先要优化一点，再在存储过程中优化.

Answer 22

bk_paycode没有索引或者not in造成没走索引吧。
========================================= 
pb11&SQL   QQ群:   6539042

Answer 23

not in 换为not exists对相关的字段建索引

Answer 24

select   a.*   
from   t_weblog   a ,t_welog as b  
where   a.bk_abbPaytime   between   '2008-01-13'   and   '2008-01-19'   
and   a.bk_payCode=b.bk_paycode and (b.bk_abbpaytime<'2008-01-13' or  b.bk_abbpaytime>'2008-01-19')这样快不? :)