RE: One HUGE Query - any optimisers?

Size isn't everything. (Although I've seen bigger :) Could do with an idea of the table structure. what columns do indexes live on. (If any :) ) If you run the SQL with set explain on then the output produced by that would help as well. Could also do with an idea of the amount of data likely to be retrieved for each part of the where clause, otherwise the words peeing and wind come to mind. Cheers Rekaish -----Original Message----- From: george [mailto:georgem@its.soft.net] Sent: Wednesday, September 01, 1999 3:21 PM To: Informix-List (E-mail) Subject: One HUGE Query - any optimizers? 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 :) 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. Informix 7.20UC1 on Sun Solaris. Any takers? :) Thanks in advance.. George