One HUGE Query - any optimizers?

One HUGE Query - any optimizers?

Post by georg » Sun, 31 Dec 1899 09:00:00



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>&nbsp;</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
&lt;&gt;'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 =
&gt;=3D1000=20
<BR>AND t_applacct.appl_fastrack_yn =3D'Y' <BR>AND=20
t_applacct.aps_credit_attr[42,42] &lt; 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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>(HUGE, isn't it :)</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Can&nbsp;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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Informix 7.20UC1 on Sun =
Solaris.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Any takers? :)</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in advance..</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>George</FONT></DIV></BODY></HTML>

------=_NextPart_000_0082_01BEF4B3.58C19EC0--

 
 
 

One HUGE Query - any optimizers?

Post by Obnoxio The Clow » Sun, 31 Dec 1899 09:00:00



Quote:

>I've developed a program which constructs the following query and then
>executes it.

I trust you're suitably proud? :)

Quote:>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!

Quote:>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?

Quote:>Any takers? :)

That's funny....

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

 
 
 

One HUGE Query - any optimizers?

Post by Paul watso » Sun, 31 Dec 1899 09:00:00



>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

Quote:>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
**********************************************************************

 
 
 

One HUGE Query - any optimizers?

Post by allen » Sun, 31 Dec 1899 09:00:00




> >(HUGE, isn't it :)

> That's funny -- that's just what she said last night!

ROFL.............

aj

 
 
 

One HUGE Query - any optimizers?

Post by David William » Sun, 31 Dec 1899 09:00:00





>>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?

Quote:>>Any takers? :)

>That's funny....

>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com

--
David Williams
 
 
 

One HUGE Query - any optimizers?

Post by Paul watso » Sun, 31 Dec 1899 09:00:00




>> >(HUGE, isn't it :)

>> That's funny -- that's just what she said last night!

>ROFL.............

Is that due to the fact you know it to be untrue:-))

Paul

**********************************************************************
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
**********************************************************************

 
 
 

One HUGE Query - any optimizers?

Post by Obnoxio The Clow » Sun, 31 Dec 1899 09:00:00





> >> >(HUGE, isn't it :)

> >> That's funny -- that's just what she said last night!

> >ROFL.............

>Is that due to the fact you know it to be untrue:-))

Oh yeah, and when did _you_ measure it? :-)

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

 
 
 

One HUGE Query - any optimizers?

Post by allen » Sun, 31 Dec 1899 09:00:00





> >> >(HUGE, isn't it :)

> >> That's funny -- that's just what she said last night!

> >ROFL.............

> Is that due to the fact you know it to be untrue:-))

No.....your wife told me it was untrue........   :-)

aj

Quote:> **********************************************************************
> 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
> **********************************************************************

 
 
 

1. 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

2. 16 & 32 bit Registry entries

3. MS Access to SQL Server with optimizer hints in query or less selective optimizer (more details)

4. Pick or Basic Unidata P-A to $60,000,

5. Huge Table vs Smaller Ones

6. Ora-03113 & Ora 01041

7. Strange thing, one comment cause huge difference on performance

8. Enterprise Manager Timeout

9. One for the code optimizers