Re: How to improve SQL

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