RE: Slow Query
> -----Original Message-----
> From: Dirk Moolman [SMTP:DirkM@mxgroup.co.za]
> Sent: Monday, December 08, 2003 7:58 AM
> To: Stuart Harrison; informix-list@iiug.org
> Subject: RE: Slow Query
>
> One thing I always try to stay away from (although I'm not much into SQL
> anymore), is the
>
> Where fieldname in ( ) syntax - this can be very slow
[Bill Dare]
A little something I picked up from Jack Parker:
If "where fieldname in ( )" is slow try "where (not) exists(
)".
I'll forward Jack's original email to you, Stuart. He explains
exactly what effect this has.
Regards,
Bill
> The people who are more current can just comment on this.
>
>
>
> -----Original Message-----
> From: Stuart Harrison [mailto:stuart.harrison@cognito.co.uk]
> Sent: Monday, December 08, 2003 11:47 AM
> To: informix-list@iiug.org
> Subject: Slow Query
>
> I am running a query that currently take about 40 seconds, this joins
> 3 tables. Below is the output from the Explain :-
>
> QUERY:
> ------
> SELECT ti.teleph_id, ti.teleph_t
> FROM tele_id ti, tele_prd tp
> WHERE ti.teleph_t = tp.teleph_t AND tp.tele_p_sts = '2'
> AND tele_av_dt <= '08 Dec 2003' AND lck_ord_t IS NULL
> AND lck_ssn_id IS NULL AND tp.subscrtn_t IN
> (SELECT subscrtn_t FROM cus_prod cp, prd_pmtn pp
> WHERE acc_t = 743 AND cusprd_sts = '2'
> AND pp.pmtn_t = cp.pmtn_t AND pp.fndtn_t IN (152,176))>
> Estimated Cost: 550
> Estimated # of Rows Returned: 1
>
> 1) css.tp: INDEX PATH
>
> Filters: css.tp.tele_p_sts = '2'
>
> (1) Index Keys: subscrtn_t (Serial, fragments: ALL)
> Lower Index Filter: css.tp.subscrtn_t = ANY <subquery>
>
> 2) css.ti: INDEX PATH
>
> Filters: ((css.ti.lck_ssn_id IS NULL AND css.ti.lck_ord_t IS
> NULL ) AND css.ti.tele_av_dt <= 08-
> 12-2003 )
>
> (1) Index Keys: teleph_t (Serial, fragments: ALL)
> Lower Index Filter: css.ti.teleph_t = css.tp.teleph_t
> NESTED LOOP JOIN
>
> Subquery:
> ---------
> Estimated Cost: 548
> Estimated # of Rows Returned: 8
>
> 1) css.pp: INDEX PATH
>
> (1) Index Keys: fndtn_t (Serial, fragments: ALL)
> Lower Index Filter: css.pp.fndtn_t = 152
>
> (2) Index Keys: fndtn_t (Serial, fragments: ALL)
> Lower Index Filter: css.pp.fndtn_t = 176
>
> 2) css.cp: INDEX PATH
>
> Filters: (css.pp.pmtn_t = css.cp.pmtn_t AND
> css.cp.cusprd_sts = '2' )
>
> (1) Index Keys: acc_t (Serial, fragments: ALL)
> Lower Index Filter: css.cp.acc_t = 743
> NESTED LOOP JOIN
>
> However if I remove most of the where clause apart from the joins the
> query returns almost immediately. I would like to know 1) why it
> takes so long when the selection clauses are on indexed fields? 2) if
> it is possible to improve the speed?
>
> sending to informix-list
sending to informix-list