Oninit cdi / Archive
Talk to an expert →

SE 5.05UC2 Query-Prob.

Posted in 1999

Topics: Performance & Tuning, Installation, Setup & Upgrades, Connectivity: ESQL/C, 4GL & Embedded SQL, Migration, Import/Export & Data Conversion, Clustering, Grid & MACH11

Andreas Bettin — — source: Informix-list mailing list archive (1991-1998)

Hello! We use SE 5 on several boxes with now DG-Unix 4.0D (formerly 3.2 or 3.0). Database is SE from 5.02 to 5.05UC2 RT and 4GL-RT from 4.10 to 4.12. Now, we upgraded DG-Unix to 4.0D because Y2K. SE was not upgraded. Before upgrading to 4.0D no perf-problems with queries occoured. Now, following query works **very** slow on (only) one box: SELECT p.*, r.txt_warengr, a.atg_art, t.ks_faktor, t.lgrbb_lfs FROM ap_atgp p, a p_art r, ap_atg a, ap_atg_art t WHERE p.fi = "102" AND p.wrk = "662" AND r.art = p.art AND a.fi = p.fi AND a.wrk = p.wrk AND a.atg = p.atg AND t.atg_art = a. atg_art and t.fibu_vorzeichen <> 0 AND p.rech_datum between "15.07.1999" AND "15 .07.1999" AND p.rech is not null ORDER by r.txt_warengr, p.art Sqexplain say's: Estimated Cost: 78 Estimated # of Rows Returned: 2 Temporary Files Required For: Order By 1) root.p: INDEX PATH Filters: root.p.rech IS NOT NULL (1) Index Keys: fi wrk rech_datum Lower Index Filter: (root.p.fi = '102' AND (root.p.wrk = '662' AND root. p.rech_datum >= '15.07.1999' ) ) Upper Index Filter: root.p.rech_datum <= '15.07.1999' 2) root.a: INDEX PATH Filters: root.a.atg = root.p.atg (1) Index Keys: fi wrk kto Lower Index Filter: (root.a.wrk = root.p.wrk AND root.a.fi = root.p.fi ) 3) root.t: INDEX PATH Filters: root.t.fibu_vorzeichen != 0 (1) Index Keys: atg_art Lower Index Filter: root.t.atg_art = root.a.atg_art 4) root.r: INDEX PATH (1) Index Keys: art Lower Index Filter: root.r.art = root.p.art The query needs half an hour, on other boxes 3-4 sec, where estimated cost from sqexplain is 19! Filling grade of the mentioned tables: ap_atgp: 58931 rows (order - position's) ap_art: 110411 rows (article) ap_atg: 19651 rows (order - header's) ap_atg_art: 20 rows (order - types) I've tried to unload/drop/load/index, cluster the index, update statistics, dbexport. I've no Idea what's going wrong. Thanks in advance. Andreas Bettin

↪ replying to Andreas Bettin
Art S. Kagel — — source: Informix-list mailing list archive (1991-1998)

I'd guess you need to upgrade SE to a version certified for DGUX 4.0D and while you are at it get a Y2K compliant version if available. Art S. Kagel Andreas Bettin wrote: > > Hello! > > We use SE 5 on several boxes with now DG-Unix 4.0D (formerly 3.2 or > 3.0). > Database is SE from 5.02 to 5.05UC2 RT and 4GL-RT from 4.10 to 4.12. > Now, we upgraded DG-Unix to 4.0D because Y2K. SE was not upgraded. > > Before upgrading to 4.0D no perf-problems with queries occoured. > Now, following query works **very** slow on (only) one box: > > SELECT p.*, r.txt_warengr, a.atg_art, t.ks_faktor, t.lgrbb_lfs FROM > ap_atgp p, a > p_art r, ap_atg a, ap_atg_art t WHERE p.fi = "102" AND p.wrk = "662" > AND r.art > = p.art AND a.fi = p.fi AND a.wrk = p.wrk AND a.atg = p.atg AND > t.atg_art = a. > atg_art and t.fibu_vorzeichen <> 0 AND p.rech_datum between > "15.07.1999" AND "15 > .07.1999" AND p.rech is not null ORDER by r.txt_warengr, p.art > > Sqexplain say's: > > Estimated Cost: 78 > Estimated # of Rows Returned: 2 > Temporary Files Required For: Order By > > 1) root.p: INDEX PATH > > Filters: root.p.rech IS NOT NULL > > (1) Index Keys: fi wrk rech_datum > Lower Index Filter: (root.p.fi = '102' AND (root.p.wrk = '662' > AND root. > p.rech_datum >= '15.07.1999' ) ) > Upper Index Filter: root.p.rech_datum <= '15.07.1999' > > 2) root.a: INDEX PATH > > Filters: root.a.atg = root.p.atg > > (1) Index Keys: fi wrk kto > Lower Index Filter: (root.a.wrk = root.p.wrk AND root.a.fi = > root.p.fi ) > > 3) root.t: INDEX PATH > > Filters: root.t.fibu_vorzeichen != 0 > > (1) Index Keys: atg_art > Lower Index Filter: root.t.atg_art = root.a.atg_art > > 4) root.r: INDEX PATH > > (1) Index Keys: art > Lower Index Filter: root.r.art = root.p.art > > The query needs half an hour, on other boxes 3-4 sec, where estimated > cost > from sqexplain is 19! > > Filling grade of the mentioned tables: > > ap_atgp: 58931 rows (order - position's) > ap_art: 110411 rows (article) > ap_atg: 19651 rows (order - header's) > ap_atg_art: 20 rows (order - types) > > I've tried to unload/drop/load/index, cluster the index, update > statistics, dbexport. > > I've no Idea what's going wrong. > > Thanks in advance. > Andreas Bettin

↪ replying to Art S. Kagel
Greg Dewinter — — source: Informix-list mailing list archive (1991-1998)

Please refer to the informix web page for actual Y2K compliance and supportablity of SE 5.0x http://www.informix.com/informix/products/year2000/table2.htm Greg A. DeWinter "Art S. Kagel" wrote: > I'd guess you need to upgrade SE to a version certified for DGUX 4.0D > and while you are at it get a Y2K compliant version if available. > > Art S. Kagel > > Andreas Bettin wrote: > > > > Hello! > > > > We use SE 5 on several boxes with now DG-Unix 4.0D (formerly 3.2 or > > 3.0). > > Database is SE from 5.02 to 5.05UC2 RT and 4GL-RT from 4.10 to 4.12. > > Now, we upgraded DG-Unix to 4.0D because Y2K. SE was not upgraded. > > > > Before upgrading to 4.0D no perf-problems with queries occoured. > > Now, following query works **very** slow on (only) one box: > > > > SELECT p.*, r.txt_warengr, a.atg_art, t.ks_faktor, t.lgrbb_lfs FROM > > ap_atgp p, a > > p_art r, ap_atg a, ap_atg_art t WHERE p.fi = "102" AND p.wrk = "662" > > AND r.art > > = p.art AND a.fi = p.fi AND a.wrk = p.wrk AND a.atg = p.atg AND > > t.atg_art = a. > > atg_art and t.fibu_vorzeichen <> 0 AND p.rech_datum between > > "15.07.1999" AND "15 > > .07.1999" AND p.rech is not null ORDER by r.txt_warengr, p.art > > > > Sqexplain say's: > > > > Estimated Cost: 78 > > Estimated # of Rows Returned: 2 > > Temporary Files Required For: Order By > > > > 1) root.p: INDEX PATH > > > > Filters: root.p.rech IS NOT NULL > > > > (1) Index Keys: fi wrk rech_datum > > Lower Index Filter: (root.p.fi = '102' AND (root.p.wrk = '662' > > AND root. > > p.rech_datum >= '15.07.1999' ) ) > > Upper Index Filter: root.p.rech_datum <= '15.07.1999' > > > > 2) root.a: INDEX PATH > > > > Filters: root.a.atg = root.p.atg > > > > (1) Index Keys: fi wrk kto > > Lower Index Filter: (root.a.wrk = root.p.wrk AND root.a.fi = > > root.p.fi ) > > > > 3) root.t: INDEX PATH > > > > Filters: root.t.fibu_vorzeichen != 0 > > > > (1) Index Keys: atg_art > > Lower Index Filter: root.t.atg_art = root.a.atg_art > > > > 4) root.r: INDEX PATH > > > > (1) Index Keys: art > > Lower Index Filter: root.r.art = root.p.art > > > > The query needs half an hour, on other boxes 3-4 sec, where estimated > > cost > > from sqexplain is 19! > > > > Filling grade of the mentioned tables: > > > > ap_atgp: 58931 rows (order - position's) > > ap_art: 110411 rows (article) > > ap_atg: 19651 rows (order - header's) > > ap_atg_art: 20 rows (order - types) > > > > I've tried to unload/drop/load/index, cluster the index, update > > statistics, dbexport. > > > > I've no Idea what's going wrong. > > > > Thanks in advance. > > Andreas Bettin

Glossary