IC66048: JOIN OF REGULAR TABLE AND VIEW DEFINED WITH SELECT UNION ALL IS SLOWER AFTER IDS 9 TO IDS 11.50 UPGRADE.

APAR status

• Closed as program error.

Error description

• Performance degradation after upgrading from IDS 9.40.FC5XT to
  IDS 11.50.FC5W4 on HP Itanium.
  
  I've provided a test case and also verified the slower
  performance in 11.50.FC6W1 on Itanium.
  
  The query plans are the same from IDS 9 to IDS 11 and the query
  is:
  
  select --+ ORDERED
    unique jobcard.job_no from channel_cust, jobcard, allcusts
    where jobcard.job_no = channel_cust.job_no
    and channel_cust.custcode = allcusts.custcode and  1=1 into
  temp t1 with no log;
  
  I added the ORDERED directive in testing to ensure a simple
  means for generating
  the same query plan across versions.
  
  The view definition is as follows:
  
  create view channel_cust
  (job_no,custcode,cname,sstreet1,sstreet2,scity,state,szip,scount
  ry)
  as
    select x1.job_no ,x0.custcode ,x0.cname ,x0.sstreet1
  ,x0.sstreet2
      ,x0.scity ,x0.sstate ,x0.szip ,x0.scountry from
      allcusts x0 ,jobcard x1 where ((x1.cust_no = x0.cust_no
      ) AND (x1.cacct_no IS NULL ) )  union all select x3.job_no
      ,x2.custcode ,x2.cname ,x2.sstreet1 ,x2.sstreet2 ,x2.scity
      ,x2.sstate ,x2.szip ,x2.scountry from allcusts
      x2 ,jobcard x3 where ((x3.cacct_no = x2.cust_no
      ) AND (x3.cacct_no IS NOT NULL ) ) ;
  
  In the provided repro, I run the query 100x as the timing
  results are
  more obvious with multiple iterations of the query.  The problem
  can be
  observed in the timex output for these runs.  For instance, on
  average, these are the times:
  
  IDS 9.40.FC5XT:  46.61 seconds
  IDS 11.50.FC5W4: 62.45 seconds
  IDS 11.50.FC6W1: 58.58 seconds
  
  The query plans for all versions are alike with the estimated
  cost in the
  IDS 9 explain output being very small compared to that in IDS
  11.5:
  
  
  QUERY:
  ------
  create view "informix".channel_cust
  (job_no,custcode,cname,sstreet1,sstreet2,scity,state,szip,scount
  ry)
  as select x1.job_no ,x0.custcode ,x0.cname ,x0.sstreet1
  ,x0.sstreet2 ,x0.scity ,x0.sstate ,x0.szip ,x0.scountry from
  "informix".allcusts x0 ,"informix".jobcard x1 where ((x1.cust_no
  = x0.cust_no ) AND (x1.cacct_no IS NULL ) )  union all select
  x3.job_no ,x2.custcode ,x2.cname ,x2.sstreet1 ,x2.sstreet2
  ,x2.scity ,x2.sstate ,x2.szip ,x2.scountry from
  "informix".allcusts x2 ,"informix".jobcard x3 where
  ((x3.cacct_no = x2.cust_no ) AND (x3.cacct_no IS NOT NULL ) ) ;
  
  Estimated Cost: 20770
  Estimated # of Rows Returned: 6169
  
    1) informix.jobcard: INDEX PATH
  
      (1) Index Keys: cacct_no   (Serial, fragments: ALL)
          Lower Index Filter: informix.jobcard.cacct_no IS NULL
  
    2) informix.allcusts: INDEX PATH
  
      (1) Index Keys: cust_no   (Serial, fragments: ALL)
          Lower Index Filter: informix.jobcard.cust_no =
  informix.allcusts.cust_no
  NESTED LOOP JOIN
  
  
  Union Query:
  ------------
  
    1) informix.jobcard: SEQUENTIAL SCAN
  
          Filters: informix.jobcard.cacct_no IS NOT NULL
  
    2) informix.allcusts: INDEX PATH
  
      (1) Index Keys: cust_no   (Serial, fragments: ALL)
          Lower Index Filter: informix.jobcard.cacct_no =
  informix.allcusts.cust_no
  NESTED LOOP JOIN
  
  
  QUERY:
  ------
  select --+ ORDERED
  unique jobcard.job_no from channel_cust, jobcard, allcusts
  where jobcard.job_no = channel_cust.job_no
  and channel_cust.custcode = allcusts.custcode and  1=1 into temp
  t1 with no log
  
  DIRECTIVES FOLLOWED:
  ORDERED
  DIRECTIVES NOT FOLLOWED:
  
  Estimated Cost: 4
  Estimated # of Rows Returned: 1
  
    1) (Temp Table For View): SEQUENTIAL SCAN
  
    2) informix.jobcard: INDEX PATH
  
      (1) Index Keys: job_no   (Key-Only)  (Serial, fragments:
  ALL)
          Lower Index Filter: informix.jobcard.job_no = (Temp
  Table For View).job_no
  NESTED LOOP JOIN
  
    3) informix.allcusts: INDEX PATH
  
      (1) Index Keys: custcode type_code   (Key-Only)  (Serial,
  fragments: ALL)
          Lower Index Filter: (Temp Table For View).custcode =
  informix.allcusts.custcode
  NESTED LOOP JOIN
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Performance degradation seen in nested loop join query when  *
  * customer upgraded from IDS 9.40 to IDS 11.50 on HPIA64       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS 11.50.xC8                                     *
  ****************************************************************
  

Problem conclusion

• Protect a structure from being modified while in use.  Fix is
  in11.50.FC7W1.
  

Temporary fix

Comments

• For download information, go to:
  
  http://www-933.ibm.com/support/fixcentral/
  For documentation information, go to
  
  http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSY

     UP