Bulk Upsert enhancements to Data Migrator

[Greg Weinheimer]

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

MySQL

Oracle

Teradata

Amazon Redshift

Apache Hive

EXASol

Google BigQuery

Greenplum DB

Hyperstage (PG)

Informix

MariaDB

Netezza

PostgreSQL

Snowflake

Sybase

Vertica

Salesforce.com