Technical Articles
WHILE n SECONDS – An alternative to WAIT UP TO n SECONDS
Hi!
Well, I’ve try to find any article, blog or help to this and I wasn’t able to find anything so I decide to share it.
I was in this project where I had to load an Excel with about 20.000 rows and each row should create a Purchase Order, a Delivery, split this delivery, do the picking, create the Shipment and create the Nota Fiscal (a Brazilian document created after the invoice).
As usual, if you create or change a document even the BAPI returns ok with a document number it doesn’t mean that the document exists in the database. It took some time to fully end the whole process.
Since I need to create a lot of documents I need a solution that was more performance-friendly as the usual WAIT UP TO n SECONDS after the creation/change of each of those documents.
So, I after few researches I decide do create something like a WHILE command but using TIME as the logical condition.
The basic concept is very simple, something like this:
GET TIME.
CONVERT DATE sy-datum
TIME sy-uzeit
INTO TIME STAMP DATA(l_time_ini)
TIME ZONE sy-zonlo.
GET TIME.
DATA(l_time2) = sy-uzeit + 1. "1 second in the future
CONVERT DATE sy-datum
TIME l_time2
INTO TIME STAMP DATA(l_time_fim)
TIME ZONE sy-zonlo.
WHILE l_time_ini LE l_time_fim.
GET TIME.
CONVERT DATE sy-datum
TIME sy-uzeit
INTO TIME STAMP l_time_ini
TIME ZONE sy-zonlo.
*** Do your thing here
WRITE / sy-index.
*** End your thing here
ENDWHILE.
In this very example the WHILE runs in my development environment something like 120.000 to 180.000 times depending on how loaded is the server.
Well, a very simple SELECT on EKKO (in my example) takes 38ms using a document number that doesn’t exists (yet) and 29ms when the number already exists in the database. So, why I need to wait 1 “endless” second to check if my record was created in the database?
By using this simple technique allows my loop to run only the time I needed until the creation of the record on database was completed, no more no less. This loop is more performance-friendly against the old the performance-killer WAIT UP TO n SECONDS.
The final code is this:
***********************************
GET TIME.
CONVERT DATE sy-datum
TIME sy-uzeit
INTO TIME STAMP DATA(l_time_ini)
TIME ZONE sy-zonlo.
GET TIME.
DATA(l_time2) = sy-uzeit + 10. "wait up to 10 seconds
CONVERT DATE sy-datum
TIME l_time2
INTO TIME STAMP DATA(l_time_fim)
TIME ZONE sy-zonlo.
WHILE l_time_ini LE l_time_fim.
GET TIME.
CONVERT DATE sy-datum TIME sy-uzeit
INTO TIME STAMP l_time_ini TIME ZONE sy-zonlo.
SELECT ebeln, ebelp, matnr
INTO TABLE @DATA(tl_purchase)
FROM ekpo
WHERE ebeln EQ @ebeln_new.
IF tl_purchase[] IS NOT INITIAL.
EXIT.
ENDIF.
ENDWHILE.
***********************************
You can change the time you want to make the loop “waits” and the WHILE will stay in loop only the necessary time until the records get saved in the database, in my case EKPO.
Bigger the amount of data you need to process bigger will be the TIME you will save.
WHILE n SECONDS in practice
Well, after my project finish I could do some tests using the comments you shared in this post and there is the result.
To run this test I used a XLS file with 4758 line from this XLS the following happened:
- 687 Purchase Orders was created using BAPI_PO_CREATE1
- 656 Deliveries was created using BAPI_OUTB_DELIVERY_CREATE_STO
- All deliveries was modified (to update STORAGE LOCATION info) using BAPI_OUTB_DELIVERY_CHANGE
- All deliveries was modified again to split the items by CHARG field using BAPI_OUTB_DELIVERY_CHANGE a second time
- For every delivery a “Z” customer table was update
- And finally the PICKING was made for all of them using WS_DELIVERY_UPDATE function
I created a copy of my program changing my WHILE to a WAIT ‘0.1’ SECONDS in the same WHILE between every single step of this process but I still needed a LOOP to check if the record exists in the corresponding database tables.
UPDATE TO TASK didn’t work with those BAPIs and functions.
*I have to edit the image to fit in the article window width.
As you can see this “method” WHILE n SECONDS have his value and was faster almost an hour (48 minutes to be more precise).
You have to understand that I made this test in QA environment/server and problably this resutls are not too precise.
I hope you enjoy it !!
I know you problably have a better solution for this issue, so please share !!
If you find any problem with this code please correct me !
Thanks
Ivan
WAIT UP TO '0.1' SECONDS. No DB access 🙂
Thank you Mike !!!
‘0.1’ = 100ms, right? The loop and the select will return faster than 0.1s as shown, if I’m right.
And I need to access DB because I will need to use the document I’ve just created to process his subsequents. I need the PO created to create a the delivery and so on…
The help says:
I think ‘0.1’ will not work.
Thanks
Ivan
It accepts decimals since 7.40 SP 8.
Sorry, bit hasty. What I meant was no repetitive DB access in a wait-loop, and no tying up a processor with 100,000's of useless cycles.
As Sandra said, decimals are possible. Just try it, it only takes a few seconds to find out whether your system supports it 🙂
Hi Mike, don't worry !!!
But this WHILE will not run thousand times, just 2 to 4 times and it will always leave the loop after the WAIT '0.1' I assure you.
Usually, BAPI_TRANSACTION_COMMIT called with WAIT = 'X' is sufficient to let the asychronous update task terminate the database updates (or instead of WAIT = 'X' an alternative is a SET UPDATE TASK LOCAL before calling the BAPI, which does the same effect), but sometimes there are additional asynchronous processes (I can't give exact cases) which make a WAIT still useful.
Thank you for your comment Sandra !
I think the problem is to define when you can use WAIT = ‘X’ and when need a wait.
The problem is that the COMMIT WORK AND WAIT does not work a lot of the time.
You would expect it to wait until the database is updated but it does not. That is why you need some sort of loop until you are 100% sure the record is in the database. Otherwise if you create (say) a sales order, do a COMMIT WORK AND WAIT and then try and create a delivery based on that sales order it will not work as the order does not yet exist.
There was a wonderful blog on SCN some years back on why COMMIT WORK AND WAIT doesn't work most of the time, but I cannot remember the exact reason.
Thank you Paul!
Yep, there is this old post where you comment your solution for the problem, I took this post as inspiration, for years I used the command DO n TIMES until I came to this WHILE n SECONDS what I think is really better.
Thanks for the link ! So, the stated issue is that many BAPIs are not written as per recommendations, i.e. they do a COMMIT WORK, and they do it without AND WAIT, so the updates start asynchronously, a 2nd LUW starts implicitly, and when we do a COMMIT WORK AND WAIT it waits for the updates of the 2nd LUW but not for the updates of the 1st LUW.
But one thing I don't understand is why SET UPDATE TASK LOCAL called before the BAPI wouldn't solve the issue, because it adds implicitly AND WAIT to the subsequent COMMIT WORK. The blog post doesn't explain why it doesn't work.
I didn't try but maybe SET UPDATE TASK LOCAL with IN UPDATE TASK works!
I also read a long time ago about DESTINATION 'NONE' and calling BAPI_TRANSACTION_COMMIT wiht DESTINATION 'NONE' and then after this you should call RFC_CONNECTION_CLOSE again with DESTINATION 'NONE'. But I never tried too !!
Thanks Sandra
Thanks Ivan
Another way to solve things like this can be achieved by checking the lock to was set when doing the update and wait till the lock was removed ...
I use the QUEUE functions to lock objects too, usually to process the Deliveries but always inside of this WHILE and always DEQUEUEing because I need to do more things like do the PICKING of the this delivery right after the creation.
Thank you Hugo!
Correct. Before starting your WHILE, you will have to wait until the lock is actually SET (I had situations where the lock was not set yet when I started assuming the update process was allready finished. Test, test and test again ... 🙂
Good Ivan. Thanks for share!!
Your sharing was great help for me. Thank you Ivan!
King regards.
Use Enqueue_Sleep instead - it does not cause a Db Commit, and I seem to remember that it also releases the work process.
Thanks for the hint.
I always try to avoid waiting.
Fortunately sometimes it is possible to use transaction swec and its events to "subscribe" to an event after a document creation for example. But this approach expects an existing change document for a given table.
Good effort. Ages old problem to which there is still no perfect solution. 🙂
Hi guys (and girls) !!
I ran some tests to compare the results of my method and some sugestions posted by you, I hope you enjoy the results.
Hi,
I’ve good experiences (regarding Performance & COMMIT WORK and WAIT) with SET UPDATE TASK LOCAL. Especially for reports with a many BAPI calls or Updatefunction calls.
Regards, Johann
I always try to avoid WAIT at all costs and have done so successfully using SET UPDATE TASK LOCAL before every BAPI call in combination with DESTINATION 'NONE' in order to ensure correct database update sequence, proper execution of programs/reports used when calling BAPIs in a LOOP as well as avoiding locking conflicts. Here some more insights..
Sometimes COMMIT WORK AND WAIT is not enough, as for example described in note 697989 - Objects locked despite synchronous update - SAP for Me
Next, see Local Update | SAP Help Portal
and to ensure calling BAPIs in a loop correctly, see e.g. 1923267 - BAPI_ALM_NOTIF_* - Notification not updated - SAP for Me