SAP HANA HINTS

[shyam_uthaman]

Hi All,

Would like to know about your experiences with using HINTS.

When would it help to use them? I've seen people use HINTS to force the query execution to OLAP engine.

I am quite ignorant on the topic. Would love to hear your opinions.

Shyam

[lbreddemann · Accepted solution]

IMO hints in SAP HANA are pretty much what they are in other DBMS.

They're workarounds for situations when the automatic mode for one reason or another doesn't pick the best way to process a query the way it's intended to.

And just as with any other DBMS you need to have very detailed understanding about the various parts that play a role in query processing in SAP HANA. And this - absolutely required - information is not available in any coherent way. In my eyes there's no chance that anybody outside the SAP HANA core development team can accurately recommend a hint to solve a specific issue.

So, what's left is the option to just try out different hints and see whether the query is now processed the way to intend it to.

But let's say you managed to find the set of hints that just works wonders for your queries so you implement them.

That's additional half-imperative code there that you have now (half-imperative since hints might or might not be actually adhered, depending on the prerequisites) and this code needs to be maintained.

DBMS processing changes a lot over time and HANA's track record for that until now is impressive (major changes with basically every SPS release).

Unfortunately, the way that hints are but into place (just as dumb plain-text added to the SQL statement), makes any automated handling very hard.

There's no way to limit the validity of any given hint to HANA software versions, time-box it, or have a central ledger of them, when they are used.

There's no way to add commentary and rationales to the use of hints (except for inline comments) so that some later reader would have an understanding of why the hint was put there in the first place.

Looking at this, I'd say it's obvious that making use of hints immediately adds to your development and maintenance efforts and the payout you get for it is typically not very clear.

TL;DR version

You cannot know what hints to use when - thus it's a trial'n'error method that invokes maintenance hell and typically is used instead of actually understanding the cause of the problem.

Cheers,

Lars

[shyam_uthaman · Accepted solution]

Thank you Lars.

I usually believe in letting HANA do it's thing and not messing with the execution plans.

Was just curious to know if HINTs have some good business case as well.

Your answer was helpful as always.

Cheers.

Shyam