半山云嵐

在做自動定價流程的性能測試中，發(fā)現(xiàn)一個很棘手的性能問題：一個非常簡單的查詢跑起來非常非常慢，幾乎每一秒鐘只從數(shù)據(jù)庫返回一條記錄！數(shù)據(jù)庫是zLinux Server 上的 DB2 8.5，CPU Utilization 只有 35% 而已。

花了大半天時間，最后我以為是數(shù)據(jù)庫的問題（因為是由別人管理的遠(yuǎn)程數(shù)據(jù)庫），SQL如此簡單應(yīng)該沒有問題；但是從DBA的反饋是他檢查了數(shù)據(jù)庫的所有狀態(tài)，一切正常:( 我?guī)缀醣罎ⅰ?/p>

但今天，最終還是承認(rèn)SQL確實有問題，而且犯大忌 （對查詢優(yōu)化的認(rèn)識有待提高啊…）

-- less 1 record inserted per second, 30000 records insertion need 10 hours
-- to complete (timeout...)
INSERT INTO WWPRT.CABLEPRODUCT_PRICETYPE_JOIN_CN (CABLEPRODUCTID,
PRICETYPE, SLAVE) 
(
     SELECT cableproduct.ID, pricetypes.PRICETYPE, 'N'
     FROM WWPRT.CABLE_PRODUCT_JOIN_CN AS cableproduct,
     (
          SELECT DISTINCT usprice.PRICETYPE FROM WWPRT.PRICE_CN usprice
          JOIN WWPRT.CONF_PRICETYPE pt ON pt.ID = usprice.PRICETYPE
          WHERE usprice.CABLEID = 'USCABF3FVT01'
          AND pt.DOMAIN IN ( SELECT DISTINCT DOMAIN FROM WWPRT.CONF_GAP )
     ) AS pricetypes
     WHERE CABLEID = 'GEOANNF3FVT1'
     AND EXISTS (
         ......
     )
)

其罪魁禍?zhǔn)拙褪恰癴rom”分句中的 subselect 語句，數(shù)據(jù)庫會對 WWPRT.CABLE_PRODUCT_JOIN_CN 表中的每一條記錄都執(zhí)行一次上面的 subselect 語句，太可怕了！修改了 SQL 如下，讓上面的 subselect 部分只執(zhí)行一次作為臨時表：

-- 30000 records insertion only costs 9 seconds to complete!
WITH US_PRICETYPES (PRICETYPE) AS (
    SELECT DISTINCT usprice.PRICETYPE FROM WWPRT.PRICE_CN usprice
    JOIN WWPRT.CONF_PRICETYPE pt ON pt.ID = usprice.PRICETYPE
    LEFT OUTER JOIN WWPRT.CONF_GAP gap ON pt.DOMAIN = gap.DOMAIN
    WHERE usprice.CABLEID = 'USCABF3FVT01'
    AND gap.DOMAIN IS NOT NULL
)
SELECT COUNT(*) FROM NEW TABLE
(
    INSERT INTO WWPRT.CABLEPRODUCT_PRICETYPE_JOIN_CN
    (CABLEPRODUCTID, PRICETYPE, SLAVE)
    (
    SELECT cableproduct.ID, pricetypes.PRICETYPE, 'N'
    FROM WWPRT.CABLE_PRODUCT_JOIN_CN AS cableproduct,
    US_PRICETYPES AS pricetypes
    WHERE CABLEID = 'GEOANNF3FVT1'
    AND EXISTS (
        ......
    )
))

性能的改進太可怕了，需要10個小時跑完的查詢10秒就結(jié)束了！盡管我用的是DB2，但我想對其它數(shù)據(jù)庫應(yīng)該也有這樣的問題，沒有驗證過…

Followed:
--------------------------------------------------------------------------------------
謝謝Daniel的建議：

如果程序不關(guān)心究竟有多少條記錄被插入了，可以用另一個 Fetch，而不是 Count(*)，這樣性能會更好：

SELECT CABLEPRODUCTID FROM NEW TABLE
(INSERT INTO WWPRT.CABLEPRODUCT_PRICETYPE_JOIN_CN (CABLEPRODUCTID,
PRICETYPE, SLAVE)
(
     SELECT cableproduct.ID, pricetypes.PRICETYPE, 'N'
     FROM WWPRT.CABLE_PRODUCT_JOIN_CN AS cableproduct,
     US_PRICETYPES AS pricetypes
     WHERE CABLEID = 'GEOANNF3FVT1'
     AND EXISTS (
         ......
     )
)) FETCH FIRST 1 ROWS ONLY