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
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