1

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

Improve this question
4
  • 2
    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? Commented Apr 8, 2022 at 10:56
  • you cannot try the copy command route ? Commented Apr 8, 2022 at 19:15
  • @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 Commented Apr 11, 2022 at 8:43
  • @hopeIsTheonlyWeapon i can give it a try an get back Commented Apr 11, 2022 at 10:18

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.