[BulkInsert] [BulkInsert] vs WotC topics about bulk inserts vs AsyncronousLogging?

[BulkInsert] [BulkInsert] vs WotC topics about bulk inserts vs AsyncronousLogging?

  
Forge Component
(5)
Published on 2015-11-24 by João Pedro Abreu
5 votes
Published on 2015-11-24 by João Pedro Abreu

Hi,

Just being curious, there are some topics on WotC which are related to this issue:

1) http://www.outsystems.com/ideas/1643/support-for-bulk-insert-and-update
2) http://www.outsystems.com/forums/discussion/13511/is-there-a-common-best-practice-pattern-for-bulk-inserting-large-amount-of-data/
3) http://www.outsystems.com/forums/discussion/13917/importing-large-quantities-of-data/

Would this component be the solution for this?

And what is the difference/relation between the AsyncronousLogging Extension from the platform and this component?

Do you also have an performance example, like 1.000.000 records with 20 atttributes using the OS loop takes 1 hour and with this component 20 minutes?


Kind regards,
Evert
Hi Evert!

Thanks for the great compilation of topics about this. I'll try to answer all:

1) http://www.outsystems.com/ideas/1643/support-for-bulk-insert-and-update
This component allows bulk inserts but not bulk updates, as requested in this idea.

2)  http://www.outsystems.com/forums/discussion/13511/is-there-a-common-best-practice-pattern-for-bulk-inserting-large-amount-of-data/
This component would probably be a good solution for the problem explained in this topic. I would say it is a good idea to use it in every scenario where you are dealing with inserting lots of data into staging tables, or importing files into tables.


3) http://www.outsystems.com/forums/discussion/13917/importing-large-quantities-of-data/
This sounds like a different issue altogether. From what I could understand, the issue here was not the insert speed, which would be the only thing this component could improve.

4) What is the difference/relation between the AsyncronousLogging Extension from the platform and this component?
The platform's 
AsyncronousLogging extension uses message queues to insert records into the database asyncrounously. This is good for auditing actions, for example, as you don't want your application to become slower because you are logging events. It isn't good if you want to wait for the insert to complete before you proceed with your flow and it isn't good to insert a very large number of records, because message queues have a maximum length and any messages that are received when the queue is full are discarded.
BulkInsert is 
syncronous and the number of records it can insert at once is only limited by the server's available memory.

5) Do you also have an performance example, like 1.000.000 records with 20 atttributes using the OS loop takes 1 hour and with this component 20 minutes?
Disclaimer: the performance is not consistent across stacks, as different stacks use different mechanisms to perform the inserts; it also depends on the database hardware, on the number of columns in the entity and on the number of foreign keys on the table. Also, I ran these tests a few months ago and I'm writing fom memory, so the values won't be very precise.
That being said, tests for GeoPerformance on a m3.medium amazon server shown that inserting 15000 records with this component would take between 8s, on SQL Server and 18s on MySQL. The same operation would take 2-8 minutes using single Create actions.


Hello João,

Good reaction! Gives a good insight on what this component is able to do and what the difference is between other functions.

Hope I get some time to check the logic of this component (-:

Kind regards,
Evert