bad performance when use jdbc

[IcebergXTY]

Hi, I found the performance is poor when batch insert to clickhouse-server, here is the demo to reproduce.

1. change the clickhouse config in application.yaml
2. insert the table sql
3. start DemoApplication

I log the time and found that the largest proportion is when 'setting param' , the log is like this

I copy org.springframework.jdbc.core.JdbcTemplate to my project to add some log, other than that, I haven't changed anything.

You can see the log code at here and I used stopwatch to record time.

As you can see, there just 20ms when the sql actually insert to the server but about 1000+ms when prepare the sql.

1 second and more for 1000 data is too slow. But I just use the basic jdbc api so I don't know how to improve the performance now😳

[IcebergXTY]

JDK 17.0.5
ClickHouse 22.10.3.27
Clickhouse-jdbc 0.3.2-patch11

[IcebergXTY]

@zhicwu 还有个问题，老哥，你是中国人吗，看名字好像是拼音。为啥所有的提交和issue都是你在处理，这个项目没有其他人了吗，那些member去哪了😂，项目版本好像已经很久没有更新了

[zhicwu]

As you can see, there just 20ms when the sql actually insert to the server but about 1000+ms when prepare the sql.

1 second and more for 1000 data is too slow. But I just use the basic jdbc api so I don't know how to improve the performance now😳

Could you check batch insert examples at here? In general, you need to tell the driver or at least give it clues to figure out table structure, so that it can use binary stream for batch inserting, instead of constructing a large SQL query. Apart from that, a few more things you can try:

• Use PrepareStatement.setObject by passing primitive or object array directly, instead of creating java.sql.Array object
• Try nightly build, which has 2x performance improvement when dealing with arrays

If performance is mission critical, you may also use Java client - see more information at #928 (expand About the test... to see code).

为啥所有的提交和issue都是你在处理，这个项目没有其他人了吗，那些member去哪了😂，项目版本好像已经很久没有更新了

I took over maintenance for the exact same reason :) The latest code is on develop branch, but yes, it's been a few months that I didn't create any release due to limited time spend on this project.

[IcebergXTY]

Could you check batch insert examples at here?

I have seen the examples and used insert into xx select xx from input() like this, but the result is same as the general insert sql.

Use PrepareStatement.setObject by passing primitive or object array directly, instead of creating java.sql.Array object

This way exactly works well!