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

Have you also checked in Query History in Snowflake UI to see if the insert time is related to Snowflake server side vs your app? — Sergiu, Apr 08 '22 at 10:56
@Sergiu Thanks for pointing in this direction. Snowflake is taking approx 30 seconds to insert 50k records, 70% time is used by the network + java app. I am investigating more. I will share more details regarding it — Ish Mahajan, Apr 11 '22 at 08:43

score 1 · Accepted Answer · answered Apr 20 '22 at 07:11

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

jdbc template batch update with snowflake database is very slow

1 Answers1