One HUGE Query - any optimizers?
This is a multi-part message in MIME format.
------=_NextPart_000_0082_01BEF4B3.58C19EC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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)=20
SELECT DISTINCT t_applacct.acct_num,'SC2', t_applacct.prod_id, =t_applacct.appl_prod_init_dt, t_tsys_acct.acct_id,' '=20
FROM =
t_applacct,t_aps_batch_item,t_batch,t_cust_demog,t_tsys_acct,t_ldc_pgrp_d=
tl,t_ldc_critr=20
WHERE t_applacct.applacct_id =3D t_aps_batch_item.applacct_id=20
and t_aps_batch_item.batch_id =3D t_batch.batch_id=20
and t_batch.batch_content_type <>'P'=20
AND t_batch.batch_pay_type =3D'G'=20
AND t_batch.apply_fee_yn =3D'Y'=20
AND t_applacct.aps_cust_id =3D t_cust_demog.aps_cust_id=20
AND t_cust_demog.chk_acct_yn =3D'Y'=20
AND t_cust_demog.chk_acct_bal >=3D1000=20
AND t_applacct.appl_fastrack_yn =3D'Y'=20
AND t_applacct.aps_credit_attr[42,42] < 5=20
AND t_applacct.prod_id =3D t_ldc_pgrp_dtl.prod_id=20
AND t_ldc_pgrp_dtl.pgrp_id =3D t_ldc_critr.pgrp_id=20
AND t_ldc_critr.critr_cd =3D 'SC2'=20
AND t_tsys_acct.crdt_acct_id =3D 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
------=_NextPart_000_0082_01BEF4B3.58C19EC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2014.210" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I've developed a program which =
constructs the=20
following query and then executes it.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>INSERT INTO t_temp_aps (crdt_acct_id, =
critr_cd,=20
prod_id, prod_init_dt, acct_id,acct_stat_cd) <BR>SELECT DISTINCT=20
t_applacct.acct_num,'SC2', t_applacct.prod_id, =
t_applacct.appl_prod_init_dt,=20
t_tsys_acct.acct_id,' ' <BR>FROM=20
t_applacct,t_aps_batch_item,t_batch,t_cust_demog,t_tsys_acct,t_ldc_pgrp_d=
tl,t_ldc_critr=20
<BR>WHERE t_applacct.applacct_id =3D t_aps_batch_item.applacct_id =
<BR>and=20
t_aps_batch_item.batch_id =3D t_batch.batch_id <BR>and =
t_batch.batch_content_type=20
<>'P' <BR>AND t_batch.batch_pay_type =3D'G' <BR>AND =
t_batch.apply_fee_yn=20
=3D'Y' <BR>AND t_applacct.aps_cust_id =3D t_cust_demog.aps_cust_id =
<BR>AND=20
t_cust_demog.chk_acct_yn =3D'Y' <BR>AND t_cust_demog.chk_acct_bal =
>=3D1000=20
<BR>AND t_applacct.appl_fastrack_yn =3D'Y' <BR>AND=20
t_applacct.aps_credit_attr[42,42] < 5 <BR>AND t_applacct.prod_id =3D=20
t_ldc_pgrp_dtl.prod_id <BR>AND t_ldc_pgrp_dtl.pgrp_id =3D =
t_ldc_critr.pgrp_id=20
<BR>AND t_ldc_critr.critr_cd =3D 'SC2' <BR>AND t_tsys_acct.crdt_acct_id =
=3D=20
t_applacct.acct_num</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>(HUGE, isn't it :)</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Can there be an optimised version =
of the above=20
code? I used to construct this query earlier using just one table =
(t_applacct)=20
in the FROM clause and all the other conditions satisfied through =
subqueries.=20
But this method used to run for a long time. The above query doesn't as =
much=20
time, but still we are looking for a way to speed it up.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Informix 7.20UC1 on Sun =
Solaris.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Any takers? :)</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in advance..</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>George</FONT></DIV></BODY></HTML>
------=_NextPart_000_0082_01BEF4B3.58C19EC0--