Solved: WITH HINT is highly needed in SAP CAP

Petr_Plenkov · ‎2022 Feb 08

Dear developers,

Since we started building artifacts using cap framework quite quickly we came to performance issues.

Like for example I've created a large union of multiple events from different sources

entity events as select from source_1 {
'SOURCE_1' as source,
....
} union all select from source_2 { 
'SOURCE_2' as source,
...
}
and so on

as a result I expect that whenever I select from such an entity, union node pruning must happen based on where source = ... condition.

However when we started testing it I've noticed such a behavior:

- if I use hardcoded values to query a view generated from such cds - pruning happens and it only selects data from one of union tables which is matching source filter

- however when handling odata request CAP generates quite generic statement

where source = ? and... and calling it with parameters.

Unfortunately for such a plan the behavior in our current Hana revision ( 2.0 rev56 ) is different and ESX engine starts pulling all the tables from the union. It dramatically increases memory and CPU consumtion, api's work poorly

So we have found a note pretty much close to the issue we face: https://launchpad.support.sap.com/#/notes/3133887

Of course there is a patch ( no fact that it will help ) but there is also immediate workaround by applying hints to the view.

So hint like NO_ESX_UNION_ALL works as expected however we cannot inject it to CDS/generated hdbview anyhow.

From our practice we have many more examples when hints were needed for some of our views therefore it would be nice if we could use them in CAP as well.

Please confirm if you have such a feature in your backlog.

Thank you!

TobiT · ‎2023 Jun 28

For everybody landing on this page, this is now possible with CAP: https://cap.cloud.sap/docs/java/query-execution#hana-hints

Petr_Plenkov · ‎2022 Feb 17

johannesvogel so far here is my implementation of this feature:

odata.js

const cds = require("@sap/cds");
const enableHints = require("./pulgins/enable_hints");

cds
  .connect("db")
  .then(({ db }) =>
    db?.before("READ", (req) => enableHints(req)      
    )
  );

module.exports = class odata {};

and enable_hints.js

const SelectBuilder = require("@sap/cds/libx/_runtime/db/sql-builder/SelectBuilder");
const { build } = SelectBuilder.prototype;

// with hint support
Object.assign(SelectBuilder.prototype, {
  build() {
    const result = build.apply(this, arguments);

    // to support also expands we will proxy all found hints to the end of the query
    const hintsRegex = /(?<=WITH HINT\().*?(?=\))/g;
    const foundHints = [result.sql.match(hintsRegex)]
      .flat()
      .filter((hints) => hints)
      .flatMap((hints) => hints.split(","));
    if (!this._obj.SELECT.withHint) {
      this._obj.SELECT.withHint = foundHints;
      // remove current hints to avoid duplicate definition
      result.sql = result.sql.replace(/(WITH HINT\().*?(\))/g, "");
    }

    if (
      Array.isArray(this._obj.SELECT.withHint) &&
      this._obj.SELECT.withHint.length
    ) {
      this._withHint(result);
    }

    return result;
  },
  _withHint(result) {
    result.sql = result.sql.concat(
      ` WITH HINT(${this._obj.SELECT.withHint.join(",")})`
    );
  },
});

// query handler
module.exports = function enableHints({ target, query }) {
  const withHint = target["@Consumption.dbHints"];
  Array.isArray(withHint) && Object.assign(query.SELECT, { withHint });
};

I know it's kind of a hacky way to do this, but it works as a concept just perfectly. So now we are able to have a model like this

and it injects this hint not only to direct selects but also to quite deep navigations. So I've solved some issue which I've been trying to solve during couple weeks 😃

WITH HINT is highly needed in SAP CAP

Know the answer?

Need more details?

Accepted Solutions (1)

Accepted Solutions (1)

Answers (1)

Answers (1)