SAP HANA Develpment - SQL Scripting and Modelling

[Former Member]

Hi Team,

I am New to SAP HANA. I have worked as Oracle Developer for 8 years and now moved into SAP HANA.
I want to know why procedures are not preferred in SAP HANA.We have requirement to replace Procedures with views wherever possible. Can you please help to know the reason why Views are preferred over procedures .As we know their are few tasks where programming is invovled can be done only in procedures , so their we cannot use views directly . What approch i should follow for this task

[lbreddemann · Accepted solution]

The reason for why views are generally preferred over procedures is that views in SAP HANA can be efficiently combined (stacked) to provide complex analytic query structures made out of re-usable parts.

With procedures it’s common to see slow-by-slow/row-by-row processing (thanks to Tom Kyle for the expression!) as many developers struggle to take off their “imperative programming hat”. Queries generally are most efficiently processed by DBMS when they use the correct set operations to declare the intended result set and leave it to the DBMS to figure out how to compute it. SAP HANA encourages this by providing set oriented graphical modelling tools.

For the cases, where e.g. multi-result set computations have to be performed, SQLScript provides automatic optimisation and parallel execution of commands, where data consistency allows for that.

Since every procedure invocation does incur a small overhead in runtime, using SQLScript should be left to situations where there is a clear benefit of doing so. If the goal is to get the application as quickly as possible to run on SAP HANA, than that would probably warrant to replicate the stored procedure approach and maybe consider moving to e.g. parameterized views or table functions later on.

[shyam_uthaman]

Hi,

Graphical views are optimized for fetching data.

The HANA engines compute the best possible execution plan (contingent on how well you develop a view) and produce faster query results.

Stored procedures are not used in HANA for querying data. They are only used for data manipulation.

Use Graphical views as a preferred modeling practice. If you have to code (due to a limitation of graphical view), use a Table function.

Regards,

Shyam Uthaman

[lbreddemann]

“Stored procedures are not used in HANA for querying data. They are only used for data manipulation.”

This is not quite correct.

It’s perfectly possible to use SQLScript stored procedures to query data. Just look into the documentation to find examples for that. SQLScript is used especially for query constructs that are difficult or impossible to deliver with graphical views. And yes, in that context using SQLScript in the shape of table functions is the recommended approach.

This, however, is not because SQLScript is inherently bad or procedures are not well optimised. It has more to do with a unified modelling approach and how to consume the SQLScript logic.

[shyam_uthaman]

Hi Lars,

I think I miscommunication here. What I meant was that stored procedures are not recommended to be used in HANA to query data because there are better ways to do so.

Shyam

[Former Member]

Hey , thanks guys for explanations . I am coming back on forum after long time as I was away from systems due to some medical issues. I explored hana in this time and can relate the answers you provided. Appreciating your help to make it clear.