Hi,
I have an application that takes a range (start of range and end of range) and creates records in an entity based on that start and end range. I use a for each loop and cycle through it and use a "create" server action. This seems like common practice. However, when there is a large range, for instance 0-6000, it takes a long time to process. Is there a more efficient way to create records so I can cut down on time. Creating 6000 records took me 30 minutes.
Hello Tenisha,
If I understand your question then....
You can bootstrap data through excel file in an entity.
and please check below thread for your reference.
https://www.outsystems.com/forums/discussion/69937/big-data-very-slow/
hope this helps you.
Regards,
Ajit kurane.
Hi Ajit,
It's not in an excel sheet. The range is taken from inputs in a form.
Tenisha Lovell
Hi Tenisha,
Definitely the most performant approach is to use an Advanced SQL widget and use a bulk insert query. Actually, this is a performance best practice, perhaps you want to take a look at those to learn how to avoid these performance bottlenecks.
Kind Regards,João
Hi João,
Thank you! I will try this. Is there an example I can follow? I take the range and create a list with each integer, for example if the start of range was 0 and the end of range 6000, the list will have all integers from 0-6000. That will then be iterated like below,
When using Advanced SQL, the syntax depends on your database server (e.g. MySQL, SQL Server, Oracle). Here you can find an example for SQL Server syntax:
In any case, it's not common to insert 60000 records numbered from 1 to 60000.
I don't know what your use case is, but perhaps you want to revisit your data model, store in the database the intervals (in your case 1 record with minRange = 1 and maxRange = 60000) and use the reading query on that interval. But once again, it depends on your business use case.
I am using a SQL Server. To be honest, I am not inserting integers into a field necessarily, but I used that example to simplify and because some of the information is confidential. As I mentioned to Kilian below, The entity has 15 attributes and one of the fields require a "calculation" to be made. Would I have to have to create a record list to do the bulk insert?
Depending on the size of the records (the number of attributes) and the number of foreign keys and indexes (not to mention the speed of the database itself), inserting records can take a long time, even with the bulk inserts João mentioned. With 6000 records taking half an hour, that's about 3 per second, which isn't very fast, but not extremely slow either.
Hello Kilian,
I agree! I worked it out and it isn't very slow especially when the range is smaller. There are quite a few attributes (15 to be exact) and one of the attributes require a "calculation" to be performed. Would the bulk insert save any additional time though?
Hi, Lovell,
In addition to ITAS (Insert Table As Select) as pointed by Joao above, you can do pre-calculation first to the "calculation" in a separate process (say it in a timer). I think it will be much faster, mainly if it is a complex calculation.
regards
That quite depends on the type of calculation. Modern servers are very fast when it comes to calculations, so if it's just calculations, and not say ListFilters on long Lists or database access (like queries), calculation time will be negligeable compared to the insert in the database.
tl;dr Don't do premature optimizations!