Jump to content

Bulk Upsert enhancements to Data Migrator

Recommended Posts


Data Migrator BULK UPSERT

Over the last year ibi has made a concerted effort to improve performance for data flows upserting data to target databases.

The effort has come in the form of updating our adapters for all databases that support merge and upsert. This allows for us to support bulk upserts, improving performance significantly.

In the example flows I will be showing a flow using insert/update to do upserts to a database table and then a flow using a bulk upsert and pointing out the improvement in throughput. 

These first two images show a flow loading data from a table in a MS SQL Server database to a Postgresql database table using insert/update. This is how upserts were done in Data Migrator before Bulk Upserts were available.





As you can see the log below insert/update process took over 35 minutes to upsert over 2 million rows into the target table.



The next two images show the configuration of a flow that utilizes bulk upsert. The flow is configured to be optimized and Bulk Load is used to Merge data into the Target. This leverages the Merge ability of the database and is significantly more efficient than insert/update.


*Note: On the Web Console Optimize is always set on, so this setting change is unnecessary.



Both of these flows used the same source table, with target tables that are identical. As you see in the log below, using bulk upsert, upserted over 2 million rows into the table in just over 2 minutes. A significant improvement over insert/update which took roughly 35 minutes.


As of 9.3 bulk upsert is supported by the following databases:

MS Azure Synapse Analytics (formerly SQL DW)

MS SQL Server ODBC/AzureDB

MS SQL Server OLE DB/AzureDB




Amazon Redshift

Apache Hive


Google BigQuery

Greenplum DB

Hyperstage (PG)









  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...