Slow Query

Posted in 2003

Topics: Performance & Tuning, SQL Development & Query Writing

Stuart Harrison — 8 Dec 2003 01:46:34 -0800 — source: Usenet: comp.databases.informix

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?

↪ replying to Stuart Harrison

Obnoxio The Clown — Mon, 08 Dec 2003 14:17:59 +0000 — source: Usenet: comp.databases.informix

What version/platform? How does: SELECT ti.teleph_id, ti.teleph_t FROM tele_id ti, tele_prd tp, cus_prod cp, prd_pmtn pp 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 = cp.subscrtn_t -- might be pp. AND acc_t = 743 AND cusprd_sts = '2' AND pp.pmtn_t = cp.pmtn_t AND pp.fndtn_t IN (152,176)) perform? Also, it is a very common fallacy that an arbitrary index path is of any use. The _right_ index path is the useful one, so on the tele_id table, I'd probably want a composite index on teleph_t, lck_ssn_id, lck_ord_t and tele_av_dt. Possibly add the teleph_id column to the index to make it a key-only search. On the tele_prd table, I'd probably want one on teleph_t, tele_p_sts and subscrtn_t. cus_prod probably needs a composite on pmtn_t, cusprd_sts and acc_t. Some jiggery pokery (aka testing) may be needed to find the optimum order. Stuart Harrison wrote: > 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? -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche