IT17944: SELECT FROM VIEW AND SUBQUERY RUNS MUCH SLOWER THAN A SELECT FROM THE UNDERLYING TABLES

APAR status

• Closed as program error.

Error description

• When selecting from views, the query running time can take about
  15 times slower than selecting directly from the underlying
  tables.
  
  For example:
  
  SELECT ...
  FROM
  	SELECT ...
  	FROM ( View1
  		LEFT OUTERJOIN (
  			SELECT * FROM View2
  			INNER JOIN
  			SELECT * FROM View3
  		)
  	FULL OUTTER JOIN
  		SELECT ... FROM View2
  
  The above query takes 34 mins 10 secs to return the result.
  If this query uses underlying tables in the subquery instead of
  views, it takes 2 minutes 35 seconds to run.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 12.10.xC1 through 12.10.xC8.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query may have poor performance when Full-Outer-Join is    *
  * used, especially when one side of the FOJ is a very large    *
  * table.                                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IBM Informix Server 12.10.xC9.                     *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IBM Informix Server 12.10.xC9.
  

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