幫朋友優化個sql

        今天朋友跟我說,有個sql消耗系統資源非常嚴重,看能不能幫忙優化下

sql本身不復雜,只是關聯了四五個表,有個表resultreport 3百多萬條資料庫,其他幾個表才幾千條几萬條記錄

Select A.Lineid,
       A.Linecode,
       A.Linename,
       A.Man,
       A.Time,
       A.Way,
       A.Desc,
       D.Name,
       Count(Distinct B.Hospid) as HospNum,
       C.Lineman,
       C.Carid,
       Sum(decode(E.ReportType, '0', 1, Null)) As SumNomal,
       Sum(decode(E.ReportType, '1', 1, Null)) As SumExecp,
       Sum(decode(E.ReportType, '2', 1, Null)) As SumBacks,
       Sum(decode(E.ReportType, '3', 1, Null)) As SumUnTest
  from LineInfo A
  join LineHosp B
    ON A.Lineid = B.Lineid
  left join Lineclass C
    ON (A.Lineid = C.Lineid and C.Delidate = trunc(sysdate))
  left join dUsers D
    ON C.Lineman = D.Userid
  left join resultreport E
    on ((B.ServCycle like :ServCycle Or Trim(B.ServCycle) = '8') and
       (B.Hospid = E.Hospid and E.State = '1'))
 Group by A.Lineid,
          A.Linecode,
          A.Linename,
          A.Man,
          A.Time,
          A.Way,
          A.Desc,
          D.Name,
          C.Lineman,
          C.Carid
 Order by A.LineCode

cost代價為12萬多

把對resultreport 大表有查詢的條件,改成子查詢,然後再和b表關聯

改寫後的語句如下:

Select A.Lineid,
       A.Linecode,
       A.Linename,
       A.Man,
       A.Time,
       A.Way,
       A.Desc,
       D.Name,
       Count(Distinct B.Hospid) as HospNum,
       C.Lineman,
       C.Carid,
       Sum(decode(E.ReportType, '0', 1, Null)) As SumNomal,
       Sum(decode(E.ReportType, '1', 1, Null)) As SumExecp,
       Sum(decode(E.ReportType, '2', 1, Null)) As SumBacks,
       Sum(decode(E.ReportType, '3', 1, Null)) As SumUnTest
  from LineInfo A,(select Lineid,Hospid from LineHosp
  where ServCycle like :ServCycle Or Trim(ServCycle) = '8' ) B,Lineclass C,
        dUsers D,(select ReportType,Hospid  from resultreport where State = '1') E
  where A.Lineid = B.Lineid
  and A.Lineid = C.Lineid and C.Delidate = trunc(sysdate)
  and C.Lineman = D.Userid
  and B.Hospid = E.Hospid
 Group by A.Lineid,
          A.Linecode,
          A.Linename,
          A.Man,
          A.Time,
          A.Way,
          A.Desc,
          D.Name,
          C.Lineman,
          C.Carid
 Order by A.LineCode;

 

cost代價變為1萬多,提高了10倍多

這個sql優化主要是重寫sql,對大表先查出需要的結果集再和其他小表關聯,這樣就提高查詢速度。

而原來大表和小表直接做關聯，掃描的資料更多，從而影響查詢速度。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7199859/viewspace-671882/，如需轉載，請註明出處，否則將追究法律責任。