03-23-2017 11:21 AM
Hi Experts,
Our project need to call run-time decided hana stored procedure.
I have two choice, using ADBC or AMDP.
Which one is better on performance measure? And which one is better for dynamic programming?
Can anyone please share some lights on it? Thanks a lot:).
03-23-2017 12:28 PM
Which one is better on performance measure?
There shouldn't be a a difference. Both execute the SQLScript of the DB procedure. Only when an AMDP method is called the first time, the framework has to do some setup work.
And which one is better for dynamic programming?
03-23-2017 12:28 PM
Which one is better on performance measure?
There shouldn't be a a difference. Both execute the SQLScript of the DB procedure. Only when an AMDP method is called the first time, the framework has to do some setup work.
And which one is better for dynamic programming?
03-24-2017 3:06 AM
Hi Keller,
Thanks for sharing the insight.
Actually the exact thing I am doing here is build an API for our team member to deaI with HANA stored procedure. They can use the API to create/drop/call procedures/table types.
I will go with ADBC at first because I learned from my colleague that we cannot call CDS view with input parameter(table function in hana) inside a AMDP. This kind of limitation doesn't exist in pure HANA stored procedure. Since we are generating procedure anyway, seems pure procedure is more powerful over AMDP.
Regarding performance, I already went through your answer in this blog Slow performance in AMDP. For correctness's sake, I will open the parameter IS_LOB to consumer for them to decide if the parameter need LOB handling.
Is there any other point I need to take care of or improve on handling procedure with ADBC?
Regarding dynamic programming, here's the thing, I am quite suffering to build native sql script to create/call stored procedure.
-------------------------------------------------------------------------------------------
#1 For create_procedure method of my API, I need to open an import parameter it_param for consumer to pass in the in parameters of procedure so that I can generate script like below:
CREATE PROCEDURE "XXX"."Test"( <br> in in_1 NVARCHAR ,<br> in in_2 DECIMAL(38,2) ,<br> out out_1 NVARCHAR ,<br> out out_2 DECIMAL(38,2))
......
The problem here is I have to convert ABAP DATA TYPE to HANA DATA TYPE by myself. Referring to the blog How SLT is mapping data types. I have done the job in attachment convert-abap-type-to-hana-type.txt. Could you have a review on that if I made some mistake on the code?CALL "XXX"."Test"( P_TIMESTAMP=>'20170314121212', P_AMOUNT=>1000, outputParams=>?);
You can see here I have two parameters P_TIMESTAMP which is CHAR and P_AMOUNT which is DECIMAL. How Can I fill in the parameter value '20170314121212' and 1000? The are all data reference, could I simply write as below:lv_script = |CALL "XXX"."Test"( P_TIMESTAMP=>{ ir_param1->* }, P_AMOUNT=>{ ir_param2->* }, outputParams=>?);|.
Also I am not sure if I have to add the quote '' to character type.Could you share your thoughts on above questions? Thanks a lot!
BR,Steve
03-24-2017 7:51 AM
"because I learned from my colleague that we cannot call CDS view with input parameter(table function in hana) inside a AMDP"
That would be new to me. Inside an AMDP you are in Native SQLScript as in an ADBC generated procedure. You can call anything you want as long it follows the AMDP rules for the usage of objects of DB schemas.
03-24-2017 4:46 AM
Hi Sean,
Thanks for reply:). Actually our project will be moved to SCP. So pure procedure would be more helpful in this case.
And I cannot write R lang. script in pure hana stored procedure? Most our use cases are OLAP. Is the procedure best choice for us or may be R lang. can do it more efficient?
BR,Steve
03-24-2017 7:02 AM
I use AMDP in most cases. However, I've not worked out how to get the implicit results from a call to a stored procedure using these, so for that I use ADBC. (See here for what I mean https://answers.sap.com/questions/51380/index.html ).
03-24-2017 8:00 AM
Hi Matthew,
I just got what you mean:). This is a good share on how to get implicit result. Although we don't have this kind of case, but thanks for sharing.
03-24-2017 11:12 AM
Hi Steve,
Using R lang would be better if you're really working on statistical business, but I think normal OLAP is not always the case.
at the same time, theoreticly, R shall also be possible be used in pure DB procedure, because, AMDP in the end is also a procedure.
since you're moved to SCP, then pure DB procedure would be one you have to go.
Best Regards,
Sean
03-24-2017 4:27 PM
Just to mention it, R is not supported by AMDP.
AMDP supports L for SAP internal usage but not R.
03-24-2017 7:16 PM
Hi Steve,
More to say which one is better on performance measure or which one is better for dynamic programming, i think, you need to decide based on your experience(working with AMDP or ADBC), because in project you will find scenarios complicated, if you don´t has clear the concepts probably, you will have several issues.
Regards,
03-25-2017 7:58 PM
03-26-2017 6:35 AM
Hi steve, The simple answer for your query is that...Use AMDP rather than ADBC.
Performance would depend on the work you are doing inside the AMDP.
"Actually the exact thing I am doing here is build an API for our team member to deaI with HANA stored procedure. They can use the API to create/drop/call procedures/table types." - Please elaborate the task you are up to
The API would create/drop PROCEDURES ?? I did not get that...:)
03-27-2017 12:10 PM
The topic description is ambiguous. Some clarification here. We are building finance consolidation product for global company consolidate their finance data from sub companies.
For run-time performance's purpose, we want to push logic to HANA. And we need user input to determine the business logic run at run-time. So we want generate AMDP/Pure Procedure, the generate of amdp/procedure is not necessary for comparing, they are both quick.
The topic is regarding using AMDP/ADBC to call the procedure, focusing on below two points:
1. Dynamic programming
2. Performance on calling the procedure