Agree!

If I were you I start by breaking the joins and produce temporary tables
that have lesser rows that the original tables... until I have the
desired result.

Do you understand what the query does?

If so try the approach I describe above combined with the directives for
hinting the optimizer if it does weird plans... (IDS certainly have
weird plans... sometimes

If not so... (I'm guessing 'cause for the select you post it seems that
it is automatically generated)... you are very unluck!! the only way I
imagine to optimze that query is knowing what it does and rewrite it.

Chucho!


Olmedo Monteverde wrote:
> Hi,
> You have a join select on 11 (!) tables. I'm not surprised your system
> hangs. I would suggest you to break your script down into several temporary
> tables. That will help a lot.
> Regards,
> OM
>
> "Wayne T" <ntrieu@yahoo.com> a écrit dans le message news:
> e6623a40.0309161548.270a4252@posting.google.com...
>
>>Hi All,
>>Can you explain me how to improve this sql statement. The "top"
>>command showed this SQL program took about 50% CPU resource. When it
>>run system kind of halt and nobody can run anything until it done. Any
>>help you could give me would be greatly appreciated.
>>
>>--Wayne
>>
>>Here is the out put from "set explain on"
>>
>>
>>QUERY:
>>------
>>select f.t_cofc, a.t_orno, a.t_pono, a.t_sqnb, a.t_cprj, b.t_item,
>> b.t_dsca, a.t_shpm, a.t_oqua, a.t_dqua, a.t_pric, a.t_oamt,
>> ldate(a.t_odat), ldate(a.t_dldt), ldate(a.t_prdt),
>>ldate(a.t_invd),
>> b.t_kitm, b.t_cpcl, b.t_cpln, a.t_cpcl, a.t_cpln, b.t_ctyp,
>>c.t_prog,
>> c.t_cont, d.t_nama, e1.t_nama, e2.t_nama, e3.t_nama,
>>e4.t_nama,
>> c.t_per1, a.t_ttyp, a.t_invn, f.t_corn, f.t_refa, g.t_dsca,
>> trim(h.t_name) || ', ' || h.t_cste || ' ' || trim(h.t_pstc),
>> decode(c.t_bpos, 1, 'SoleSource', 2, 'Competetive', 'NA'),
>> decode(c.t_btyp, 1, 'N', 2, 'F', 'X') from ttdsls401100 a,
>>ttcibd001100
>> b, tltsls400100 c, ttccom100100 d, ttccom001100 e1, outer
>>ttccom001100
>> e2, outer ttccom001100 e3, outer ttccom001100 e4, ttdsls400100
>>f,
>> outer ttcmcs045100 g, ttccom130100 h where f.t_futo not in
>>(2,3) and
>> a.t_item = b.t_item and a.t_orno = c.t_orno and a.t_ofbp =
>>d.t_bpid
>> and c.t_cadm = e1.t_emno and c.t_fina = e2.t_emno and c.t_rep1 =
>> e3.t_emno and c.t_bdev = e4.t_emno and a.t_orno = f.t_orno and
>>a.t_item
>> = b.t_item and f.t_creg = g.t_creg and a.t_stad = h.t_cadr and
>>a.t_oltp
>> <> 1 and a.t_clyn <> 1 and decode(f.t_cofc, 110, 101, 100)
>>between 100
>> and 999 and a.t_orno between '000000000' and 'ZZZZZZZZZ' and
>>exists
>> (select b.t_orno from ttdsls401100 b where b.t_orno = a.t_orno and
>> b.t_pono = a.t_pono and ldate(b.t_dldt) between '9/16/2003' and
>> '9/16/2003') order by a.t_orno, a.t_pono, a.t_sqnb
>>
>>Estimated Cost: 10504182
>>Estimated # of Rows Returned: 1
>>Temporary Files Required For: Order By
>>
>> 1) bsp.a: SEQUENTIAL SCAN
>>
>> Filters: ((((bsp.a.t_oltp != 1 AND bsp.a.t_clyn != 1 ) AND
>>EXISTS <subquery> ) AND bsp.a.t_orno <= 'ZZZZZZZZZ' ) AND bsp.a.t_orno
>>
>>>= '000000000' )
>>
>> 2) bsp.f: SEQUENTIAL SCAN
>>
>> Filters:
>> Table Scan Filters: ((CASE WHEN bsp.f.t_cofc = 110 THEN 101
>>ELSE 100 END<= 999 AND CASE WHEN bsp.f.t_cofc = 110 THEN 101 ELSE
>>100 END>= 100 ) AND bsp.f.t_futo NOT IN (2 , 3 ))
>>
>>DYNAMIC HASH JOIN
>> Dynamic Hash Filters: bsp.a.t_orno = bsp.f.t_orno
>>
>> 3) bsp.b: INDEX PATH
>>
>> Filters: bsp.a.t_item = bsp.b.t_item
>>
>> (1) Index Keys: t_item (Serial, fragments: ALL)
>> Lower Index Filter: bsp.a.t_item = bsp.b.t_item
>>NESTED LOOP JOIN
>>
>> 4) bsp.h: SEQUENTIAL SCAN
>>
>>DYNAMIC HASH JOIN (Build Outer)
>> Dynamic Hash Filters: bsp.a.t_stad = bsp.h.t_cadr
>>
>> 5) bsp.c: INDEX PATH
>>
>> (1) Index Keys: t_orno (Serial, fragments: ALL)
>> Lower Index Filter: bsp.a.t_orno = bsp.c.t_orno
>>NESTED LOOP JOIN
>>
>> 6) bsp.e1: INDEX PATH
>>
>> (1) Index Keys: t_emno (Serial, fragments: ALL)
>> Lower Index Filter: bsp.c.t_cadm = bsp.e1.t_emno
>>NESTED LOOP JOIN
>
>
>
>
>

--


Atte,


Jesús Antonio Santos Giraldo
jeansagi@myrealbox.com
jeansagi@netscape.net

sending to informix-list

hi,
MMh, try to sample cases where you can remove all the 'outer' clauses
in your request and run your request.
Conclusion ?
If positive, may you add a little bit of code to treat the cases where not
exists
datas extracted from the "outer" tables...
It just an idea.



"Jean Sagi" <jeansagi@myrealbox.com> a écrit dans le message de news:
bk9j43$4si$1@terabinaries.xmission.com...
>
> Agree!
>
> If I were you I start by breaking the joins and produce temporary tables
> that have lesser rows that the original tables... until I have the
> desired result.
>
> Do you understand what the query does?
>
> If so try the approach I describe above combined with the directives for
> hinting the optimizer if it does weird plans... (IDS certainly have
> weird plans... sometimes
>
> If not so... (I'm guessing 'cause for the select you post it seems that
> it is automatically generated)... you are very unluck!! the only way I
> imagine to optimze that query is knowing what it does and rewrite it.
>
> Chucho!
>
>
> Olmedo Monteverde wrote:
> > Hi,
> > You have a join select on 11 (!) tables. I'm not surprised your system
> > hangs. I would suggest you to break your script down into several
temporary
> > tables. That will help a lot.
> > Regards,
> > OM
> >
> > "Wayne T" <ntrieu@yahoo.com> a écrit dans le message news:
> > e6623a40.0309161548.270a4252@posting.google.com...
> >
> >>Hi All,
> >>Can you explain me how to improve this sql statement. The "top"
> >>command showed this SQL program took about 50% CPU resource. When it
> >>run system kind of halt and nobody can run anything until it done. Any
> >>help you could give me would be greatly appreciated.
> >>
> >>--Wayne
> >>
> >>Here is the out put from "set explain on"
> >>
> >>
> >>QUERY:
> >>------
> >>select f.t_cofc, a.t_orno, a.t_pono, a.t_sqnb, a.t_cprj, b.t_item,
> >> b.t_dsca, a.t_shpm, a.t_oqua, a.t_dqua, a.t_pric, a.t_oamt,
> >> ldate(a.t_odat), ldate(a.t_dldt), ldate(a.t_prdt),
> >>ldate(a.t_invd),
> >> b.t_kitm, b.t_cpcl, b.t_cpln, a.t_cpcl, a.t_cpln, b.t_ctyp,
> >>c.t_prog,
> >> c.t_cont, d.t_nama, e1.t_nama, e2.t_nama, e3.t_nama,
> >>e4.t_nama,
> >> c.t_per1, a.t_ttyp, a.t_invn, f.t_corn, f.t_refa, g.t_dsca,
> >> trim(h.t_name) || ', ' || h.t_cste || ' ' || trim(h.t_pstc),
> >> decode(c.t_bpos, 1, 'SoleSource', 2, 'Competetive', 'NA'),
> >> decode(c.t_btyp, 1, 'N', 2, 'F', 'X') from ttdsls401100 a,
> >>ttcibd001100
> >> b, tltsls400100 c, ttccom100100 d, ttccom001100 e1, outer
> >>ttccom001100
> >> e2, outer ttccom001100 e3, outer ttccom001100 e4, ttdsls400100
> >>f,
> >> outer ttcmcs045100 g, ttccom130100 h where f.t_futo not in
> >>(2,3) and
> >> a.t_item = b.t_item and a.t_orno = c.t_orno and a.t_ofbp =
> >>d.t_bpid
> >> and c.t_cadm = e1.t_emno and c.t_fina = e2.t_emno and c.t_rep1 =
> >> e3.t_emno and c.t_bdev = e4.t_emno and a.t_orno = f.t_orno and
> >>a.t_item
> >> = b.t_item and f.t_creg = g.t_creg and a.t_stad = h.t_cadr and
> >>a.t_oltp
> >> <> 1 and a.t_clyn <> 1 and decode(f.t_cofc, 110, 101, 100)
> >>between 100
> >> and 999 and a.t_orno between '000000000' and 'ZZZZZZZZZ' and
> >>exists
> >> (select b.t_orno from ttdsls401100 b where b.t_orno = a.t_orno and
> >> b.t_pono = a.t_pono and ldate(b.t_dldt) between '9/16/2003' and
> >> '9/16/2003') order by a.t_orno, a.t_pono, a.t_sqnb
> >>
> >>Estimated Cost: 10504182
> >>Estimated # of Rows Returned: 1
> >>Temporary Files Required For: Order By
> >>
> >> 1) bsp.a: SEQUENTIAL SCAN
> >>
> >> Filters: ((((bsp.a.t_oltp != 1 AND bsp.a.t_clyn != 1 ) AND
> >>EXISTS <subquery> ) AND bsp.a.t_orno <= 'ZZZZZZZZZ' ) AND bsp.a.t_orno
> >>
> >>>= '000000000' )
> >>
> >> 2) bsp.f: SEQUENTIAL SCAN
> >>
> >> Filters:
> >> Table Scan Filters: ((CASE WHEN bsp.f.t_cofc = 110 THEN 101
> >>ELSE 100 END<= 999 AND CASE WHEN bsp.f.t_cofc = 110 THEN 101 ELSE
> >>100 END>= 100 ) AND bsp.f.t_futo NOT IN (2 , 3 ))
> >>
> >>DYNAMIC HASH JOIN
> >> Dynamic Hash Filters: bsp.a.t_orno = bsp.f.t_orno
> >>
> >> 3) bsp.b: INDEX PATH
> >>
> >> Filters: bsp.a.t_item = bsp.b.t_item
> >>
> >> (1) Index Keys: t_item (Serial, fragments: ALL)
> >> Lower Index Filter: bsp.a.t_item = bsp.b.t_item
> >>NESTED LOOP JOIN
> >>
> >> 4) bsp.h: SEQUENTIAL SCAN
> >>
> >>DYNAMIC HASH JOIN (Build Outer)
> >> Dynamic Hash Filters: bsp.a.t_stad = bsp.h.t_cadr
> >>
> >> 5) bsp.c: INDEX PATH
> >>
> >> (1) Index Keys: t_orno (Serial, fragments: ALL)
> >> Lower Index Filter: bsp.a.t_orno = bsp.c.t_orno
> >>NESTED LOOP JOIN
> >>
> >> 6) bsp.e1: INDEX PATH
> >>
> >> (1) Index Keys: t_emno (Serial, fragments: ALL)
> >> Lower Index Filter: bsp.c.t_cadm = bsp.e1.t_emno
> >>NESTED LOOP JOIN
> >
> >
> >
> >
> >
>
> --
>
>
> Atte,
>
>
> Jesús Antonio Santos Giraldo
> jeansagi@myrealbox.com
> jeansagi@netscape.net
>
> sending to informix-list