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

  • 问题

  • 原先的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行 为什么会影响这么大呢? 怎么改进???

    2011年8月3日 10:14

答案

全部回复