Hi
I have a poor performance sql using "Not Exists" as follows.
1. Problems
When I executed a sql with not exists, It took a long time to be completed.
It took "5959.16" seconds to be completed.
[SQL]
SELECT COUNT(*) AS CNT
FROM TBIA70D 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 TBIA59D A, TBIA70A 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 );
2. Solution
IQ optimizer is hard to find the best query plan without indexes on join columns.
To gain the fastest processing of joins, All join columns should have indexes such as HG, LF.
Because the IQ optimizer may need metadata from the HG/LF index to produce an optimal query plan.
The Query took "12" seconds to be completed after creating below indexes.
Ex)
create hg index TBIA59D_MGT_ACCT_NO_hg on TBIA59D(MGT_ACCT_NO);
create hg index TBIA70A_MGT_ACCT_NO_hg on TBIA70A(MGT_ACCT_NO);
create hg index TBIA70D_MGT_ACCT_NO_hg on TBIA70D(MGT_ACCT_NO);
** If you set the INDEX_ADVISOR option on your database, SAP Sybase IQ issues messages in the message log or query plan to suggest additional indexes that might improve performance.
And It might be sometimes more faster with left outer join not using "Not Exists".
[SQL]
SELECT COUNT(*) AS CNT
FROM TBIA70D AS C LEFT OUTER JOIN
( SELECT A.MGT_ACCT_NO
FROM TBIA59D A, TBIA70A 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;
HTH.
Gi-Sung Jang
