Can a BW Query reuse the execution plan storen in HANA SQL Cache?

[Former Member]

Dear all,

I would like to ask, whether a BW Query executed on HANA DB can use the HANA SQL Cache.

Example. BW 7.5 on HANA DB. BW executes a BW Query. If the BW Query was executed already a couple minutes ago and the data did not change (there was no load into master data, not Info Providers) is HANA able to reuse an execution plan already stored in the SQL Plan Cache?

Thanks,

Martin

[lbreddemann · Accepted solution]

Yes, BW 7.5 on HANA can and does use the SQL plan cache in HANA.

The plan cache entry is independent from the data in the tables, so even if data is updated, the same query plan can be reused - for the same query that is. Note, that in some circumstances the same BW query leads to different DB level queries (e.g. when temporary tables had been re-created). From BW point of view these queries are identical, but on DB (HANA) level they are actually accessing different objects.

There can be several reason for why a query plan cannot be reused or shared across multiple users/sessions and without a specific example it is not possible to really say more than that.

One "specialty" of BW on HANA is that it can use an internal query interface to HANA (TREXviaDBSL) that allows a more direct transfer of the BW FEMS structure to HANA (see also here) . For this (and for BW on HANA in general) there exist several configuration options to adjust performance related behavior.

I recommend starting with SAP note 2000002 - FAQ: SAP HANA SQL Optimization, 10. What are typical approaches to tune expensive SQL statements? --> Long BW query runtime and check the BW specific SAP notes mentioned in this section.