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
[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
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.
SAP IQ 16.0 SP10 PL01
SAP IQ 16.0 SP10 PL10
SAP IQ, IQ, 16.0, query, performance, query performance, white paper, CR 799138, CR799138,799138 , KBA , BC-SYB-IQ , Sybase IQ , Problem
About this pageThis is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).
Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.