Re: How to improve SQL

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