百度云_cdn测试站_试用

数据库 虚拟云 浏览

小编: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 A

百度云_cdn测试站_试用

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

'Not exists' topic title... not exists anywhere in your example. Sorry did not get, what exactly you want to prove here.

Hi

Sorry to confuse.

I modified the contents.

,数据库系统,云上数据,大数据与云,网件云存储,y云服务器

文章来源:www.vmchk.com

 
你可能喜欢的: