Hi ntrieu

this link will help you

http://www.klimaexpert.com/gorazd/informix/03_3365.pdf

Regard

HITEN .S.BHAWSAR




-----Original Message-----
From: David McPaul [mailto:dmcpaul@lumley.com.au]
Sent: Wednesday, September 17, 2003 7:56 AM
To: informix-list@iiug.org
Subject: RE: How to improve SQL


Some thoughts.

How up to date are your statistics?

Obviously if we can remove the sequential scans we should improve
performance
> 1) bsp.a: SEQUENTIAL SCAN
> 2) bsp.f: SEQUENTIAL SCAN

So what is the size of these 2 tables?

The following part of the where clause worries me somewhat
a.t_orno between '000000000' and 'ZZZZZZZZZ'

Is it really possible to have t_orno outside of this range?

I suspect that the optimiser knows that all values of t_orno are in this
range so it does a sequential scan.

I think optimising this subquery would help as well

(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')

Is the b.t_orno = a.t_orno and b.t_pono = a.t_pono part really necessary?
also why convert the date to a string to do the compare?

Cheers
David

> -----Original Message-----
> From: ntrieu@yahoo.com
> Sent: Wednesday, September 17, 2003 9:48 AM
> To: informix-list@iiug.org
> Subject: How to improve SQL
>
>
> 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
>

This e-mail is intended for the use of the individual or entity named above
and may contain information that is confidential and privileged. If you are
not the intended recipient, you are hereby notified that any dissemination,
distribution or copying of this e-mail is strictly prohibited. If you have
received this e-mail in error, please notify us immediately at
helpdesk@lumley.com.au and destroy the original message. While this mail
and any attachments have been scanned for common computer viruses and found
to be virus free, we recommend you also perform your own virus checking
processes before opening any attachments.
sending to informix-list
sending to informix-list