sql server2005数据查询范围越小时间越慢，求解析

[bug343]

select * from view_t_bctz as c
where not exists
(select a.cjbh_xz from view_t_bctz as a inner join view_t_bctz as b
on a.cjbh_xz=b.cCancelSign and a.fbcje=-b.fbcje
where a.cazzt=5 and a.djbsj between '2011-06-01 00:00:00' and '2011-06-14 23:59:59' and a.cxzbm in ('1','2')
and b.cazzt=5 and b.djbsj between '2011-06-01 00:00:00' and '2011-06-14 23:59:59' and b.cxzbm in ('1','2')
and a.cjbh_xz=c.cjbh_xz )
and fbcje>0 and cazzt=5 and cxzbm in ('1','2')
and djbsj between '2011-06-01 00:00:00' and '2011-06-14 23:59:59'

cazzt，cjbh_xz，cxzbm ，djbsj 为索引
查询时间用了6S

如果把时间换成'2011-06-13 00:00:00' and '2011-06-14 23:59:59'
查询时间用了3min

求解析

[GoAwayZ]

用not exists似乎用不到索引。

[叶子]

需要看执行计划来分析。
貌似6s时间也挺长了，不知道一共是多少数据量？

[rucypli]

看6s用的什么索引 强制3m的查询也有6s的那个索引

[--小F--]

通过查看执行计划和性能分析器跟踪来看

[sunylf]

语句需优化。

[tcjacky]

你这个查询写的很有问题 同一张表自己联自己3次！
with atable as
(select * from view_t_bctz
where 1=1
and fbcje>0 and cazzt=5 and cxzbm in ('1','2')
and djbsj between '2011-06-01 00:00:00' and '2011-06-14 23:59:59')
select * from atable as a
where not exists(select 1 from atable as b where a.cjbh_xz=b.cCancelSign and a.fbcje=-b.fbcje)
你用这个语句应该可以解决你的问题

[bug343]

执行计划短时间范围全部用到索引查找，不知何故~