SubQuery Optimization Difference in 10.x and 11.7

Sorry, this is an old question but I would like to ask again. 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. 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

The difference may be in the details of the data distributions on the two servers. Part of it may be the difference between 10 and 11.70 which permits 11.70 to use more than one index for a single table in a query. I'm putting my $$ on data distributions. 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 3, 2013 at 8:19 PM, MOHAMMAD IRFAN <irfan199@yahoo.com> wrote: > Sorry, this is an old question but I would like to ask again. > > 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. > > 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 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec554d23212970e04d97e91b3

So what to do to achieve the same performance? Lots of existing query using subquery. It took time to convert it to join friendly query. Thanks.

Hard to say in a vacuum, Mohammed. I would want to get hands-on to really figure that out. You can try using my dostats utility to update the data distributions on the new server and see if that fixes the problem. You can try enabling view folding in the ONCONFIG file and see if that technology helps. You could try using optimizer directives to restore the older behavior. Could there be an index missing that 10.00 could not have taken advantage of anyway but which 11.70 can use to make the query even faster? 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 3, 2013 at 9:17 PM, MOHAMMAD IRFAN <irfan199@yahoo.com> wrote: > So what to do to achieve the same performance? Lots of existing query using > subquery. It took time to convert it to join friendly query. > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f23465990e99204d97eee92