Hi @Prejith Prasanna Kumar ,

You should do this asynchronously. Once uploaded, the file should be saved as binary in an entity and queued for background processing by a timer.

Instead of processing the file synchronously (which would force the user to wait), the upload is handled immediately, and a timer asynchronously processes the file later using the "Excel to Record List" tool. This improves performance and prevents timeouts.

Here's an exercise that can help you with that: Implementing a Good Timer Exercise .

Hello.

Following your points and in a very short format because Mihai already answered:

1. You upload file as binary.

2. As you insert data in the UI for calculations, rows are being processed in a timer and sent to a new entity for raw data.

3. The timer may load data into RawData as text instead of doing the type conversion. It is a bit faster.

4. Will any user validate the 100k rows manually??? Still, the time you save on 3 by using text, you will spend on 4 converting to the appropriate types. 

5. Regarding reading speed, that depends on the architecture. Number of attributes on each table and having the proper cache and indexes. It is feasible, but are we talking of a monthly file or one every hour? Because OS is not a data warehouse. You may need a specific software for that.

As per my opinion, after having the binary of the excel, we need to import records from the binary and import all them in a raw table (which will be discarded after importing data to the actual table). Objective of doing this exercise is -: Operations on table data is lighter than the operations on the data placed in excel resource.

After having excel-things on entities you can take inputs from the form and proceed further.

One more thing-: If we have large amount of data, we can process data in chunks. That means, first pick 2000 records and do your operations on it, then commit the changes with this 2000 records. Then pick next chunk of 2000 records. For this chunk approach, you can use flags in the tables.

You can use timer or you can do it directly, it depends upon the business. But the chunk-approach and excel-to-table approach will make things lighter.

Hi @Prejith Prasanna Kumar,

• For 100k rows, avoid processing all at once on the server to prevent timeouts or performance hits. Implement chunked processing: split the file into manageable batches (e.g., 5,000 or 10,000 rows per batch) and process asynchronously. 
• Use bulk queries with IN clauses or joins for batch lookups rather than querying one record at a time. 
• For such a large dataset (up to 100k records), don’t load everything at once in the UI. Implement pagination or infinite scroll with lazy loading:
• Load only the visible page (e.g., 100 rows at a time).As the user scrolls, fetch the next chunk asynchronously.

• When saving 100k records, avoid saving one by one.

• Use bulk insert patterns: Use OutSystems SQL Bulk Insert extension or custom SQL for batch insert. Consider temporary staging tables to insert data first, then merge into production tables.

• If this needs to be asynchronous to avoid UI timeout, run a background process or scheduled job that processes save requests. Use indexed database columns for fast query performance. 

Hope this will help you :)