SAP Knowledge Base Article - Preview

2953695 - After upgrading from SAP IQ 15.4 to 16.0, there was a significant performance degradation of complex query. SAP IQ

Symptom

After upgrading from SAP IQ 15.4 to 16.0, there was a significant performance degradation of complex query.
After upgrade, same query runs differently with REVERT_TO_V15_OPTIMIZER database option setting.
Html plans clearly prove that IQ 16 optimizer made bad choices than IQ 15 optimizer.

Following was observed between 2 html query plans with and without REVERT_TO_V15_OPTIMIZER option.

- All plans generate 0 rows finally, yet IQ 16 optimizer is very slow as compared with IQ 15 optimizer.
- All 16 optimizer plans have Nested Loop and order by nodes which clearly took lot of time.
- Nested loop node seem to be overworking in estimating rows. 
- All join preference plans seems to show same nested loop behavior.
- One of the Nested loop nodeswith large table, IQ 15 optimizer estimated rows better in IQ 16 software than IQ 16 optimizer itself.


Read more...

Environment

IQ 16.0 SP11 PL20+

Product

SAP IQ 16.0 ; SAP IQ 16.1

Keywords

performance , REVERT_TO_V15_OPTIMIZER , "Nested Loop" , KBA , BC-SYB-IQ , Sybase IQ , Bug Filed

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.