早上檢查報警郵件時發現又是1000+的報警,于是查找兇手...最終找到了罪魁禍首,一個ETL查詢從晚上10點開始跑到凌晨1點50,好家伙足足跑了3小時50分鐘,阻塞了一片一片的JOB:

SELECT  ****FROM  AINNER JOIN (  SELECT XXX,A.END_DATE_REP,MAX(PUBLISH_DATE) AS PUBLISH_DATE  FROM A  INNER JOIN    (    SELECT XXX,MAX(END_DATE_REP) AS END_DATE_REP    FROM A    GROUP BY XXX   )B  ON A.XXX = B.XXX  AND A.END_DATE_REP=B.END_DATE_REP  GROUP BY A.XXX,A.END_DATE_REP ) CON A.XXX = C.XXXAND A.END_DATE_REP=C.END_DATE_REPAND A.PUBLISH_DATE=C.PUBLISH_DATELEFT JOIN (SELECT A.*     FROM B    JOIN   (   SELECT XXX,FISCAL_PERIOD,MAX(PUBLISH_DATE) AS  PUBLISH_DATE   FROM  B   GROUP BY XXX,FISCAL_PERIOD  ) D    ON B.XXX = D.XXX    AND B.PUBLISH_DATE = D.PUBLISH_DATE    ) FON A.XXX = F.XXXAND A.END_DATE_REP = F.END_DATE_REP

一看到這個查詢，瞬間被石化了。典型的使用開窗函數的場景嘛。嘗試使用如下的開窗函數寫法后，妥妥的10s內解決戰斗。

SELECT  ****FROM(SELECT XXX,RANK() OVER (PARTITION BY XXX ORDER BY END_DATE_REP DESC,PUBLISH_DATE DESC) RAKFROM A WITH(NOLOCK)) CLEFT JOIN(SELECT  XXX,END_DATE_REP,RANK() OVER (PARTITION BY XXX,END_DATE_REP ORDER BY PUBLISH_DATE DESC) RAKFROM  B WITH(NOLOCK)) FON C.XXX=F.XXXAND C.END_DATE_REP = F.END_DATE_REPAND F.RAK = 1 WHERE C.RAK = 1