This is for any of the SAP'ers out there who have access to the Query Hint documentation - can you please share any documents on the topic. The WITH HINT() clause is completely missing from the SELECT reference page - http://help.sap.com/hana/html/sql_select.html.
the DBSL hints are not about any of HANAs numerous query optimizers (yes, there are several of them... ) but about how ABAP OpenSQL statements are transformed before they are send to HANA.
And the OLAP_PARALLEL_AGGREGATION hint (which was mentioned also in the other thread) could be avoided if the query is modelled into an analytic view.
Absolutely, but it's interesting to note you can add the hints yourself to queries made directly on hana (outside the bw landscape). For olap_parallel_aggregation, translated to 'analyze_model' in hana, it does make the query pass through the olap processor rather than being treated as purse sql. If you know your data model and the impacts the processor will have, you can get a fair speedup by using this hint (though you need a fair dataset size to do so)
Mark you're right - under very specific circumstances you can have HANA execute a free-form SQL the way it would execute a modelled query.
Your last sentence however totally backs up my point - because there is no way that anyone could possible tell beforehand how many resources would be used by a given query.
Up to day it's pretty much impossible to explain resource consumption if you are not part of the HANA core developer team.
Concerning the hints "outside of hana" - well, this is pretty simple.
Have a clearly defined database access layer in your application.
Have this db layer analyse the SQL you write and modify it there.
Then you introduce "hints" to influence how the SQL will be transformed.
I agree. The idea of hints as I understood the original question is for when we are using HANA outside a BW landscape - maybe as a "standard" database via say ODBC or JDBC - in this situation it's useful to know which hints exist and what they do.
I understand that query hints in general are "evil", but as the thread http://scn.sap.com/message/13610999#13610999 points out, they are necessary in times when the query optimizer doesn't get the query. At least the folks at Microsoft document their query hints publicly for SQL Server http://msdn.microsoft.com/en-us/library/ms181714(v=sql.110).aspx. They do have a big note at the top with the caution and everyone know this, but forcing developers to make a support call to learn more about the hints makes it impossible for the community to learn and contribute. We are mature people trying to make the best of HANA, but hiding information is not the way to promote a platform.
Yes I do know that other DBMS have loads of hints to "help" the optimizer.
Yet, all of them discourage the productive use of those hints for very well reasons:
hints are a maintenance horror (they might work in one version and not in a later one - and you can't know where you used them.
hints are used the wrong way 99% of the time, not providing the optimizer with better information about the data to work on, but with actual instruction on how to process the query. This is extremely unfortunate as you prevent better optimization of the same query in later versions of the database plus you start letting go THE big advantage of relational databases: that you don't have to care about how the query is executed.
In the mentioned example it's not even clear what exactly caused the worse query run time. Maybe the hint used in that case just worked around the still existing issue. Not a situation anybody would like to have in a production system. Yet, it's a good example for why hints really shouldn't be used as of today (fix by guesswork).
The fact that certain product information about unsupported functions are not disclosed has nothing to do with a view of users not being mature enough to deal with it.
The opposite is just true - rather not to give away stuff, that should not be used because it's not safe to do so or because the feature is simply not ready yet or because the intended usage for the feature simply wasn't meant for outside SAP.
Nobody forces anybody to open support calls to get information. But if your data models aren't performing well enough for no obvious reason, this might be a product bug that needs to be fixed.
Finally, a disclaimer like "use at own risk" never led to a customer that didn't ask for help if things went belly up.
Having worked in database support for Oracle, MaxDB and HANA the last nine years - this is my personal point of view on this topic.
Have the bad performance fixed - not worked around by "black magic" hints and features.
I used to work at Microsoft on the SQL Server team and I fully get your position. In my prior life, I used to present how to tune queries at conferences and I'll admit I used to give the same warning about the evils of using hints - but the HANA engine is still evolving and it will take years to get the optimizer right. The HANA development team can't possibly correct query optimization issues without regressions in a timely manner for customers who have a real problem today. Case in point, HANA on AWS is using rev 36 of the engine with no firm date for getting SPS5. If I have a problem that can only be solved with a hint, hints become a necessary evil. The HANA engine should be smart enough to know when the hint is no longer needed and log the information in a trace/log file so that the developer is educated that the problem is "fixed". The database should also have a session flag that tells the query optimizer to ignore hints as well so that developers can see if a hint is cause more problems that it's worth. Finally, hints should be highlighted in the Visual Explain Plan so that the developer is aware of the "oeverride" they have applied. Ideally the plan should indicate that the hint is not needed as well.
I'm just asking - don't hide useful information - add all the cautions needed - add the tools to highlight when hints go bad - trust your customers to do the right thing.
I'm the last person hiding information. Never did.
Maybe HANA on AWS isn't the right platform to do serious HANA development - I don't know. What I know is that optimizer and query performance issues are always treated top priority from development to be fixed. Meanwhile SPS5 is out (rev. 45) and with that the hint might already be superfluous.
And personally I think building a whole feature infrastructure with warnings and ignore-rules for SQL-hints-hacking that no developer in the world will ever look at again (sorry, just my experience) is definitively the wrong way to go.
Just look at what trouble Oracle is in with the plan management features they created over the years.
I think analyzing why something doesn't work correct and fixing this is the right way. Not throwing an arbitrary set of hints or settings at the engines and observe when it seems to go faster.
That's my 2 ct on that and it looks like we won't agree here ...
No problem - as I stated before - when I was on the SQL Server team - I used to make the same stance . Now that I'm out in the wild and need to get "real" work done, I'll make due and pose any performance issues of queries on HANA taking seconds to the forum for optimization tips and hope that they work their way back into the engine.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.