Skip to Content

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     

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply