SubQuery Optimization Difference in 10.x and 11.7

Posted in 2013

Topics: SQL Development & Query Writing, Server Administration

MOHAMMAD — Wed, 13 Mar 2013 10:35:42 — source: IIUG Forums & Mailing Lists

Hi, my customer has a query with subquery in the columns. When I run it on informix 10.x environment, the query runs fast. It took only few seconds to complete. On the contrary when I run it on 11.7 environment, it took forever. From the onconfig setting I Find the setting on: OPTCOMPIND both set to 2. What could be the difference? Thanks.

↪ replying to MOHAMMAD

Art Kagel — Wed, 13 Mar 2013 10:40:16 — source: IIUG Forums & Mailing Lists

Did you run the query under SET EXPLAIN ON in both environments to determine if the query plan is different? Was the database upgraded in-place or rebuilt from scratch in 11.70? Have you updated statistics since the upgrade? If so, did you follow the recommended protocols or use dostats? If the upgrade was in-place did you drop all distributions in the database before running the update statistics commands? 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, Mar 13, 2013 at 6:35 AM, MOHAMMAD IRFAN <irfan199@yahoo.com> wrote: > Hi, my customer has a query with subquery in the columns. When I run it on > informix 10.x environment, the query runs fast. It took only few seconds to > complete. On the contrary when I run it on 11.7 environment, it took > forever. > > >From the onconfig setting I Find the setting on: > OPTCOMPIND both set to 2.> > What could be the difference? Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d042d051cd6474d04d7cc079d

↪ replying to MOHAMMAD

Deillon — Wed, 13 Mar 2013 10:48:26 — source: IIUG Forums & Mailing Lists

Hi, Could you please give us more detials (table info, nb rows, subquery). Have you run the same stats on the table for both IDS versions ? Regards, Samuel -----Message d'origine----- De : ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] De la part de MOHAMMAD IRFAN Envoyé : mercredi 13 mars 2013 11:36 À : ids@iiug.org Objet : SubQuery Optimization Difference in 10.x and 11.7 [29727] Hi, my customer has a query with subquery in the columns. When I run it on informix 10.x environment, the query runs fast. It took only few seconds to complete. On the contrary when I run it on 11.7 environment, it took forever. >From the onconfig setting I Find the setting on: OPTCOMPIND both set to 2. What could be the difference? Thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Art Kagel

MOHAMMAD — Wed, 03 Apr 2013 07:42:28 — source: IIUG Forums & Mailing Lists

Sorry for the late reply. The query gave different result on Explain. What could be different? The 11.7 Query Explained: QUERY: (OPTIMIZATION TIMESTAMP: 03-11-2013 17:55:02) (FIRST_ROWS OPTIMIZATION) ------ select distinct tdate, scode, sname, sono, bono, tno, to_char(etimestamp,'%H:%M:%S%F') as ttime, tref, a.bid, CASE WHEN (a.sid <= 4) then 1 ELSE 2 END as ses, a.pr, a.quantity, a.value, (select pcode from part xy where a.slpartid = xy.partid) as ABS, (select partname from part xy where a.slpartid = xy.partid) as ABDNNsl, sdom, (select pcode from part xy where a.bypartid = xy.partid) as ABby, (select partname from part xy where a.bypartid = xy.partid) as ABDNNby,bdom from tdr a, scy b, part c where a.scyid = b.scyid and (a.bypartid = c.partid or a.slpartid = c.partid) --and a.bypartid <> a.slpartid --uts and pcode = 'IK' --and bid = 'GN' --and scode IN ('COS') and tdate between '2013-03-01' and '2013-03-11' order by 1,2,6 Estimated Cost: 422549 Estimated # of Rows Returned: 29 Temporary Files Required For: Order By 1) informix.b: SEQUENTIAL SCAN 2) informix.a: INDEX PATH Filters: (informix.a.tdate >= 2013-03-01 AND informix.a.tdate <= 2013-03-11 ) (1) Index Name: informix.ak_tdr_sec Index Keys: scyid (Serial, fragments: ALL) Lower Index Filter: informix.a.scyid = informix.b.scyid NESTED LOOP JOIN 3) informix.c: INDEX PATH Filters: informix.c.pcode = 'IK' (1) Index Name: informix. 197_1337 Index Keys: partid (Serial, fragments: ALL) Lower Index Filter: informix.a.bypartid = informix.c.partid (2) Index Name: informix. 197_1337 Index Keys: partid (Serial, fragments: ALL) Lower Index Filter: informix.a.slpartid = informix.c.partid NESTED LOOP JOIN Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.xy: INDEX PATH (1) Index Name: informix. 197_1337 Index Keys: partid (Serial, fragments: ALL) Lower Index Filter: informix.xy.partid = informix.a.slpartid Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.xy: INDEX PATH (1) Index Name: informix. 197_1337 Index Keys: partid (Serial, fragments: ALL) Lower Index Filter: informix.xy.partid = informix.a.slpartid Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.xy: INDEX PATH (1) Index Name: informix. 197_1337 Index Keys: partid (Serial, fragments: ALL) Lower Index Filter: informix.xy.partid = informix.a.bypartid Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.xy: INDEX PATH (1) Index Name: informix. 197_1337 Index Keys: partid (Serial, fragments: ALL) Lower Index Filter: informix.xy.partid = informix.a.bypartid --------------------------------------------------------------- The 10.x Explained: QUERY: ------ select distinct tdate, scode, sname, sono, bono, tno, to_char(etimestamp,'%H:%M:%S%F') as ttime, tref, a.bid, CASE WHEN (a.sid <= 4) then 1 ELSE 2 END as ses, a.pr, a.quantity, a.value, (select pcode from part xy where a.slpartid = xy.partid) as ABS, (select partname from part xy where a.slpartid = xy.partid) as ABDNNsl, sdom, (select pcode from part xy where a.bypartid = xy.partid) as ABby, (select partname from part xy where a.bypartid = xy.partid) as ABDNNby,bdom from tdr a, scy b, part c where a.scyid = b.scyid and (a.bypartid = c.partid or a.slpartid = c.partid) --and a.bypartid <> a.slpartid --uts and pcode = 'IK' --and bid = 'GN' --and scode IN ('COS') and tdate between '2012-03-01' and '2012-03-11' order by 1,2,6 Estimated Cost: 245186 Estimated # of Rows Returned: 2530 Temporary Files Required For: Order By 1) informix.c: INDEX PATH (1) Index Keys: pcode (Serial, fragments: ALL) Lower Index Filter: informix.c.pcode = 'IK' 2) informix.a: INDEX PATH Filters: (informix.a.bypartid = informix.c.partid OR informix.a.slpartid = informix.c.partid ) (1) Index Keys: tdate (Serial, fragments: ALL) Lower Index Filter: informix.a.tdate >= 2012-03-01 Upper Index Filter: informix.a.tdate <= 2012-03-11 NESTED LOOP JOIN 3) informix.b: INDEX PATH (1) Index Keys: scyid (Serial, fragments: ALL) Lower Index Filter: informix.a.scyid = informix.b.scyid NESTED LOOP JOIN

Glossary

onconfig