IDS Forum

Re: Performance issue

Posted By: Art Kagel
Date: Wednesday, 13 April 2011, at 12:02 p.m.

In Response To: Performance issue (TRI TRINH)

Here's a suggestion. Moving the filters on tf010.t_comp and tf001.t_osta
from the WHERE clause to the ON clause will eliminate the need to create a
temp table with the results of the joins and apply those filters post-join.
However, note that you may also try moving them into the ON clause for the
join to the ti001 table which may eliminate another level of intermediate
temp table. Additional notes and questions after the query:

select tf010.t_pdno, tf010.t_prdt, tf010.t_opno, tf010.t_tano, tf010.t_mcno,

tf010.t_cwoc, tf010.t_comp, tf001.t_osta t_osta_tf001, tf001.t_mitm

t_mitm_tf001, tf001.t_cprj t_cprj_tf001, tf001.t_pdno t_pdno_tf001,

tf001.t_qrdr t_qrdr_tf001, tf001.t_prcd t_prcd_tf001, tr001.t_cwoc

t_cwoc_tr001, tr001.t_dsca t_dsca_tr001, tr002.t_mcno t_mco_tr002,

tr002.t_dsca t_dsca_tr002, ti001.t_item t_item_ti001, ti001.t_dsca

t_dsca_ti001, tp021.t_dsca t_dsca_tp021, tp021.t_item t_item_tp021
from (

ttisfc010104 tf010

left outer join (

ttisfc001104 tf001

left outer join ttiitm001104 ti001

on tf001.t_mitm = ti001.t_item

left outer join ttipcs021104 tp021

on tf001.t_mitm = tp021.t_item

)

on tf010.t_pdno = tf001.t_pdno

AND tf010.t_comp = '2'

AND tf001.t_osta IN ('5', '4')

)
left outer join ttirou001104 tr001

on tf010.t_cwoc = tr001.t_cwoc
left outer join ttirou002104 tr002

on tf010.t_mcno = tr002.t_mcno
;

Several points, some do not apply specifically to the upgrade:

- Did you drop all distributions and recreate them after the upgrade?

- This is a seven table join, you might try SET OPTIMIZATION LOW to

reduce the cost of optimizing this query. It may improve runtime.

- Did you run the original query under SET EXPLAIN on both 11.10 and

11.50 to see how the two engines are handling the query differently (or at

least on 11.50)?

- Can you post the SET EXPLAIN output from the original version and my

suggestion (and any other alternatives suggested)?

- The filter on tf001.t_osta and tf010.t_comp are comparing the columns

to numeric values as quotes strings. If the columns are actually numeric

types changing those filters to use numbers rather than strings will improve

performance.

Note that the handling of some ANSI '92 style queries was changed in 11.50
from earlier releases, so it MAY be that you are hitting a bug or
performance hit from those changes. Ultimately you may have to open a case
with IBM.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Wed, Apr 13, 2011 at 11:33 AM, TRI TRINH <tri_trinh@hotmail.com> wrote:

> Hi,
>
> We just upgraded our IDS from 10.10.FC6 to 11.50.FC8 in HP-UX11.11. One of
> our
> queries runs through ODBC connection has issue with performance after the
> upgrade. Please give us some advice. Thanks
>
> select tf010.t_pdno, tf010.t_prdt, tf010.t_opno, tf010.t_tano,
> tf010.t_mcno,
> tf010.t_cwoc, tf010.t_comp, tf001.t_osta t_osta_tf001, tf001.t_mitm
> t_mitm_tf001, tf001.t_cprj t_cprj_tf001, tf001.t_pdno t_pdno_tf001,
> tf001.t_qrdr t_qrdr_tf001, tf001.t_prcd t_prcd_tf001, tr001.t_cwoc
> t_cwoc_tr001, tr001.t_dsca t_dsca_tr001, tr002.t_mcno t_mco_tr002,
> tr002.t_dsca t_dsca_tr002, ti001.t_item t_item_ti001, ti001.t_dsca
> t_dsca_ti001, tp021.t_dsca t_dsca_tp021, tp021.t_item t_item_tp021 from
> (ttisfc010104 tf010 left outer join (ttisfc001104 tf001 left outer join
> ttiitm001104 ti001 on tf001.t_mitm = ti001.t_item left outer join
> ttipcs021104
> tp021 on tf001.t_mitm = tp021.t_item) on tf010.t_pdno = tf001.t_pdno) left
> outer join ttirou001104 tr001 on tf010.t_cwoc = tr001.t_cwoc left outer
> join
> ttirou002104 tr002 on tf010.t_mcno = tr002.t_mcno where (tf010.t_comp =
> '2')
> AND (tf001.t_osta = '5' OR tf001.t_osta = '4')
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--bcaec54859bede759904a0ceee87

Messages In This Thread

• Performance issue
  TRI TRINH -- Wednesday, 13 April 2011, at 11:33 a.m.
  • Re: Performance issue
    Alexandre Marini -- Wednesday, 13 April 2011, at 11:42 a.m.
    • Re: Performance issue
      TRI TRINH -- Wednesday, 13 April 2011, at 11:51 a.m.
      • Re: Performance issue
        Art Kagel -- Wednesday, 13 April 2011, at 12:14 p.m.
  • Re: Performance issue
    Art Kagel -- Wednesday, 13 April 2011, at 12:02 p.m.
    • Re: Performance issue
      TRI TRINH -- Wednesday, 13 April 2011, at 4:21 p.m.
      • Re: Performance issue
        TRI TRINH -- Tuesday, 19 April 2011, at 11:48 a.m.
        • Performance issue
          TRI TRINH -- Tuesday, 19 April 2011, at 1:56 p.m.
        • Re: Performance issue
          Art Kagel -- Wednesday, 20 April 2011, at 10:04 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.