Re: One HUGE Query - any optimizers?
From: "george" <georgem@its.soft.net>
>
>I've developed a program which constructs the following query and then
>executes it.
I trust you're suitably proud? :)
>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 :)
That's funny -- that's just what she said last night!
>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.
According to Kagel's Laws of SQL, there's always more than one way (three
ways?) of writing the same select.
Can you send us schemas of the tables and the number of rows in each?
>Any takers? :)
That's funny....
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
Obnoxio The Clown wrote: > > From: "george" <georgem@its.soft.net> > > > >(HUGE, isn't it :) > > That's funny -- that's just what she said last night! ROFL............. aj
In article <7qjfu5$5ng$1@news.xmission.com>, Obnoxio The Clown
<obnoxio@hotmail.com> writes
>
>From: "george" <georgem@its.soft.net>
>>
>>I've developed a program which constructs the following query and then
>>executes it.
>
>I trust you're suitably proud? :)
>
>>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_l
>dc_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 :)
>
>That's funny -- that's just what she said last night!
>
>
>Can you send us schemas of the tables and the number of rows in each?
>
Also a sqexplain.out for what the query is currently doing...
(sqexplain.out have it split so you can see how each table is accessed
separately and which filters are used. Easier to approach this one
table at a time.). Also how long does it currently take to run?
Is this under Online and if so how many CPUVPS do you have?
>>Any takers? :)
>
>That's funny....
>
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
--
David Williams