早上檢查報警郵件時發現又是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
新聞熱點
疑難解答