javacc parser is very slow to parse sql

[holmofy]

I executed a batch insert SQL, but the execution was very slow. I took a look at Flame Graph and the sql parsing took most of the time. I looked at the source code of this project, which uses the parser generated by javacc. Will this have a significant impact on performance? I hope someone can help improve it.

my sql like this:

insert into trade_info (id, ....)
values 
(111, ...),
(222, ...),
(333, ...),
...
-- 1000 rows

[zhicwu]

The overhead may high for very large SQL. Why not use jdbc batch insertion? It should be much faster than crafting and sending a large sql. Alternatively, you can turn the javacc parser off by adding use_new_parser=false in your connection string.

[holmofy]

Yes, I also tried batch insertion, but the speed did not improve significantly. In the above picture, the red is the batch insertion of jdbc(10k rows 1.83s), and the blue is the direct batch sql insertion(10k rows 3.41s).
Can batch insert of jdbc support rewrite batched statements?

I think the insertion speed of a single SQL is much slower than that of multiple batches.

And I added the use_new_parser=false attribute, an error was reported when creating a connection, init timezone failed

[zhicwu]

And I added the use_new_parser=false attribute, an error was reported when creating a connection, init timezone failed

This is not expected. I'll look into this issue and add more tests accordingly.

By batch insertion, I meant something like below(or this example):

try (PreparedStatement s = connection.preparedStatement("insert into trade_info (id, ....) values (?, ...)")) {
    int index = 1;
    s.setInteger(index++, 111);
    ...
    s.addBatch();
    ...
    s.executeBatch(); // you may implement a loop and call this method for every 10k records or so
}

The query will be rewrite to below:

insert into trade_info (id, ....) format TabSeparated
111\t...
222\t...
333\t...
...

[holmofy]

I tested the performance on the test machine. The machine performance is like this:

Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
4 Core  32G

For 1000 to 10000 rows of data, I test for 200 rounds and then take the average. And finally got the following statistical results(unit is millisecond):

• SqlExecutor is to build sql and then execute it with clickhouse-driver.
• BatchUpdater uses what you said PreparedStatement.addBatch to add batches and then executeBatch.
• HttpClientExecutor is that I use Apache HttpClient to directly access the http interface of clickhouse-server to execute the built sql statement.

Compared with using http client directly, clickhouse-driver has a lot of performance loss.

[zhicwu]

Thanks a lot @holmofy for detailed inputs here. The poor number shows plenty of room to improve the driver ;) Actually, some of the features I'm working on will improve performance of batch insertion, so I hope the situation will be better after 0.3.1 release.

[holmofy]

Sorry, I tested it again and found that using the native batch and using httpClient is similar.

For the previous slow of batch insert, the core reason is that the spring framework call connect.getMetaData() while some fields of the inserted data are null. And ClickHouseConnectionImpl.getMetaData() will use LogProxy reflection proxy to record logs.

I think the LogProxy can be optimized. There should be many frameworks that call ClickHouseConnectionImpl.getMetaData(). And reflection logging really affects performance.

[zhicwu]

What's the difference between BatchUpdater and JdbcBatch? What do you mean by native batch? Using the generated SQL I mentioned above?

[holmofy]

BatchUpdater is a batch processing method provided by spring framework JdbcTemplate. I thought it didn't do much, but I just tested it and the logic for handling null values is still more complicated.

BatchUpdater like this:

jdbcTemplate.batchUpdate(sql, args);

JdbcBatch like this:

try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
    Preconditions.checkArgument(JdbcUtils.supportsBatchUpdates(conn));
    for (Object[] arg : args) {
        for (int i = 0; i < arg.length; i++) {
            int paramIndex = i + 1;
            Object o = arg[i];
            if (o instanceof CharSequence) {
                ps.setString(paramIndex, o.toString());
            } else {
                ps.setObject(paramIndex, o);
            }
        }
        ps.addBatch();
    }
    ps.executeBatch();
} catch (SQLException e) {
    ExceptionUtils.rethrow(e);
}

[zhicwu]

Thanks again for the clarification. So to summarize:

1. SQL parser's overhead is too high when dealing with large SQL
2. getMetaData should be optimized as it slows down JdbcTemplate for null-check - not sure why they use the method so frequent but looks like it can be cached
3. Batch insertion is inconvenient(compare to JdbcTemplate) and it can be optimized(~10% overhead)

Now batch insertion at this point is based on text-based format, so I expect the performance will be improved after switching to binary format. I'll address the last two in 0.3.1 release, and the first in 0.4.0.

[holmofy]

perfect !!!