日期:2014-05-18  浏览次数:20565 次

SQL调优问题!
问题描述:
1. 涉及表
请购单身表:   PURTB
请购单头表:   PURTA
采购单头表:   PURTC
采购单身表:   PURTD
      以上为ERP系统自带表,以下为自己开发的排程系统涉及表
      定单排程主表:   KSPCSA
      采购跟踪表:   KSPCSE
      交期回复表:   KSPCSF

      PURTD   记录40W以上,   PURTB   记录50W以上
2. 现状
入系统下单(维护请购单并生成采购单)采购人员> 25,   忙的时候下单及录入进货单都很缓慢.
3. 问题:
SQL1:
select   DISTINCT
          cast(I.TA003   as   datetime)   AS   RQ1,
          cast(F.TC024   as   datetime)   AS   RQ2,
          C.TD021   AS   F01,
          J.SA08   AS   F02,
          C.TD002   AS   F03,
          C.TD004   AS   F04,
          C.TD005   AS   F05,
          C.TD006   AS   F06,
          C.TD009   AS   F07,
          C.TD008   AS   F08,
          (C.TD015-C.UDF51)   AS   F09,
          case   when   J.SA27=0   and   (D.SE16   is   null   or   D.SE16= ' ')   and   (E.SF08   is   null)   then   '未排 '   else
          (case   when   (D.SE16   is   null   or   D.SE16= ' ')   and   (E.SF08   is   null)   then   '未复 '
                    when   E.SF08   is   null   then
                    case   when   D.SE16= '待复 '   then   '待复 '
                              when   D.SE16= '待交 '   then   '待交 '
                    end
          else   rtrim(cast(DATEPART   (year,E.SF08)   as   char))+ '- '
                    +rtrim(cast(DATEPART   (month,E.SF08)   as   char))+ '- '
                    +rtrim(cast(DATEPART   (DAY,E.SF08)   as   char))   end)     end   AS   F10,
          isnull(E.SF09,(C.TD008-C.TD015-C.UDF51))   AS   F11,
          isnull(H.MV002, '无 ')   AS   F12,
          F13=case   when   E.SF08   is   null   then   C.TD008   else  
                  (select   sum(SF09)   from   KSPCSF   where   SF02=E.SF02   and   SF03=E.SF03   and     SF04=E.SF04   and   SF08 <=E.SF08)   end,
          G.MA002   AS   F14,C.TD008-C.TD015+C.UDF51   as   F15,C.TD010   AS   F16,rtrim(D.SE18)+rtrim(isnull(E.SF10, ' '))   AS   F17,B.TB031   AS   F18,
    &