jdbc template batch update with snowflake database is very slow

Question

I have a spring boot application that connects to the Snowflake Database and uploads records (approx 50 columns of different data types). I am using

JdbcTemplate.batchUpdate(insertSql, values, types)

to do the bulk insert. Currently, it is consuming around 100 seconds for 50,000 records. I want to improve the batch performance. but not able to find an optimal solution.

I referred to and tried the solution mentioned in this post, but it didn't help at all. Any suggestions will be highly appreciated

Answer 1

I moved away from batch insert to snowflake copy command using JDBC. It is lightning fast. With the copy command, it is barely taking 2-3 seconds to load 50000 records from a CSV file with XS (extra small) size Dataware house.

Moreover, in case of error, messages are very clear and can be viewed in information_schema.load_history. Different file formats can be loaded and there are a variety of options to customize load process.

In my case, I am first loading the CSV file to the internal staging area (takes less than 1 sec), Run Copy command (takes 1-2 seconds), verifying load status in information_schema.load_history table (takes a few milliseconds) and proceed accordingly

This article was also helpful for running copy command with JDBC