SQL Query or BulkInsert Component?

Hi guys!

I added 2 tables from remote SQL Server(Table1 and Table2).

I did a test and i created 2 buttons in my screen: "Insert by Query" and "Insert by BulkInsert".

My Table1 have 500000 records and both buttons do same action: copy records from Table1 to Table2.

When i used "Insert by Query", the time was 4 seconds to complete the process.

When i used "Insert by BulkInsert", the time was 16 seconds.

This is make sense?Anybody can say me, why this happen?

Tks!

* The component that i used: https://www.outsystems.com/forge/component-overview/1117/bulkinsert

Solution

Rodrigo Noma wrote:

Hi guys!

I added 2 tables from remote SQL Server(Table1 and Table2).

I did a test and i created 2 buttons in my screen: "Insert by Query" and "Insert by BulkInsert".

My Table1 have 500000 records and both buttons do same action: copy records from Table1 to Table2.

When i used "Insert by Query", the time was 4 seconds to complete the process.

When i used "Insert by BulkInsert", the time was 16 seconds.

This is make sense?Anybody can say me, why this happen?

Tks!

* The component that i used: https://www.outsystems.com/forge/component-overview/1117/bulkinsert

 Yes, Rodrigo, that's bound to happen. When you use the bulk insert component you don't have to create the actual query but just pass a list of records based on which there is a .Net code in the background which does the work for us. There are some steps involved like data validations, reflection to check the dynamic column names , System. Data library to write the data to SQL etc. So these steps would take time. On the other hand, the SQL query is a direct query and would have no overhead.  

 


Hope that makes sense!


Cheers,

Tushar

Solution

TKS Tushar!!!

Your answer was clear for me!

So, If i need to do a load of registers in database, is better i choose by SQL query instead bulk(in case the so many data)?

And, when is better to use bulk instead SQL queries?

TKS!



Tushar Panpaliya wrote:

Rodrigo Noma wrote:

Hi guys!

I added 2 tables from remote SQL Server(Table1 and Table2).

I did a test and i created 2 buttons in my screen: "Insert by Query" and "Insert by BulkInsert".

My Table1 have 500000 records and both buttons do same action: copy records from Table1 to Table2.

When i used "Insert by Query", the time was 4 seconds to complete the process.

When i used "Insert by BulkInsert", the time was 16 seconds.

This is make sense?Anybody can say me, why this happen?

Tks!

* The component that i used: https://www.outsystems.com/forge/component-overview/1117/bulkinsert

 Yes, Rodrigo, that's bound to happen. When you use the bulk insert component you don't have to create the actual query but just pass a list of records based on which there is a .Net code in the background which does the work for us. There are some steps involved like data validations, reflection to check the dynamic column names , System. Data library to write the data to SQL etc. So these steps would take time. On the other hand, the SQL query is a direct query and would have no overhead.  

 


Hope that makes sense!


Cheers,

Tushar