IC96795: POOR PERFORMANCE FROM A QUERY THAT INVOLVES VIEWS AND CORRELATED SUBQUERIES

APAR status

• Closed as program error.

Error description

• A query consisting of regular tables, views and one or more
  correlated subqueries is performing poorly or possibly hanging.
  Taking the correlated subquery(ies) out, allows the query to run
  to completion in a reasonable amount of time.
  
  The query plan looks like this while it is running:
  
  Estimated Cost: 4654669
  Estimated # of Rows Returned: 84425
  
    1) (Temp Table For View): SEQUENTIAL SCAN
  
    2) informix.c: INDEX PATH
  
      (1) Index Name: informix.ps_person
          Index Keys: emplid  (Serial, fragments: ALL)
          Lower Index Filter: (Temp Table For View).emplid =
  informix.c.emplid
  NESTED LOOP JOIN
  
    3) (Temp Table For View): AUTOINDEX PATH
  
          Filters: <text removed>
  
      (1) Index Name: (Auto Index)
          Index Keys: emplid
          Lower Index Filter: (Temp Table For View).emplid =
  informix.c.emplid
  NESTED LOOP JOIN
  
  
  The stack while running looks something like this:
  
  readidx_new
  gettupl
  scan_next
  inner_next
  join_next
  join_next
  join_next
  join_next
  sort_open
  filltemp
  scan_open
  materialize_viewtmp
  materialize_viewtmp
  prepselec
  subqprep
  exsubq
  ev_cb
  new_eval
  gettupl
  scan_next
  filltemp
  scan_open
  materialize_viewtmp
  <repeated many times>
  prepselect
  open_cursor
  sql_open
  sq_open
  sqmain
  listen_verify
  spawn_thread
  th_init_initgls
  startup
  idle_processor
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All 12.10 users.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query may have bad performance under the following         *
  * conditions:                                                  *
  * - Main query has one or more correlated subqueries, and a    *
  * correlated subquery contains aggregation in projection       *
  * clause                                                       *
  * - the correlated subquery is in the form of "col <op>        *
  * (<subquery>)", i.e. no ANY/ALL/EXISTS/NOT EXISTS keywords in *
  * front of subquery                                            *
  * - the correlated subquery contains at least one view or      *
  * derived table reference, and the view or derived table is    *
  * complex and needs to be materialized into temp table at run  *
  * time                                                         *
  * - the column referenced in the correlated subquery predicate *
  * has many rows and thus the correlated subquery needs to be   *
  * executed many times                                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IDS-12.10.xC3                                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IDS-12.10.xC3
  

Temporary fix

Comments

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

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels

• RC10 PSN

     UP