AMDP take too much time to run when execute program, but when debug in DB it is very quick

[DanBB]

Dear expert !

When I'm using the AMDP, I come up with the issue that make me confuse about the behavior of AMDP. When I call AMDP in execution ABAP program it took so much time to complete , sometimes it throw error SQL code 2048 no memory allocation.
But when I execute in debug mode ( debug run all statements in AMDP ) it just take about 30s to complete.
Is there anyone face this problem before? Please help me find out the reason and solution for this.
I use the AMDP trace to find out which variable take time and realize that : not_common and no_sprctr take time, but while using debugging mode it just took 4s to go through that statement.
@Dear Horst!
I'm a fan of your blogs, I really hope that you could help me out of this issue !

Thanks so much!

Dan

[Sijin_Chandran]

This is really strange and eagerly waiting for a comment on this by ABAP SQL experts.

[DanBB]

Dear horst.keller !
Could you please help me out of this issue? Thanks so much !

[DanBB]

That's could be resolved by using the separated tables instead of using the database view.
the data base ce92442_v_ic is combined of ce92442_h and ce92442_ic. When I select data base on 2 tables, that issue wont happen again

[Sijin_Chandran]

Truly speaking I had lot of expectations ( even evident with the number of upvotes ) with this question and was eagerly waiting for the solution.

Still the mystery remains there why its working fine and quick in debug mode.

Anyways glad you found the solution.

Thanks,

Sijin

[DanBB]

Hi Sijin Chandran!

When I using AMDP trace I found that all sql statement related to this database view will be process in 1 sql ce92442_v_ic. It won't split into separated sqls.
When I change the DB view into it's components, it doesn't happen anymore. I face this issue when i run post closing (there more than 2M lines in ce92442_v_ic sastify the condition). I asked and get the answer that they faced the same situation before:
the more simple sql, the more performance . SO better to split complex sql into simple sql.
I really wonder why this happen (indeed when i trace AMDP in debug mode, i saw that the more sql statements, the more time it takes)
really have no idea @@

[former_member359724]

Dear badan,

Apparently, HANA tries to improve the performance by combining the sql queries for you. And, sometimes, that doesn't work as expected. You might want to use the hint "NO_INLINE" to kindly ask the system to execute your queries as you wrote them (that's what happens when you debug the AMDP method).

Just add "WITH HINT (NO_INLINE)" to your queries and try again.

More info:

https://launchpad.support.sap.com/#/notes/2000002

https://launchpad.support.sap.com/#/notes/2142945

https://blogs.sap.com/2020/11/19/how-to-investigate-if-sql-queries-in-a-procedure-are-inline-or-not/