Skip to Content
0

AMDP OR ADBC?

Mar 23, 2017 at 11:21 AM

790

avatar image

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

10 |10000 characters needed characters left characters exceeded

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

0
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Horst Keller
Mar 23, 2017 at 12:28 PM
6

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.
Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

2
Matthew Billingham
Mar 24, 2017 at 07:02 AM
2

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Sean Zhang
Mar 23, 2017 at 11:31 AM
-2

Hi Steve,

I think you're want to compare using DB procedure with AMDP, since ADBC is only the DB connectivity way.

I think there is no much performance difference between DB procedure and AMDP if you're trying to use SQL script in AMDP.

while, in AMDP you have chance to use other language,e.g. R lang. which might be better from performance side.

and furthermore, using AMDP, you will really benefit from lifecycle and calling AMDP in ABAP. because AMDP could be easily consumed by ABAP similar as Class, and the transport is in the same way as ABAP objects.

with latest Netweaver, you could even possible directly debug in AMDP procedure directly, which might be amazing feature.

However, AMDP has to be on HANA up to now. if it's not HANA, then you have to got normal DB procedure.

Best Regards,
Sean

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

0

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

AMDP supports L for SAP internal usage but not R.

1
SHUBHAMOY DEY Mar 26, 2017 at 05:35 AM
-2

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

Share
10 |10000 characters needed characters left characters exceeded
David Antonio Arroyo León Mar 24, 2017 at 07:16 PM
0

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,

Share
10 |10000 characters needed characters left characters exceeded
Wouter Lemaire
Mar 25, 2017 at 07:58 PM
0

AMDP because of syntax checks and auto-completion :)

Kr,

Wouter

Share
10 |10000 characters needed characters left characters exceeded