Re: One HUGE Query - any optimizers?
>>> "george" <georgem@its.soft.net> 09/01/99 03:21PM >>>
>I've developed a program which constructs the following query and then executes it.
>
>INSERT INTO t_temp_aps (crdt_acct_id, critr_cd, prod_id, prod_init_dt, >acct_id,acct_stat_cd)
>SELECT DISTINCT t_applacct.acct_num,'SC2', t_applacct.prod_id, >t_applacct.appl_prod_init_dt, t_tsys_acct.acct_id,' '
>FROM >t_applacct,t_aps_batch_item,t_batch,t_cust_demog,t_tsys_acct,t_ldc_pgrp_dtl,t_ldc_critr
>WHERE t_applacct.applacct_id = t_aps_batch_item.applacct_id
>and t_aps_batch_item.batch_id = t_batch.batch_id
>and t_batch.batch_content_type <>'P'
>AND t_batch.batch_pay_type ='G'
>AND t_batch.apply_fee_yn ='Y'
>AND t_applacct.aps_cust_id = t_cust_demog.aps_cust_id
>AND t_cust_demog.chk_acct_yn ='Y'
>AND t_cust_demog.chk_acct_bal >=1000
>AND t_applacct.appl_fastrack_yn ='Y'
>AND t_applacct.aps_credit_attr[42,42] < 5
>AND t_applacct.prod_id = t_ldc_pgrp_dtl.prod_id
>AND t_ldc_pgrp_dtl.pgrp_id = t_ldc_critr.pgrp_id
>AND t_ldc_critr.critr_cd = 'SC2'
>AND t_tsys_acct.crdt_acct_id = t_applacct.acct_num
(HUGE, isn't it :)
Errrr No
>Can there be an optimised version of the above code? I used to construct this query >earlier using just one table (t_applacct) in the FROM clause and all the other conditions >satisfied through subqueries. But this method used to run for a long time. The above >query doesn't as much time, but still we are looking for a way to speed it up.
Number of rows etc would be useful, as would a sqexplain.
I'd probably start by using some temp tables.
Paul Watson
WF Software Ltd
Tel: +44 1436 674729
Fax: +44 1436 678729
www.wfsoftware.com/informix
# If you broke it, hide the evidence
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************