一个复杂的SQL语句优化的疑惑

背景:两个表workacacdata.movementdata.

movementdata中为日期提供过滤,及db_id提供过滤(此表中db_id是主键,唯一)

workacacdata中存在三种数据(此表中db_id不唯一,引用自上表)

A类型

    recordkind的顺序为

   A101 ->其他->A110   或者    A101->其他(不包括A110)

B类型   A110->A101->其他->A110

C类型   A110->A101->其他  

 

数据的db_id相同代表数据属于同一组,每一组数据只属于一种类型A或B或C

 

相同db_id的一组数据 用record_id来表示它插入的顺序

 

所以

对A类数据来说

 

record_kind=A101的数据的record_id肯定小于和它同组的record_kind=A110的数据的record_id

 

以此类推

B类型数据:必存在两个record_kind=A110 的和一个record_kind=A101的数据,且 record_kind=A101的数据的record_id在三者record_id中排序居中

 

C........

 

要求:

取出所有A类型数据的开始日期

delivery_start_date

 

 看起来超复杂,感觉做了很多次的重复查询.

于是修改之:

 

 结果却不尽如人意,修改后的语句怎么看执行的查询数也不多啊,可用SQLServer自带的性能分析工具一测,优化后的反而更慢.

尤其在日期条件发生变化后.

 

 

修改后的语句read指数明显减少,但cpu指数明显增加

 

难道是我测试的表不够大???

workacacdata表1400条

movementdata表80条

 

 

原sql语句:

select distinct( convert(char(10),delivery_start_date,112)  ) as selectdate 
from movementdata  

where (

delivery_start_date >= '2008-04-24 00:00:00' and 

delivery_start_date < '2008-07-26 00:00:00') and  
(
(
--选出A和B类型
		(db_id in  
			  (select db_id from  

				    (
				    select 
				    a.db_id, 
				    count(a.record_kind) as cnt 
				    from workacacdata a, workacacdata b,movementdata c 
				    where 
				    (upper(a.record_kind)='A110') and 
				    (upper(b.record_kind)='A201') and 
				    a.db_id=c.db_id and 
				    c.delivery_start_date >= '2008-04-24 00:00:00' and 
				    c.delivery_start_date < '2008-07-26 00:00:00' and 
				    a.db_id=b.db_id 
				    group by a.db_id
				    ) 

			  deliverytbl 
			  where (cnt=1)
			  )
		) and  

--选出A存在A110数据
		(db_id in  
				    (
				    select a.db_id as db_id 
				    from workacacdata a, workacacdata b,movementdata c 
				    where 
				    upper(a.record_kind)='A101' and 
				    upper(b.record_kind) = 'A110' and 
				    a.db_id=c.db_id and 
				    c.delivery_start_date >= '2008-04-24 00:00:00'and 
				    c.delivery_start_date < '2008-07-26 00:00:00' and 
				    a.db_id = b.db_id and 
				    a.record_id<b.record_id
				    )
		)
) 
--选出不存在A110的A类型数据
or  	(    
        db_id in  
        	 		(
        	 		select distinct(db_id) 
        	 		from movementdata  
        	 		where 
        	 		delivery_start_date >= '2008-04-24 00:00:00' and 
        	 		delivery_start_date < '2008-07-26 00:00:00' and 
        	 		db_id not in 
       	 					(
      	 					select distinct(a.db_id) 
     	 					from workacacdata a,movementdata b  
        	 					where 
       	 					a.db_id=b.db_id and 
      	 					b.delivery_start_date >= '2008-04-24 00:00:00' and 
     	 					b.delivery_start_date < '2008-07-26 00:00:00'  and 
        	 					upper(a.record_kind)='A110'
        	 					)
    				)

       )
) 
order by selectdate 

 

 

 

 

修改后的语句:

--选出所有不同的db_id,包括A.B.C
select distinct( convert(char(10),delivery_start_date,112)  )
	from workacacdata a,movementdata b 
	where
	b.delivery_start_date >= '"+start_time+"' and 
	b.delivery_start_date < '"+end_time+"'  and 
	upper(a.record_kind)='A101' and
	a.db_id=b.db_id and
--排除B和C类型的	
a.db_id not in(
--选出B和C类型的
	select a.db_id as db_id
	from workacacdata a,movementdata b ,workacacdata c
	where
	b.delivery_start_date >= '"+start_time+"' and 
	b.delivery_start_date < '"+end_time+"'  and
	upper(a.record_kind)='A101' and
	a.db_id=b.db_id and
	upper(c.record_kind) = 'A110' and
	c.db_id=a.db_id and
	c.record_id<a.record_id 
)