SAP Knowledge Base Article - Preview

2125488 - Slow Correlated SubQuery performance in IQ16 SP08.x.

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 ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.