Use mybatis to insert in batches, each of which needs to be routed for multiple inserted data. It is recommended to add an internal cache to reduce routing frequency and improve performanc

[honestAnt]

Question

I use shardingsphere-jdbc-core-spring-boot-starter(v5.1.1) 、mybatis-plus-boot-starter(3.5.1) into my springBoot project. When I use native sql to batch insert data in mapper.xml, I found that the incoming collection data will be used as the sharding condition of shardingConditions after grammar and lexical analysis, and then each condition must be sharded to find the matching dataNode , the performance is poor when the amount of data inserted in batches is large (most of the shard keys are repeated and do not need to be recalculated)

Batch insert sql example in mapper.xml：insert into ${table} (column1,column2..) values (xxx,xx),(xx,xx)... sharding rules: using the value of a column in the table

Related test screenshots:

Question: Is it possible to customize ShardingRouteEngine (similar to ShardingStandardRoutingEngine) and add a tableColumn cache before the route0() method call to solve the problem of repeated routing?

[honestAnt]

Try to overwrite the source code and modify it. After adding the cache, the performance improvement for large-scale insertion is still very significant; I don’t know if there is any other reasonable way to modify it?

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[terrymanu]

shardingsphere-jdbc-core-spring-boot-starter is deprecated and removed in 5.3.0, please try new version and use ShardingSphere Driver.