topic Re: AMDP OR ADBC? in Application Development and Automation Discussions

AMDP OR ADBC?

Former Member — Thu, 23 Mar 2017 11:21:26 GMT

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:).

Re: AMDP OR ADBC?

retired_member — Thu, 23 Mar 2017 12:28:36 GMT

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?

ADBC is purely dynamic Native SQL. You pass arbitrary SQL statements as strings to ADBC objects.
AMDP wraps DB procedure calls in ABAP Objects classes. The classes and with it their procedure implementations are defined at compile time. For object instantiation and calling AMDP methods you have the same dynamic possiblities as for normal classes.

Re: AMDP OR ADBC?

Former Member — Fri, 24 Mar 2017 03:06:05 GMT

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?
-------------------------------------------------------------------------------------------
#2 For call_procedure(scalar parameter only case) method of my API, like create, I also need in prameters with abap type, but I don't want to bother consumer to pass the out parameters. I will return an internal table out with all of scalar output parameter. Can I achieve that? Seems it's not possible unless I search parameters from "SYS"."PROCEDURE_PARAMETERS" to get all out parameter with HANA DATA TYPE and then convert HANA DATA TYPE to ABAP DATA TYPE by myself. I am not sure if this is the right way, could you share some thoughts on that?
--------------------------------------------------------------------------------------------
#3 For call_procedure_tabular_out(one tabular out parameter case) method of my API. I need to build the query string by myself such like below:

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

Re: AMDP OR ADBC?

Former Member — Fri, 24 Mar 2017 04:46:14 GMT

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

Re: AMDP OR ADBC?

matt — Fri, 24 Mar 2017 07:02:22 GMT

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 ).

Re: AMDP OR ADBC?

retired_member — Fri, 24 Mar 2017 07:51:38 GMT

"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.

Re: AMDP OR ADBC?

Former Member — Fri, 24 Mar 2017 08:00:35 GMT

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.

Re: AMDP OR ADBC?

Sean_Zhang — Fri, 24 Mar 2017 11:12:20 GMT

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

Re: AMDP OR ADBC?

retired_member — Fri, 24 Mar 2017 16:27:29 GMT

Just to mention it, R is not supported by AMDP.

AMDP supports L for SAP internal usage but not R.

Re: AMDP OR ADBC?

former_member235395 — Fri, 24 Mar 2017 19:16:18 GMT

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,

Re: AMDP OR ADBC?

WouterLemaire — Sat, 25 Mar 2017 19:58:43 GMT

AMDP because of syntax checks and auto-completion 🙂

Kr,

Wouter

Re: AMDP OR ADBC?

Former Member — Sun, 26 Mar 2017 05:35:09 GMT

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...:)

Re: AMDP OR ADBC?

Former Member — Mon, 27 Mar 2017 11:10:06 GMT

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