SAP Knowledge Base Article - Preview

2322065 - Much worse query performance after migrating to IQ16.0.

Symptom

It took 2-3 minutes to complete a query in IQ15.4, but it is now taking 1hour plus in IQ16.
 This performance problem happened after migrating IQ 16.0 SP10PL01 from IQ 15.4 ESD1.

 1) According to the query plan in IQ 16.0, most part of the execution time was taken in First Prepare.
     In IQ 15.4, there is no such a big white paper in the query plan.
     
     [IQ 16.0 - A long white paper ]
     Time of Cursor Creation    2016-05-13 14:04:30.554729
     Time of First Prepare    2016-05-13 15:11:56.645021  // occupied the most of the time.
     Time of First Prepare Return  2016-05-13 15:11:56.652375
     Time of First Fetch Call    2016-05-13 15:11:56.653374 
      IQ16.0_WhitePaper.jpg
 
     [IQ 15.4 - No white paper ]
     Time of Cursor Creation    2016-05-16 15:04:37.884858
     End of DFE Generation    2016-05-16 15:04:37.987514
     End of DFO Generation    2016-05-16 15:04:39.549867
     End of DFO Transforms    2016-05-16 15:04:39.551150
     Time of First Prepare    2016-05-16 15:04:39.555174
     Time of First Prepare Return  2016-05-16 15:04:39.561146


     IQ15.4_NoWhitePaper_masking.jpg
  
 2) No performance improvement with many database options related to JOIN or PARALLEL operations.
     Those options are as belows, and they were set individually, not together.
       
     - set temporary option Join_Simplification_Threshold = 8;
     - set temporary option Join_Optimization = Off;
     - set temporary option PUBLIC.DML_options14=8;
     - set temporary option dml_options9=8;
     - set temporary option dml_options6=16777216

 3)  Upgrading to SP10 PL10 wasn't helpful to resolve the performance problem.
      Also, there was no remarkable changes in query plans between SP10 PL01 and SP10 PL10.


Read more...

Environment

SAP IQ 16.0 SP10 PL01
SAP IQ 16.0 SP10 PL10

Product

SAP IQ 16.0

Keywords

SAP IQ, IQ, 16.0, query, performance, query performance, white paper, CR 799138, CR799138,799138 , KBA , BC-SYB-IQ , Sybase IQ , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.