导入“大”的性能问题从Web服务到MSSQL 2005的数据量
我有一个程序,用于通过 Web 服务 (REST) 将主表(大约 20 个)从 Oracle 复制/镜像到 MSSQL 2005。
该程序定期从Web服务读取XML数据,并通过jpa实体将其转换为列表。该实体列表将通过 JPA 存储到 MSSQL。 所有 jpa 实体将由创建 Web 服务的团队提供。
我注意到有两个问题,经过一番搜索后似乎无法解决。
第一个问题:通过 JDBC jpa 插入/更新的性能非常慢,每行大约需要 0.1s... 通过 C# 执行相同操作 ->数据表->批量插入到数据库中的新表 ->调用存储过程基于连接进行批量插入/更新需要 0.01 秒处理 4000 条记录。 (每个表每 5 分钟大约有 500-5000 条记录)
下面显示了执行该任务的 Java 代码的快照 ->持久库-> EclipseLink JPA2.0
private void GetEntityA(OurClient client, EntityManager em, DBWriter dbWriter){
//code to log time and others
List<EntityA> response = client.findEntityA_XML();
em.setFlushMode(FlushModeType.COMMIT);
em.getTransaction().begin();
int count = 0;
for (EntityA object : response) {
count++;
em.merge(object);
//Batch commit
if (count % 1000 == 0){
try{
em.getTransaction().commit();
em.getTransaction().begin();
commitRecords = count;
} catch (Exception e) {
em.getTransaction().rollback();
}
}
}
try{
em.getTransaction().commit();
} catch (Exception e) {
em.getTransaction().rollback();
}
//dbWriter write log to DB
}
是否有任何错误导致速度缓慢?如何提高插入/更新速度?
第二个问题:大约有20个表需要复制,我创建了与上面类似的相同数量的方法,基本上复制上面的方法20次并将EntityA替换为EntityB等等,你明白了。 ..
有没有办法概括该方法,以便我可以放入任何实体?
I have a program that is used to replicate/mirror the main tables (around 20) from Oracle to MSSQL 2005 via webservice (REST).
The program periodically read XML data from the webservice and convert it to list via jpa entity. This list of entity will store to MSSQL via JPA.
All jpa entity will be provided by the team who create the webservice.
There are two issues that I notice and seems unsolvable after some searching.
1st issue: The performance of inserting/updating via JDBC jpa is very slow, it takes around 0.1s per row...
Doing the same via C# -> datatable -> bulkinsert to new table in DB -> call stored procedure to do mass insert / update base on joins takes 0.01 s for 4000 records.
(Each table will have around 500-5000 records every 5 minutes)
Below shows a snapshot of the Java code that do the task-> persistent library -> EclipseLink JPA2.0
private void GetEntityA(OurClient client, EntityManager em, DBWriter dbWriter){
//code to log time and others
List<EntityA> response = client.findEntityA_XML();
em.setFlushMode(FlushModeType.COMMIT);
em.getTransaction().begin();
int count = 0;
for (EntityA object : response) {
count++;
em.merge(object);
//Batch commit
if (count % 1000 == 0){
try{
em.getTransaction().commit();
em.getTransaction().begin();
commitRecords = count;
} catch (Exception e) {
em.getTransaction().rollback();
}
}
}
try{
em.getTransaction().commit();
} catch (Exception e) {
em.getTransaction().rollback();
}
//dbWriter write log to DB
}
Anything done wrong causing the slowness? How can I improve the insert/update speed?
2nd issue: There are around 20 tables to replicate and I have created the same number of methods similar to above, basically copying above method 20 times and replace EntityA with EntityB and so on, you get the idea...
Is there anyway to generalize the method such that I can throw in any entity?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
OR 映射器对于批量插入通常很慢。根据定义。你蚂蚁速度?使用另一种方法。
一般来说,ORM 不会满足批量插入/存储过程方法的需求,因此会在这里被屠杀。您使用了错误的方法来获得高性能刀片。
泛型。成为 java 的一部分已经有一段时间了。
OR mappers generally are slow for bulk inserts. Per definition. You ant speed? Use another approach.
In general an ORM will not cater fur the bulk insert / stored procedure approach and tus get slaughtered here. You use the wrong appraoch for high performance inserts.
Generics. Part of java for some time now.
您还可以通过 JPA 执行 SQL、存储过程或 JPQL 更新所有查询。我不确定这些对象来自哪里,但如果您只是将同一数据库中的一个表迁移到另一个表,则可以使用 JPA 在 Java 中的 C# 中执行相同的操作。
如果你想处理JPA中的对象,那么请看,
http://java -persistence-performance.blogspot.com/2011/06/how-to-improve-jpa-performance-by-1825.html
对于#2,将 EntityA 更改为 Object,并且您有一个通用方法。
You can execute SQL, stored procedure or JPQL update all queries through JPA as well. I'm not sure where these objects are coming from, but if you are just migrating one table to another in the same database, you can do the same thing you were doing in C# in Java with JPA.
If you want to process the objects in JPA, then see,
http://java-persistence-performance.blogspot.com/2011/06/how-to-improve-jpa-performance-by-1825.html
For #2, change EntityA to Object, and you have a generic method.