Oninit cdi / Archive
Talk to an expert →

Full Join slow in IDS 9.40 FC6

Posted in 2009

Topics: Performance & Tuning, SQL Development & Query Writing, Stored Procedures & SPL, Server Administration, Migration, Import/Export & Data Conversion, Platform-Specific Issues, Internationalization & Character Sets, Versions, Editions & End-of-Life

SYED AHMAD NAJMI SYED MD NASIR — — source: IIUG Forums & Mailing Lists

Hi I need an advice on the following item which I ecnountered a problem. Running IBM IDS 9.40 FC6 on IBM AIX 5.3 platform We have 2 main tables called bc_ledger (100 000+ rows) and bc_trxsummary (1 200 000+ rows). Below are the SQL statements we used:- Option 1:- create view ir_ob1 (bctx_bcackey,bctx_ledgertype,bctx_year,bctx_source, bctx_itemtype,bctx_itemref,bctx_item2ndref,bctx_date,bctx_amount, bctx_bctdkey,bctx_postdtime,bclg_bcackey,bclg_year,bclg_type,bclg_amount)as select bctx_bcackey ,bctx_ledgertype ,bctx_year ,bctx_source,bctx_itemtype, bctx_itemref,bctx_item2ndref,bctx_date,bctx_amount,bctx_bctdkey,bctx_postdtime, bclg_bcackey,bclg_year,bclg_type ,bclg_amount from (bc_trxsummary full join bc_ledger on (((bctx_year = bclg_year) AND (bctx_bcackey = bclg_bcackey ) ) AND (bctx_ledgertype= bclg_type ) ) ); Then we did select * from ir_ob1; # this take long time. By looking at sqexplain, the optimizer considered as seqential scan.At one point we are got the physical lock error messages. Option 2:- Just picked the select query: select bctx_bcackey ,bctx_ledgertype ,bctx_year ,bctx_source,bctx_itemtype, bctx_itemref,bctx_item2ndref,bctx_date,bctx_amount,bctx_bctdkey,bctx_postdtime, bclg_bcackey,bclg_year,bclg_type ,bclg_amount from (bc_trxsummary full join bc_ledger on (((bctx_year = bclg_year) AND (bctx_bcackey = bclg_bcackey ) ) AND (bctx_ledgertype= bclg_type ) ) ); The output is faster displayed inside dbaccess screen. But when we try to unload to a file or change the select query to select count(*) it will long. Isimulate the script at night 1100am during nobody access the system. But until morning it wasn’t finished. Inside the sqexplain it was : Sequential scan and Index Path been utilized. Need your advice. Is there anything related to below URL :- http://www-01.ibm.com/support/docview.wss?rs=0&context=SSGU5D&context=SSGU8G&con text=SSGKNY&context=SSGU5Y&context=SSCRW7&context=SSGHZP&context=SSVT2J&context= SSHPYE&q1=fixlist&q2=9.40&uid=swg27009759&loc=en_US&cs=utf-8&lang= Legacy Defect = 173099 Regards

↪ replying to SYED AHMAD NAJMI SYED MD NASIR
Jonathan Leffler — — source: IIUG Forums & Mailing Lists

On Tue, Mar 3, 2009 at 8:59 PM, SYED AHMAD NAJMI SYED MD NASIR <najmi@centurysoftware.com.my> wrote: > Hi > > I need an advice on the following item which I ecnountered a problem. > Running IBM IDS 9.40 FC6 on IBM AIX 5.3 platform > We have 2 main tables called bc_ledger (100 000+ rows) and bc_trxsummary (1 > 200 000+ rows). > > Below are the SQL statements we used:- > > Option 1:- > > create view ir_ob1 (bctx_bcackey,bctx_ledgertype,bctx_year,bctx_source, > bctx_itemtype,bctx_itemref,bctx_item2ndref,bctx_date,bctx_amount, > bctx_bctdkey,bctx_postdtime,bclg_bcackey,bclg_year,bclg_type,bclg_amount)> as > select bctx_bcackey ,bctx_ledgertype ,bctx_year ,bctx_source,bctx_itemtype,> > bctx_itemref,bctx_item2ndref,bctx_date,bctx_amount,bctx_bctdkey,bctx_postdtime, > bclg_bcackey,bclg_year,bclg_type ,bclg_amount > from (bc_trxsummary full join bc_ledger on > (((bctx_year = bclg_year) > AND (bctx_bcackey = bclg_bcackey ) ) > AND (bctx_ledgertype= bclg_type ) ) ); > > Then we did > select * from ir_ob1;> > # this take long time. By looking at sqexplain, the optimizer considered as > seqential scan.At one point we are got the physical lock error messages. > > Option 2:- > Just picked the select query: > > select bctx_bcackey ,bctx_ledgertype ,bctx_year ,bctx_source,bctx_itemtype,> > bctx_itemref,bctx_item2ndref,bctx_date,bctx_amount,bctx_bctdkey,bctx_postdtime, > bclg_bcackey,bclg_year,bclg_type ,bclg_amount > from (bc_trxsummary full join bc_ledger on > (((bctx_year = bclg_year) > AND (bctx_bcackey = bclg_bcackey ) ) > AND (bctx_ledgertype= bclg_type ) ) ); > > The output is faster displayed inside dbaccess screen. But when we try to > unload to a file or change the select query to select count(*) it will long.I > simulate the script at night 1100am during nobody access the system. But until > morning it wasn’t finished. Inside the sqexplain it was : Sequential scan and > Index Path been utilized. > > Need your advice. > > Is there anything related to below URL :- > > http://www-01.ibm.com/support/docview.wss?rs=0&context=SSGU5D&context=SSGU8G&con text=SSGKNY&context=SSGU5Y&context=SSCRW7&context=SSGHZP&context=SSVT2J&context= SSHPYE&q1=fixlist&q2=9.40&uid=swg27009759&loc=en_US&cs=utf-8&lang= > > Legacy Defect = 173099 No, it probably isn't directly that defect, which refers to an IS NULL clause with FULL JOIN. There have been improvements in the optimization of FULL JOIN in more recent versions of IDS; it is time you upgraded. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.

Glossary