Hi folks,
This is a cut and paste from a previous discussion (http://scn.sap.com/thread/3299421). It highlights a few rules of thumb to keep in mind as you work on optimizing your views/procedures/queries on HANA. Enjoy!
Cheers,
Jody
1) Use generated columns (on table) in place of calculated attributes/measures *when possible/appropriate* in HANA views. Reason: eases computation in the Calculation Engine by storing calculated results and may avoid data result transfer from Join/OLAP Engine to Calculation Engine.
2) Avoid filtering on calculated attributes when possible. Preference is to filter on regular attributes. Reason: Entire view is materialized and then results are filtered, since HANA needs to know the values of all calculated fields before it can filter them out.
3) Leverage column engine SQL operators whenever possible over row engine operators (these are described in the HANA SQL guide). Explain plan / visual plan of a query will detail which operations run in which engine.
4) If you need measures from two fact tables, combine them using 'union with constant values' rather than a join.
5) Avoid cursors and imperative logic in general in stored procedures whenever possible.
6) Instead of using OR in a WHERE clause, SELECT with one filter condition, SELECT with the other filter condition and then combine the query results using UNION ALL (a row store operation but can still be faster).
7) Don't mix CE functions and SQL syntax in a stored procedure. Stick with one or the other so that CalcEngine can optimize in the best manner possible (with big preference given to CE functions).
😎 If different branches of data flow graph stem from the same source, create multiple instances of the same source. So, if you're trying to combine sales data from last year and this year, and you have two projection nodes coming from one analytic view in your calculation view, each with a filter on year - instead, include two analytic view nodes with respective projections (and then a union node, etc). This can also be done in a scripted view but sometimes harder to notice.
Now to be clear, these are all pretty much 'rules of thumb' which should be measured against other approaches - hence 'trial and error'. In particular I've seen the following on projects:
1) Instances where OR clause leads to faster processing than the UNION ALL approach described above.
2) I've always found that if SQL syntax is required in a stored proc / scripted CalcView, it's still faster to include CE functions wherever possible (despite potential optimization problems).
3) I've seen results that show better peformance in scripted calculation views than an equivalent analytic view. Again, this is the exception rather than the rule - encouraging developers to try multiple approaches.
So, my best advice is to try multiple approaches, leverage explain plans and performance tracing, and iteratively optimize your code once functionality is validated. As for where to find best practices, in-depth information - my experience has been that it comes from multiple sources including official HANA documentation and contributor publications.
I hope that helps a bit!
Cheers,
Jody
