有谁能帮忙由优化这个sql，多谢啦。

Question

有谁能帮忙由优化这个sql，多谢啦。
小弟写的这个sql 性能不高，查询耗费很多时间。
  各位大虾提提优化意见，改善sql的性能。
select a.forgunitid as orgId,
       j.fbudgetitem,
       c.fnumber as acctCode,
       i.fperiodnumber,
       i.fperiodyear,
       j.fnewaccountcode,
       j.fnewaccountname,
       j.fistotal,
       j.fisminus,
       sum(case
             when c.fdc = -1 then
              a.fcreditfor
             else
              a.fdebitfor
           end) realValue,
       sum(case
             when c.fdc = 1 then
              a.fcreditfor
             else
              a.fdebitfor
           end) otherValue
  from t_gl_assistbalance    a,
       t_bd_assistanthg      b,
       t_bd_accountview      c,
       t_gov_bibudgetaccount j,
       t_bd_period           i,
       t_Bd_Asstacttype      k
where a.fassistgrpid = b.fid
   and a.faccountid = c.fid
   and a.forgunitid = c.fcompanyid
   and c.fnumber = j.faccount
   and a.fperiodid = i.fid
   and i.fperiodyear = 2009
   and a.fbaltype = 5
   and a.fcurrencyid = 'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC'
   and a.forgunitid in (select distinct t2.fadminorgid
                          from t_gov_bibudgetunitclassentry t2,
                               t_org_baseunit               t3,
                               t_gov_bibudgetunitclassentry t1
                         where t1.fparentid = t2.fparentid
                           and t1.fuserid = 'KbKDSQEXEADgBp5SCiEBORO33n8='
                           and t1.fclasstype = 'B'
                           and t2.fclasstype = 'A'
                           and t2.fadminorgid = t3.fid
                           and t3.fiscompanyorgunit = 1)
   and j.FASSTACTTYPE = k.fid
-- -begin   
--加上以下的条件执行耗费了很长时间。
   and (j.FGLASSTTCTTYPEID = b.fgeneralassacttype1id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype2id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype3id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype4id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype5id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype6id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype7id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype8id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype9id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype10id or
       j.FGLASSTTCTTYPEID = b.fgeneralassacttype11id)-----end
group by a.forgunitid,
          j.fbudgetitem,
          c.fnumber,
          i.fperiodnumber,
          i.fperiodyear,
          j.fnewaccountcode,
          j.fnewaccountname,
          j.fistotal,
          j.fisminus

Answer 1

不妨尝试一下：
select a.forgunitid as orgId,
      j.fbudgetitem,
      c.fnumber as acctCode,
      i.fperiodnumber,
      i.fperiodyear,
      j.fnewaccountcode,
      j.fnewaccountname,
      j.fistotal,
      j.fisminus,
      sum(case
            when c.fdc = -1 then
              a.fcreditfor
            else
              a.fdebitfor
          end) realValue,
      sum(case
            when c.fdc = 1 then
              a.fcreditfor
            else
              a.fdebitfor
          end) otherValue
  from t_gl_assistbalance    a,
      
      t_bd_accountview      c,
      t_gov_bibudgetaccount j,
      t_bd_period          i,
      t_Bd_Asstacttype      k
where a.fassistgrpid = b.fid
  and a.faccountid = c.fid
  and a.forgunitid = c.fcompanyid
  and c.fnumber = j.faccount
  and a.fperiodid = i.fid
  and i.fperiodyear = 2009
  and a.fbaltype = 5
  and a.fcurrencyid = 'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC'
  and a.forgunitid in (select distinct t2.fadminorgid
                          from t_gov_bibudgetunitclassentry t2,
                              t_org_baseunit              t3,
                              t_gov_bibudgetunitclassentry t1
                        where t1.fparentid = t2.fparentid
                          and t1.fuserid = 'KbKDSQEXEADgBp5SCiEBORO33n8='
                          and t1.fclasstype = 'B'
                          and t2.fclasstype = 'A'
                          and t2.fadminorgid = t3.fid
                          and t3.fiscompanyorgunit = 1)
  and j.FASSTACTTYPE = k.fid
  and exists(select 1 from (select  fgeneralassacttype1id from  t_bd_assistanthg    
       union all select  fgeneralassacttype2id from  t_bd_assistanthg
       union all select  fgeneralassacttype3id from  t_bd_assistanthg
 union all select  fgeneralassacttype4id from  t_bd_assistanthg
 union all select  fgeneralassacttype5id from  t_bd_assistanthg
 union all select  fgeneralassacttype6id from  t_bd_assistanthg
 union all select  fgeneralassacttype7id from  t_bd_assistanthg
 union all select  fgeneralassacttype8id from  t_bd_assistanthg
union all select  fgeneralassacttype9id from  t_bd_assistanthg
union all select  fgeneralassacttype10id from  t_bd_assistanthg
union all select  fgeneralassacttype11id from  t_bd_assistanthg) b 
where 
      j.FGLASSTTCTTYPEID = b.fgeneralassacttype1id )group by a.forgunitid,
          j.fbudgetitem,
          c.fnumber,
          i.fperiodnumber,
          i.fperiodyear,
          j.fnewaccountcode,
          j.fnewaccountname,
          j.fistotal,
          j.fisminus 

Answer 2

如果性能还不行，建议写一个存储过程，将select  fgeneralassacttype1id from  t_bd_assistanthg   
      union all select  fgeneralassacttype2id from  t_bd_assistanthg
      union all select  fgeneralassacttype3id from  t_bd_assistanthg
union all select  fgeneralassacttype4id from  t_bd_assistanthg
union all select  fgeneralassacttype5id from  t_bd_assistanthg
union all select  fgeneralassacttype6id from  t_bd_assistanthg
union all select  fgeneralassacttype7id from  t_bd_assistanthg
union all select  fgeneralassacttype8id from  t_bd_assistanthg
union all select  fgeneralassacttype9id from  t_bd_assistanthg
union all select  fgeneralassacttype10id from  t_bd_assistanthg
union all select  fgeneralassacttype11id from  t_bd_assistanthg
写入临时表，并对此字段建立索引，然后在连接到此表，性能会提高很多

Answer 3

谢谢，zhangwonderful.
  存储过程就不能用了。
  第一sql 要执行下。看看效果。