How  to use Light Processes with multithread to upload heavy excel sheet?
Question

Hi there,

   I am new to Outsystems Light Processes. I have been asked to do Batch Processing(Reading large amount of  data from execl sheet (>=1M Records) and save the data to DB), but I have no Idea how to do that

mvp_badge
MVP

Hi Suheib,

For batch processing, use a Timer, not BPT (let alone LBPT). LBPT is meant when you have very lightweight processing for a lot of data. But for that, the data first needs to be in the database. So reading and uploading the data from an Excel sheet into the database cannot be done with (L)BPT.


Thank you for your answer

I have tried to use a Timer, here 

But I Can't upload excel sheet have >100K Records.

So, Do you have any idea to solve this problem?

Best Regards.

mvp_badge
MVP

Hi Suheib,

I think as a developer you can relate when I say that "I can't" is not the most comprehensive description of what goes wrong :).

Also, looking at that code, you are not uploading an Excel sheet, as the sheets seem already present in the database.

If you get a time-out on the Timer, you should increase the Timer's time-out value. Standard it's 20 minutes, but you could set it to 3 hours or more. If that also fails, there's various strategies you could take. For example, be sure to commit every 1,000 to 10,000 records or so (depending on how fast your processing is), and if the timer restarts, check what the last processed records is and start from there.

Also, if the "AddExpensesFromXLS" is more than just storing the data from the XLS in the database, but also a lot of querying and calculating before you store something, you could try first storing the data as-is in a temporary table, then starting the actual processing. Again keeping track where you were last time.

It's difficult to say what's the ideal situation here, as I don't know what your code looks like. But again, storing 100,000+ records in the database shouldn't take that much time, unless you have really big and multiple tables with a lot of indexes.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.