loop and select from TBTCO to get active status of child job is causing performance issue!

[former_member725670]

I have a requirement, where parent job waits for all its child job to finish and only then proceed further to next parent job. So, to get the runtime status of childjob, I am looping all the child job ( it_child) and inside loop calling select from TBTCO, to check if child job has finished. If child job finishes I am deleting that entry from it_child.

Now, here issue is with performance of code, DB is being hot too much times to check runtime status of child job. I request experts to let me know if there is any other way we can get runtime status of child job, instead of putting select from TBTCO inside loop?

Below is the code I am trying:

LOOP AT it_child] ASSIGNING <it_child>.
SELECT status FROM tbtco INTO gv_status UP TO 1 ROWS
WHERE jobname = <it_child>-jobname

AND jobcount = <it_child>-jobcount

AND status = 'F'.
ENDSELECT.
IF sy-subrc = 0.
DELETE it_child[].

ENDIF.
CLEAR gv_status.
ENDLOOP.

Many Thanks,

Sonal

[joltdx]

Well, for one, if you're reading from the table directly you should definitely select the status of all the "child jobs" at once instead of in a loop. Here the FOR ALL ENTRIES would be good. (Just make sure the table is not empty before selection - documentation here if required)

Secondly, please insert a delay of some kind between the selects as to not constantly query the database. How urgent is this really to run? Is it enough to check every 10 seconds? every 5? Please allow at least 1 second between checks...

WAIT UP TO 1 SECONDS.

Are there other ways? Well, there is for instance BP_JOB_STATUS_GET that I've used in the past, but I guess that one might be reading the tables as well.

Depending on the type of jobs and the requirements it might be possible to instead use a workflow although that adds a bit of complexity that I can't say if its worth or not. Or just using other batch job techniques for the scheduling or setting up of the jobs.

[former_member725670]

Hi jorgen_lindqvist41

Thanks for your reply, the doubt here is single time select from TBTCO will just fetch runtime info only for that instance. How will it check for runtime information continuously for child jobs? My intention is to continuously capture runtime info of child jobs and delete from internal table once it finishes. So that when all child jobs are finished further processing can go on.

BP_JOB_STATUS_GET also have select from TBTCO, so again this can't be used.

Regards,

Sonal

[joltdx]

Hi!

Using FOR ALL ENTRIES will let you select multiple lines for all your jobname/jobcounts.

Now, I don't know you exact data, but something like this:

IF lines( it_child ) > 0.
  SELECT jobname, jobcount, status
    FROM tbtco
    FOR ALL ENTRIES IN @it_child
    WHERE jobname  = @it_child-jobname AND
          jobcount = @it_child-jobcount AND
          status   <> 'F'
    INTO CORRESPONDING FIELDS OF TABLE @it_child.
ENDIF.

Please also refer to the documentation i added earlier.

But more importantly, you must add a delay before checking again. Just repeatedly checking the DB will have performance issues no matter how efficient the individual selects are.

[former_member725670]

We can put a WAIT to this select query but again this query remains insdie the loop right?

[joltdx]

Well yes you need some kind of loop, be it a WHILE or DO or something.

The query i wrote with FOR ALL ENTRIES, selects all of those from it_child at once, so no need to loop that table anymore.

When it_child is empty all of the original jobs are in status F. (So you might want to handle failed jobs as well, or you'll have an infinite loop)

Now, before you issue the query again you must WAIT, so in that sense the WAIT is inside the loop yes.

[joltdx]

Hey, please add this instead as a comment to my reply instead of a new answer... That way we can keep the threads clean. I'll explain there what I mean