topic Re: select with loop in Application Development and Automation Discussions

select with loop

umayaraj — Thu, 07 Jan 2021 18:39:06 GMT

hi,

i have performance issue with below code

LOOP AT it_dd02l ASSIGNING FIELD-SYMBOL(<wa_dd02l>).
    SELECT COUNT(*) FROM (<wa_dd02l>-tabname) INTO count_temp.
...
ENDLOOP.

My Requirement is find All traction table with total no of records .

any one have better solution for above code

Re: select with loop

Patrick_vN — Thu, 07 Jan 2021 19:12:09 GMT

I'm not sure you can improve the SQL statement. An option would be to foresee some parallelization, and thus run x SQL statements at a time (by using an RFC that returns the amount of documents of a certain (number of) table(s)), That way when one batch/RFC is done, you can trigger another etc.

example 'pseudo' code:

  CALL FUNCTION '[Your Function]'
    STARTING NEW TASK [taskname]
    DESTINATION IN GROUP [server_group]
    PERFORMING [Retrieve Info Form] ON END OF TASK
    EXPORTING
      ..

Re: select with loop

Sandra_Rossi — Thu, 07 Jan 2021 23:08:08 GMT

It takes a while to count the number of records of each table (especially big ones). It used to be counted approximately on Oracle database via the statistics, to determine the best execution plans, for performance purpose. What is your database software? Do you really need to have an exact count rather than an approximate one? Can't you use other measurement means like number of blocks to know the size on disk? (it may count unused space and deleted records)

Re: select with loop

umayaraj — Fri, 08 Jan 2021 18:46:17 GMT

Hi sandra,

DataBase: Oracle

id Native SQL Not Recommended to run Production system right?

and Native SQL DB Statics Give All Clients information We can't to split Client based Info.

Re: select with loop

Sandra_Rossi — Fri, 08 Jan 2021 19:14:20 GMT

If you can't make it work "normally", you have to find an "abnormal" solution, it's not forbidden to write native SQL. By the way, why do you want to create an ABAP program for a technical database feature?

You're right about client. Do you really have a multi-client system?

I think you may find better answers in an Oracle forum. Or go for Patrick solution to parallelize the count (you may also easily parallelize Oracle SQL).