Symptom
In IQ16 SP8.x, a 'Not Exists' Subquery performance was very slow, comparing to re-written Outer Join SQL.
After re-write the 'Not Exists' SQL to Left Outer Join format, this new SQL finished fast. At that time Join column of the Sub-query had only FP index.
Another founded clue is that,inspite of without additional index on join column, Left Outer Join query worked well. But 'Not Exists' query took very long.
[ Customer "Not Exists" SQL ]
SELECT COUNT(*) AS CNT
FROM **70D AS C
WHERE C.B_ST_DT between '2010-04-01' and '2010-04-30'
AND C.SEG_GB_BIT LIKE '00%'
AND NOT EXISTS
( SELECT 'X' FROM ( SELECT A.MGT_ACCT_NO
FROM **59D A, **70A B
WHERE A.MGT_ACCT_NO = B.MGT_ACCT_NO
AND A.B_ST_DT between '2010-04-01' and '2010-04-30'
AND A.D0_REG_CD IN ('01','02')) AS B
WHERE B.MGT_ACCT_NO = C.MGT_ACCT_NO ) ;
[ Re-written SQL : "Left Outer Join" ]
SELECT COUNT(*) AS CNT
FROM **70D AS C LEFT OUTER JOIN
( SELECT Distinct A.MGT_ACCT_NO
FROM **59D A, **70A B
WHERE A.MGT_ACCT_NO = B.MGT_ACCT_NO
AND A.B_ST_DT between '2010-04-01' and '2010-04-30'
AND A.D0_REG_CD IN ('01','02')
) AS B
ON B.MGT_ACCT_NO = C.MGT_ACCT_NO
WHERE C.B_ST_DT between '2010-04-01' and '2010-04-30'
AND C.SEG_GB_BIT LIKE '00%'
AND B.MGT_ACCT_NO IS NULL ;
-- Table Rows => **59D : 7,872,000 // **70A : 10,541,000 // **70D : 16,619,000
Read more...
Environment
SAP IQ16 SP08.x
Keywords
Correlation subquery, Subquery Flatten, "Not Exists", "Not IN", , KBA , BC-SYB-IQ , Sybase IQ , How To
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.