DB2 CASE 語句在整個 WHEN 周圍使用括號更快

試圖優化慢查詢，我團隊中的某個人遇到了這個問題。在我們的儲存過程中有這個：

CASE 
 WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM 
   AND RECTYPE = 'RO' 
   THEN 1 
   ELSE 0 
END AS MYRESULT

執行速度比這個慢約 10 秒：

CASE 
 WHEN (MYCOL BETWEEN @STARTNUM AND @ENDNUM 
   AND RECTYPE = 'RO') 
   THEN 1 
   ELSE 0 
END AS MYRESULT

唯一的區別是整個 WHEN 子句周圍的括號。我們不知道發生了什麼。這與 iSeries 上的 DB2 一起使用。

什麼可能導致這種性能差異，我們如何利用它來加快速度？作為程序員，那些額外的括號毫無意義。作為 DbA，優化器在這裡做什麼才能對性能產生如此巨大的影響？

---

**編輯：**這是查詢；這很糟糕，但這是我們必須處理的數據。我只執行了一個子選擇（在聯合之前）並且仍然有顯著的性能差異，因此聯合和最終選擇不會造成大部分性能損失。

我意識到可以修復一些日期操作以刪除函式呼叫；最終會有人做到這一點。

SELECT
ID ,
ABBREVIATION ,
METRICS.NAME ,
METRICS.TYPE AS TYPE_ID ,
JOBTYPE.NAME AS TYPE_NAME ,
SUM ( CREATEDORDUE ) AS CREATEDORDUE ,
SUM ( COMPLETED ) AS COMPLETED ,
SUM ( COMPLETEDINSIDE ) AS COMPLETEDINSIDE ,
SUM ( CASE WHEN DAYS <= 7 THEN 1 ELSE 0 END ) AS ONTIME ,
SUM ( CASE WHEN DAYS > 7 AND DAYS <= 14 THEN 1 ELSE 0 END ) AS ONEWEEKLATE ,
SUM ( CASE WHEN DAYS > 14 AND DAYS <= 21 THEN 1 ELSE 0 END ) AS TWOWEEKSLATE ,
SUM ( CASE WHEN DAYS > 21 THEN 1 ELSE 0 END ) AS MORETHANTWOWEEKSLATE

FROM (

-- Active File -- 

SELECT EQMDOM AS ID ,
CT1TID AS ABBREVIATION ,
CT1NAM AS NAME ,
JBCTL ,
JBWKA AS TYPE ,
CASE 
   WHEN JBCDT BETWEEN @STARTDATENUM AND @ENDDATENUM THEN 1 
   ELSE 0 
END AS COMPLETED ,
1 AS CREATEDORDUE ,
-- Stackexchange question
CASE 
   WHEN ( JBCDT BETWEEN @STARTDATENUM AND @ENDDATENUM AND JBDTC = 'RO' ) THEN 1
   ELSE 0 
END AS COMPLETEDINSIDE ,
-- /Stackexchange question
MASTER.USDATE_DATEDIFFERENCE ( 
   CASE WHEN JBCDT = 0 THEN @ENDDATENUM WHEN JBCDT > @ENDDATENUM THEN @ENDDATENUM ELSE JBCDT END , 
   CAST ( MASTER.DATETOCYMDDATE ( DATE ( JBCRT ) ) AS DEC ( 7 , 0 ) ) , 
   CAST ( 'DAYS  ' AS CHAR ( 6 ) ) 
   ) AS DAYS

FROM EMJOB
JOIN EMEQM ON JBUNT = EQMNUM
JOIN TABLE ( MASTER.CSVTABLE ( @LOCATIONS ) ) AS LOCATIONS ON VAL = EQMDOM --CSVTABLE function separates a csv list into a usable table
JOIN AAP030 ON CT1NUM = EQMDOM

WHERE JBSYS = 'PM'
AND ( JBCDT >= MASTER.DATETOCYMDDATE ( DATE ( @STARTDATE ) ) OR JBCDT = 0 )
AND JBCRT <= @ENDDATE 
AND JBCRT > '2012-01-01-00.00.00.000000'  -- bad data before 2012
AND JBSTS <> 'D'

UNION

-- History File -- 

SELECT
EQMDOM AS ID ,
CT1TID AS ABBREVIATION ,
CT1NAM AS NAME ,
JHCTL ,
JHWKA AS TYPE ,
CASE WHEN JHCDT BETWEEN @STARTDATENUM AND @ENDDATENUM THEN 1 ELSE 0 END AS COMPLETED ,
1 AS CREATEDORDUE ,
-- Stackexchange question
CASE 
   WHEN ( JHCDT BETWEEN @STARTDATENUM AND @ENDDATENUM AND JHDTC = 'RO' ) THEN 1 
   ELSE 0 
END AS COMPLETEDINSIDE ,
-- /Stackexchange question
MASTER.USDATE_DATEDIFFERENCE ( 
   CASE WHEN JHCDT = 0 THEN @ENDDATENUM WHEN JHCDT > @ENDDATENUM THEN @ENDDATENUM ELSE JHCDT END , 
   CAST ( MASTER.DATETOCYMDDATE ( DATE ( JHCRT ) ) AS DEC ( 7 , 0 ) ) , 
   CAST ( 'DAYS  ' AS CHAR ( 6 ) ) 
   ) AS DAYS

FROM EMJOBH
JOIN EMEQM ON JHUNT = EQMNUM
JOIN TABLE ( MASTER.CSVTABLE ( @LOCATIONS ) ) AS LOCATIONS ON VAL = EQMDOM
JOIN AAP030 ON CT1NUM = EQMDOM

WHERE
JHSYS = 'PM'
AND ( JHCDT >= MASTER.DATETOCYMDDATE ( DATE ( @STARTDATE ) ) OR JHCDT = 0 )
AND JHCRT <= @ENDDATE 
AND JHCRT > '2012-01-01-00.00.00.000000'
AND JHSTS <> 'D'

)
AS METRICS
JOIN JOBTYPES AS JOBTYPE
   ON METRICS.TYPE = JOBTYPE.TYPE
GROUP BY METRICS.ID , METRICS.ABBREVIATION , METRICS.NAME , METRICS.TYPE , JOBTYPE.NAME ;

在一個靜態 sql（過程）執行得比等效的更好的情況下，我會檢查：

a）這是否取決於執行順序，即第二個是否受益於第一個將數據從磁碟讀取到緩衝池中？如果一個執行得比另一個好，不管它們執行的順序如何，情況都不是這樣。

b) 計劃是否相同？如果一個過程是用一組統計數據編譯的，而另一個過程是用另一組統計數據編譯的，這可能會導致不同的計劃。嘗試使用相同的統計數據重新綁定（或重新創建）兩個過程，看看它們是否仍然不同

引用自：https://dba.stackexchange.com/questions/98616