Performance issue

Posted in 2011

Summary

After upgrading IDS 10.10.FC6 to 11.50.FC8 on HP-UX, a large seven-table ODBC query with nested LEFT OUTER JOINs ran much slower. Advice given: rebuild statistics (drop old 10.x distributions with 'update statistics low drop distributions', then rebuild, combining HIGH statements and recompiling procedures), move the t_comp/t_osta filters from WHERE into the ON clauses, try SET OPTIMIZATION LOW, and compare SET EXPLAIN output. Moving filters helped only slightly. Comparing plans showed test used an AUTOINDEX on tp021.t_item while production did an index self-join on tipcs0211041abaan; Art Kagel asked whether test had an extra index on t_item. No resolution is recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Performance & Tuning, Installation, Setup & Upgrades, SQL Development & Query Writing, Connectivity: ODBC / JDBC / .NET, Platform-Specific Issues

TRI TRINH — Wed, 13 Apr 2011 15:33:35 — source: IIUG Forums & Mailing Lists

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

↪ replying to TRI TRINH

Alexandre Marini — Wed, 13 Apr 2011 15:42:59 — source: IIUG Forums & Mailing Lists

Hello. Just a tip: had you rebuild your statistics??? (low+drop, medium and high with default columns)??? Have you followed the machine notes specifications, for onconfig and environment variables???? Em 13/04/2011 11:33, TRI TRINH escreveu: > 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. > > -- Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 <http://www.iiug.org/conf/2011/iiug/>;

↪ replying to Alexandre Marini

TRI TRINH — Wed, 13 Apr 2011 15:51:04 — source: IIUG Forums & Mailing Lists

Hi, Below is one of the update stat script and it's dbschema. Please take a look and let me know if I can improve it. Thanks update statistics for table "baan".ttirou002104; update statistics medium for table "baan".ttirou002104 distributions only; update statistics high for table "baan".ttirou002104(t_mcno); update statistics high for table "baan".ttirou002104(t_cwoc); update statistics for table "baan".ttirou002104(t_mcno); update statistics for table "baan".ttirou002104(t_cwoc,t_mcno); { TABLE "baan".ttirou002104 row size = 83 number of columns = 9 index size = 25 } create table "baan".ttirou002104 ( t_mcno char(6) not null , t_dsca char(50) not null , t_cwoc char(3) not null , t_mcrt smallfloat not null , t_mccp smallfloat not null , t_mdcp smallfloat not null , t_mmrt smallfloat not null , t_refcntd integer not null , t_refcntu integer not null ) in table1042 extent size 24 next size 8 lock mode row; revoke all on "baan".ttirou002104 from "public" as "baan"; create unique index "baan".tirou0021041abaan on "baan".ttirou002104 (t_mcno) using btree in index1; create unique index "baan".tirou0021042abaan on "baan".ttirou002104 (t_cwoc,t_mcno) using btree in index1;

↪ replying to TRI TRINH

Art Kagel — Wed, 13 Apr 2011 16:02:31 — source: IIUG Forums & Mailing Lists

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

↪ replying to TRI TRINH

Art Kagel — Wed, 13 Apr 2011 16:14:39 — source: IIUG Forums & Mailing Lists

Eliminate the first statement, it does not gain you anything useful. Combine the two HIGH statements into one, it will run in nearly half the time and generate the identical stats. If you have not done so since the upgrade, you should run: update statistics low drop distributions; which will remove all of the data distributions that were built under 11.10. After that, run your normal suite of commands (or adjusted as suggested above). After that you should recompile any stored procedures that depend on your tables: update statistics for procedure; 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:51 AM, TRI TRINH <tri_trinh@hotmail.com> wrote: > Hi, > Below is one of the update stat script and it's dbschema. Please take a > look > and let me know if I can improve it. Thanks > > update statistics for table "baan".ttirou002104; > update statistics medium for table "baan".ttirou002104 distributions only; > update statistics high for table "baan".ttirou002104(t_mcno); > update statistics high for table "baan".ttirou002104(t_cwoc); > update statistics for table "baan".ttirou002104(t_mcno); > update statistics for table "baan".ttirou002104(t_cwoc,t_mcno);> > { TABLE "baan".ttirou002104 row size = 83 number of columns = 9 index size > = > 25 } > create table "baan".ttirou002104 > ( > > t_mcno char(6) not null , > > t_dsca char(50) not null , > > t_cwoc char(3) not null , > > t_mcrt smallfloat not null , > > t_mccp smallfloat not null , > > t_mdcp smallfloat not null , > > t_mmrt smallfloat not null , > > t_refcntd integer not null , > > t_refcntu integer not null > ) in table1042 extent size 24 next size 8 lock mode row; > > revoke all on "baan".ttirou002104 from "public" as "baan";> > create unique index "baan".tirou0021041abaan on "baan".ttirou002104 > > (t_mcno) using btree in index1; > create unique index "baan".tirou0021042abaan on "baan".ttirou002104 > > (t_cwoc,t_mcno) using btree in index1; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54859be72dbdc04a0cf1a7c

↪ replying to Art Kagel

TRI TRINH — Wed, 13 Apr 2011 20:21:00 — source: IIUG Forums & Mailing Lists

Hi, I tried your query and it has a bit improve, but still not normal speed. I will update statistic for the whole database with your advise, then run the query again. I will date. Thank you all for your advices. update statistics low drop distributions;.... .... update statistics for procedure;

↪ replying to TRI TRINH

TRI TRINH — Tue, 19 Apr 2011 15:48:04 — source: IIUG Forums & Mailing Lists

Hi, I do the "set explain" on the same query and run it against production and test database. It ran much faster in test database, and our test database is the restore of production with less CPU and memory. I diff on the explain outputs and here is the differences. How do we get that AUTONDEX PATH in test? < 4) informix.tp021: AUTOINDEX PATH --- > 4) informix.tp021: INDEX PATH 34,36c33,35 < (1) Index Name: (Auto Index) < Index Keys: t_item < Lower Index Filter: informix.tf001.t_mitm = informix.tp021.t_item --- > (1) Index Name: baan.tipcs0211041abaan > Index Keys: t_cprj t_item (Serial, fragments: ALL) > Index Self Join Keys (t_cprj ) 37a37,38 > Lower Index Filter: informix.tp021.t_cprj = informix.tp021.t_cprj AND informix.tf001.t_mitm = info

↪ replying to TRI TRINH

TRI TRINH — Tue, 19 Apr 2011 15:55:43 — source: IIUG Forums & Mailing Lists

The same query test map 7 tables and production map only 6 TEST Table map : ---------------------------- Internal name Table name ---------------------------- t1 tf010 t2 tf001 t3 ti001 t4 tp021 t5 tp021 t6 tr001 t7 tr002 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t4 269174 269174 269174 00:01.13 4 ============================================================================= PRODUCTION Table map : ---------------------------- Internal name Table name ---------------------------- t1 tf010 t2 tf001 t3 ti001 t4 tp021 t5 tr001 t6 tr002 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t4 1458 269676 729 12:15.89 46749

↪ replying to TRI TRINH

TRI TRINH — Tue, 19 Apr 2011 17:56:09 — source: IIUG Forums & Mailing Lists

↪ replying to TRI TRINH

Art Kagel — Thu, 21 Apr 2011 02:04:13 — source: IIUG Forums & Mailing Lists

Is it possible that on "test" there is already an index on t_item and that is why the query runs faster there? 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 Tue, Apr 19, 2011 at 11:48 AM, TRI TRINH <tri_trinh@hotmail.com> wrote: > Hi, > > I do the "set explain" on the same query and run it against production and > test database. It ran much faster in test database, and our test database > is > the restore of production with less CPU and memory. I diff on the explain > outputs and here is the differences. How do we get that AUTONDEX PATH in > test? > > < 4) informix.tp021: AUTOINDEX PATH > --- > > 4) informix.tp021: INDEX PATH > 34,36c33,35 > < (1) Index Name: (Auto Index) > < Index Keys: t_item > < Lower Index Filter: informix.tf001.t_mitm = informix.tp021.t_item > --- > > (1) Index Name: baan.tipcs0211041abaan > > Index Keys: t_cprj t_item (Serial, fragments: ALL) > > Index Self Join Keys (t_cprj ) > 37a37,38 > > Lower Index Filter: informix.tp021.t_cprj = informix.tp021.t_cprj AND > informix.tf001.t_mitm = info > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf30549f69aae41704a1642732

Glossary

IDS (Informix Dynamic Server)