在做自动定价流程的性能测试中，发现一个很棘手的性能问题：一个非常简单的查询跑起来非常非常慢，几乎每一秒钟只从数据库返回一条记录！数据库是zLinux Server 上的 DB2 8.5，CPU Utilization 只有 35% 而已。
花了大半天时间，最后我以为是数据库的问题（因为是由别人管理的远程数据库），SQL如此简单应该没有问题；但是从DBA的反馈是他检查了数据库的所有状态，一切正常:( 我几乎崩溃…
但今天，最终还是承认SQL确实有问题，而且犯大忌 （对查询优化的认识有待提高啊…）
-- 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 (
......
)
)
其罪魁祸首就是“from”分句中的 subselect 语句，数据库会对 WWPRT.CABLE_PRODUCT_JOIN_CN 表中的每一条记录都执行一次上面的 subselect 语句，太可怕了！修改了 SQL 如下，让上面的 subselect 部分只执行一次作为临时表：
-- 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秒就结束了！尽管我用的是DB2，但我想对其它数据库应该也有这样的问题，没有验证过…
Followed:
--------------------------------------------------------------------------------------
谢谢Daniel的建议：
如果程序不关心究竟有多少条记录被插入了，可以用另一个 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