Make query hints great again…

In case you ever got frustrated by how 80’s like the SAP HANA hints infrastructure looks like, go and support my improvement idea on SAP Idea place: https://ideas.sap.com/D40094  SAP Customer Influence site: Make query hints great again

Go and upvote! Now! 🙂

[Update 23.1.2017]: After some of the folks that I really liked to review my idea reported that they don’t have access to it, I decided to put the idea text as a copy in here as well.
So here it is:

Currently (HANA 1 SPS12/HANA 2 SP0), SAP HANA supports the usage of query hints via information view parameters or – most commonly used – the WITH HINT query parameter.

Most of the times, the hints are used to avoid a specific bug in SAP HANA and are only applicable to a specific version/revision or application state.

For all practical matters, the hints don’t serve a functional purpose for any application but are necessary band-aids to enable the application to run successfully on SAP HANA.

As many bugs do get fixed with later SPS/revisions, the hints could and should be reviewed in order to allow the application leverage the formerly disabled feature again.

Right now, this means, that a developer/system operator has to manually document and keep track of all hints used in the system, the reason for the usage, the HANA revision the hint applies, maybe a incident numbers where the hint usage had been discussed and probably a target HANA version when the hint shouldn’t be required anymore.

This task is obviously not delivering a lot of value for any developer, so in reality, a hint, once introduced, will likely stay in place forever, as nobody remembers what it was used for and if it’s safe to remove it now.

The proposal is as follows:

Instead of a simple WITH HINT syntax, allow for a more comprehensive hint management that allows for identifying a specific statement (e.g. provide a custom statement identifier in a statement annotation or comment) and provide HANA function to specify a set of hints for any statement ID, including the already mentioned metadata.

Upon SQL query compilation SAP HANA would automatically match the statement ID against the maintained statement hints (this could be a single table structure) and include the hint into the statement execution.

The fact that this happened (and against which statement id hint setting this match had been performed) should be visible in the usual query execution analysis tools like EXPLAIN PLAN or PLANVIZ.

The proposed solution has the following benefits:

Application code does not need to be modified to add hints, as long as the statements can be uniquely identified.

The hint application can be controlled centrally, without the involvement of the original developer – essentially the hint can be applied

by

• operations/support staff
• The query compiler in HANA can automatically check for the valid HANA versions and ignore the hint for non-matching versions
• The hints for a given installation can be transported like a configuration data set across multiple servers
• The central list of statements with hints settings allows an efficient follow-up of HANA issues and – given an option to ignore the hint list during plan compilation – also allows for very efficient testing of whether a hint is still required or not.

I haven’t found an obvious way to contact support for the SAP Ideas Place, so my best bet is that in order to view any idea a registration is required.

• Lars