You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/01/03 01:29:15 UTC
[GitHub] [incubator-shardingsphere] pengqiang323 opened a new issue #3853:
It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
pengqiang323 opened a new issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853
My table data is 10W records, and it takes 10s to batch add 1000 records，How can I optimize it?
sharding-jdbc-spring version ： 4.0.0-RC1
mysql-connector-java version ：5.1.46
Code：
```
/**
* sharding config
*
* @return
* @throws SQLException
*/
@Bean
public DataSource dataSourceInit() throws SQLException {
DruidDataSource druidDataSource = druidDataSourceInit();
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.setDefaultDataSourceName("master");
// 添加分表策略
shardingRuleConfig.getTableRuleConfigs().add(this.getEtctsAdMpTableRuleConfig(druidDataSource));
// 添加分表策略
// shardingRuleConfig.getTableRuleConfigs().add(this.getEtctsAdEpTableRuleConfig(druidDataSource));
// 配置其余信息
Properties properties = new Properties();
// properties.setProperty("executor.size", "20");
// properties.setProperty("sql.show", "true");
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("master",druidDataSource);
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, properties);
// return druidDataSource;
}
private TableRuleConfiguration getEtctsAdMpTableRuleConfig(@NonNull DataSource dataSource) throws SQLException {
List<String> tables = ShardingUtil.getShardingTables(dataSource, "t_etcts_ad_mp_\\d{6}");
Assert.notEmpty(tables, "tables must not be empty.");
String actualDataNodes = tables.stream()
.map(table -> "master." + table)
.collect(Collectors.joining(","));
TableRuleConfiguration userTableRuleConfig = new TableRuleConfiguration("t_etcts_ad_mp", actualDataNodes);
// 分表保存策略
userTableRuleConfig.setTableShardingStrategyConfig(
new StandardShardingStrategyConfiguration("created_time",
new DatePreciseShardingAlgorithm("t_etcts_ad_mp_%s")));
// 主键生成规则
// KeyGeneratorConfiguration keyGeneratorConfiguration = new KeyGeneratorConfiguration("SNOWFLAKE", ShardingTableConstants.AD_COLUMN_NAME_ID);
// userTableRuleConfig.setKeyGeneratorConfig(keyGeneratorConfiguration);
return userTableRuleConfig;
}
```
```
/**
* sharding By date
* Created by Damo on 2019-11-22 10:51.
*/
public class DatePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
/**
* 日志输出
*/
private final Logger logger = LoggerFactory.getLogger(DatePreciseShardingAlgorithm.class);
private String tableName;
DatePreciseShardingAlgorithm(String tableName){
this.tableName = tableName;
}
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Date> shardingValue) {
Date createTime = shardingValue.getValue();
String dataMonth = DateTime.now().toString("yyyyMM", Locale.CHINA);
try {
dataMonth = LocalDate.fromDateFields(createTime).toString("yyyyMM", Locale.CHINA);
} catch (Exception e) {
logger.error("解析创建时间异常，分表失败，进入默认表");
}
return String.format(tableName, dataMonth);
}
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #3853: It takes
10s to insert 1000 pieces of data in batch. How can I optimize it?
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu edited a comment on issue
#3853: It takes 10s to insert 1000 pieces of data in batch. How can I
optimize it?
Posted by GitBox <gi...@apache.org>.
terrymanu edited a comment on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570786465
@xfyang1989 By the way, we have already talk about the problem in previous issue. I think you have already known it totally.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] pengqiang323 commented on issue #3853:
It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
Posted by GitBox <gi...@apache.org>.
pengqiang323 commented on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570454612
Thank you for your reply.
It takes 10 seconds to insert 1000 records using shardingjdbc ，And I use the bulk insert statement of mybatis。
The sharding route is correct, But it took too long to insert。
```
<insert id="insertList">
INSERT INTO t_etcts_ad_mp ( id,trade_type,org_id, pay_fee, fee, discount_fee, media_type, obu_id, card_id, trans_time,
vehicle_id,
vehicle_type, service_type, description, pass_id,status,created_time,ad_version,au_version,sum_version,dr_version,dz_version,dz_payment_date,dz_payment_num)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
( #{item.id},#{item.tradeType},#{item.orgId}, #{item.payFee}, #{item.fee}, #{item.discountFee}, #{item.mediaType},
#{item.obuId},
#{item.cardId}, #{item.transTime}, #{item.vehicleId}, #{item.vehicleType}, #{item.serviceType},
#{item.description}, #{item.passId}, #{item.status}, #{item.createdTime},#{item.adVersion}, #{item.auVersion},
#{item.sumVersion}, #{item.drVersion}, #{item.dzVersion}, #{item.dzPaymentDate}, #{item.dzPaymentNum})
</foreach>
</insert>
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] xfyang1989 commented on issue #3853: It
takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
Posted by GitBox <gi...@apache.org>.
xfyang1989 commented on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570480720
> Thank you for your reply.
>
> It takes 10 seconds to insert 1000 records using shardingjdbc ，And I use the bulk insert statement of mybatis。
>
> The sharding route is correct, But it took too long to insert。
>
> ```
> <insert id="insertList">
> INSERT INTO t_etcts_ad_mp ( id,trade_type,org_id, pay_fee, fee, discount_fee, media_type, obu_id, card_id, trans_time,
> vehicle_id,
> vehicle_type, service_type, description, pass_id,status,created_time,ad_version,au_version,sum_version,dr_version,dz_version,dz_payment_date,dz_payment_num)
>
> VALUES
> <foreach collection="list" item="item" index="index" separator=",">
> ( #{item.id},#{item.tradeType},#{item.orgId}, #{item.payFee}, #{item.fee}, #{item.discountFee}, #{item.mediaType},
> #{item.obuId},
> #{item.cardId}, #{item.transTime}, #{item.vehicleId}, #{item.vehicleType}, #{item.serviceType},
> #{item.description}, #{item.passId}, #{item.status}, #{item.createdTime},#{item.adVersion}, #{item.auVersion},
> #{item.sumVersion}, #{item.drVersion}, #{item.dzVersion}, #{item.dzPaymentDate}, #{item.dzPaymentNum})
>
> </foreach>
>
> </insert>
> ```
I also encounter this problem. It's a problem when using batch insert. The number of your inserts, which are parsed every time, is very time-consuming. I hope to help optimize the parsing engine
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] xfyang1989 commented on issue #3853: It
takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
Posted by GitBox <gi...@apache.org>.
xfyang1989 commented on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570789781
I know what you mean. I mean that the number of batch inserts is not certain. Sharing thinks that all new SQL statements will be parsed
发自我的iPhone
> 在 2020年1月4日，21:38，Liang Zhang <no...@github.com> 写道：
>
>
> @xfyang1989 @pengqiang323 Please use PreparedStatement. Statement is slower than PreparedStatement, we just assume they don't care about the performance if user using Statement.
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub, or unsubscribe.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] pengqiang323 commented on issue #3853:
It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
Posted by GitBox <gi...@apache.org>.
pengqiang323 commented on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570430750
2020-01-03 09:35:18.674] [main] INFO org.apache.shardingsphere.core.util.ConfigurationLogger log 134 - ShardingRuleConfiguration
defaultDataSourceName: master
tables:
t_etcts_ad_mp:
actualDataNodes: master.t_etcts_ad_mp_202001,master.t_etcts_ad_mp_202002
logicTable: t_etcts_ad_mp
tableStrategy:
standard:
preciseAlgorithmClassName: com.tzcpa.hngs.ccyboa.connector.config.DatePreciseShardingAlgorithm
shardingColumn: created_time
[2020-01-03 09:35:18.691] [main] INFO org.apache.shardingsphere.core.util.ConfigurationLogger log 134 - Properties
{}
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3853: It
takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570451892
You may use PreparedStatement and parameter markers in your SQL.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu edited a comment on issue
#3853: It takes 10s to insert 1000 pieces of data in batch. How can I
optimize it?
Posted by GitBox <gi...@apache.org>.
terrymanu edited a comment on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570786465
@xfyang1989 By the way, we have already talk about the problem in previous issue. I think you have already known it totally.
As you know, it is the regular performance of ANTLR, there is no way to improve the performance beside we don't use ANTLR.
Please use PreparedStatement because it will take the parse result from cache after second times.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu edited a comment on issue
#3853: It takes 10s to insert 1000 pieces of data in batch. How can I
optimize it?
Posted by GitBox <gi...@apache.org>.
terrymanu edited a comment on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570786465
@xfyang1989 By the way, we have already talk about the problem in previous issue. I think you have already known it totally.
As you know, it is the regular performance of ANTLR, there is no way to improve the performance beside we don't use ANTLR.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3853: It
takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570786465
@xfyang1989 By the way, we have already talk about the problem in previous issue. I think you have already know it totally.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3853: It
takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3853: It takes 10s to insert 1000 pieces of data in batch. How can I optimize it?
URL: https://github.com/apache/incubator-shardingsphere/issues/3853#issuecomment-570786399
@xfyang1989 @pengqiang323 Please use PreparedStatement. Statement is slower than PreparedStatement, we just assume they don't care about the performance if user using Statement.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services