首页 新闻 搜索 专区 学院

为何我的查询条件增加一个后性能消耗这么多

0
悬赏园豆:20 [待解决问题]

原先的sql语句:

select ac.*,bp.portname,bs.shipname,cb.ItinDur,cb.SDFare,cb.BDFare,cb.ODFare,cb.IDFare from( select shipcode,linecode,ItinDur,min(saildate)as saildate,min(cruisecode)as cruisecode,min(SDFare + GovtFees)as SDFare, min(BDFare + GovtFees)as BDFare,min(ODFare + GovtFees)as ODFare,min(IDFare + GovtFees)as IDFare from cru_bestfare group by shipcode,linecode,ItinDur ) cb inner join cru_cruises as ac on cb.shipcode = ac.shipcode and cb.linecode = ac.linecode and cb.cruisecode = ac.CruisesCode and cb.saildate = ac.DepartDate inner join base_port as bp on ac.fromport = bp.portcode inner join base_ship bs on ac.shipCode = bs.shipCode and ac.Source = bs.Source and ac.linecode = bs.linecode where ac.active = 1 and ac.modify = 1 and (cb.SDFare > 0 or cb.BDFare > 0 or cb.ODFare > 0 or cb.IDFare > 0)

这个sql语句的执行时间为 <0 秒

增加一个查询条件:

select ac.*,bp.portname,bs.shipname,cb.ItinDur,cb.SDFare,cb.BDFare,cb.ODFare,cb.IDFare from( select shipcode,linecode,ItinDur,min(saildate)as saildate,min(cruisecode)as cruisecode,min(SDFare + GovtFees)as SDFare, min(BDFare + GovtFees)as BDFare,min(ODFare + GovtFees)as ODFare,min(IDFare + GovtFees)as IDFare from cru_bestfare group by shipcode,linecode,ItinDur ) cb inner join cru_cruises as ac on cb.shipcode = ac.shipcode and cb.linecode = ac.linecode and cb.cruisecode = ac.CruisesCode and cb.saildate = ac.DepartDate inner join base_port as bp on ac.fromport = bp.portcode inner join base_ship bs on ac.shipCode = bs.shipCode and ac.Source = bs.Source and ac.linecode = bs.linecode where ac.active = 1 and ac.modify = 1 and ac.DurationDays < 8 and (cb.SDFare > 0 or cb.BDFare > 0 or cb.ODFare > 0 or cb.IDFare > 0)

增加的条件为: and ac.DurationDays < 8 其中 DurationDays 是int类型

然后执行时间变为13秒

上面每个表的数据都不到10000行 为什么会影响这么大呢? 怎么改进???

wesleyliu的主页 wesleyliu | 初学一级 | 园豆:110
提问于:2011-08-03 18:22
< >
分享
所有回答(2)
0

select ac.*,bp.portname,bs.shipname,cb.ItinDur,cb.SDFare,cb.BDFare,cb.ODFare,cb.IDFare from
( select shipcode,linecode,ItinDur,min(saildate)as saildate,min(cruisecode)as cruisecode,min(SDFare + GovtFees)as SDFare,
min(BDFare + GovtFees)as BDFare,min(ODFare + GovtFees)as ODFare,min(IDFare + GovtFees)as IDFare from
 cru_bestfare group by shipcode,linecode,ItinDur )cb 
 
  inner join cru_cruises as ac on cb.shipcode = ac.shipcode and cb.linecode = ac.linecode and cb.cruisecode = ac.CruisesCode and cb.saildate = ac.DepartDate and ac.active = 1 and ac.modify = 1 and ac.DurationDays < 8
   inner join base_port as bp on ac.fromport = bp.portcode
   inner join base_ship bs    on ac.shipCode = bs.shipCode and ac.Source = bs.Source and ac.linecode = bs.linecode   
  
   where   (cb.SDFare > 0 or cb.BDFare > 0 or cb.ODFare > 0 or cb.IDFare > 0)

然后你ac.*   改成 ac.字段名

杯具程序员 | 园豆:1718 (小虾三级) | 2011-08-04 09:05
0

建议去看执行计划,上面会有详细的原因。

快乐的Developer | 园豆:210 (菜鸟二级) | 2011-08-06 23:49
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册