Number of customers complained to us that all operations except Add all records are slow and even worse some of them believe that it is the limitation of our software
Note: In order to Update/Delete records update key must be provided.
Our Update key is CustomerId and OrderNo
Let think about how it works
The first step we need to figure out if the record exists in the database so we run count
Select count(*) from [orders] where CustomerId=? and OrderNo=?
If any records found Advanced ETL Processor will update them by executing
Update [orders] set orderdate=?, amount=? where customerid=? And OrderNo=?
If no records found Advanced ETL inserts data into the order table
Insert is very quick because it does not do anything with existing records.
The count is slower because in order to find the record full scan of the table must be performed (or index scan if present)
The update is even slower be because it scans the table and updates the data
Now imagine that our source data has 10,000 records
For every record, we must execute two SQL statements:
count and insert or count and update
So we run 20,000 SQL statements in total.
Every statement takes time to execute.
The more records there is in the target table the more time it takes to execute.
Bear in mind that it is not our software that executes statements it is the database it is connected to.
This works very well for small tables but what if we have millions of records? In this case, we recommend using a temporary table.
All data gets loaded into a temporary table (orders_tmp).
And then update and insert SQL statements are executed:
Update orders set orderdate = orders_tmp.orderdate, amount = orders_tmp.amount from orders_tmp where orders.customerid = orders_tmp.customerid And orders.OrderNo = orders_tmp.OrderNo insert into orders (customerid,OrderNo,orderdate,amount) select customerid,OrderNo,orderdate,amount FROM orders_tmp e where not exists ( select * from orders orders.customerid = e.customerid And orders.OrderNo = e.OrderNo)
You might need to use different syntax for the database you work with