Optimize Boomi Database V2 Connector performance for MySQL INSERT operation

As an Integration Developer, you aim to craft efficient integrations that deliver rapid results and optimal performance. Optimizing the connector configuration is a crucial step that can yield significant benefits. While this might seem like a small tweak, it can have a massive impact on your system's performance. Whether it's faster execution, reduced resource usage, or smoother integrations, the right optimizations can transform bottlenecks into seamless workflows. When every second counts, an efficient connector can make all the difference for large-scale operations.

The Database V2 connector allows you to process or retrieve data in any JDBC-supported database using Database drivers. The connector uses JDBC (Java Database Connectivity) to communicate to the user-defined database and retrieve and manipulate the records. You can connect to relational databases like MySQL, PostgreSQL, MS SQL Server, Oracle, etc.

If you're working with MySQL and often run batch operations like INSERT or UPDATE, you might want to know about a handy feature called the rewriteBatchedStatements property. When enabled, the driver transforms a batch of SQL statements into a single, optimized command. This streamlined approach helps the database execute your tasks much more efficiently.

Example:

• Without rewriteBatchedStatements, each statement in the batch is individually sent to the server:

INSERT INTO table_name (col1, col2) VALUES (val1, val2);  
INSERT INTO table_name (col1, col2) VALUES (val3, val4);  

• With rewriteBatchedStatements set to true, statements are combined into a single query:

INSERT INTO table_name (col1, col2) VALUES (val1, val2), (val3, val4);

This connection configuration is meant to improve the batching performance and does not cause any behavioral changes.

1. Start by enabling batch processing: Use batch processing to group multiple inserts into a single transaction and configure the Batch Size in the Database V2 connector settings. For more information on batch processing and configuring the batch size, refer to the help topic Database V2 operation.

2. Use rewriteBatchedStatements in MySQL Connection :

   • Add rewriteBatchedStatements=true to your MySQL connection URL: jdbc:mysql://localhost:3306/yourDatabase?rewriteBatchedStatements=true,

     

   • Or add a key-value pair under Connection Properties.

     

To reiterate, here are a couple of benefits you experience when using the rewriteBatchedStatements:

• Fewer database round trips.

• Combines multiple individual insert statements into a single bulk insert statement.

• Reduces execution time significantly

• Minimizes network latency and resource usage

• Helps reduce transaction overhead.

Here is an improvement chart that we observed on a local atom:

Using rewriteBatchedStatements=true gives lots of improvement in batch insertion, but there are some caveats you should be aware of, refer to the MySQL documentation for more details.

By optimizing your Boomi Database V2 Connector through enabling rewriteBatchedStatements and leveraging batch processing, you will not only significantly improve the performance of your MySQL insert operations but also help reduce execution time, enhance resource utilization, and increase throughput for large-scale integrations.

Ready to speed up your database operations? Enable rewriteBatchedStatements today and experience the difference. Let us know how it improved your workflow!