英文:
Update query inside the Batch update execution is taking long time
问题
System.out.println("multipleBatchUpdateCheckErroreValues.update starting time------" + new DateTime());
multipleBatchUpdateCheckError(
vSql.toString(), //sql query
pPriceMap.toList(), //rows
50, //batch size
new ObjectPreparedStatementSetter() {
public void setValues(Object row, PreparedStatement ps, int rowNo) throws SQLException {
Price vPrice = (Price) row;
prepareValues(vPrice, ps, UPDATE);
}
});
System.out.println("multipleBatchUpdateCheckErroreValues.update ending time------" + new DateTime());
在上述代码中，更新操作花费的时间过长。如何优化以下代码的执行时间？
SQL 查询如下：
UPDATE PRICE_T
SET TO_TIME = ?,
PRICE_EXCLUDING_TAX = ?,
PRICE_INCLUDING_TAX = ?,
REASON = ?,
PRIORITY_TYPE = ?,
TRANS_TIME = ?,
DEL_TIME = ?,
UPD_TIME = ?
WHERE CLASS = ?
and CLASS_TYPE = ?
and COUNTRY_CODE = ?
and ITEM_NO = ?
and ITEM_TYPE = ?
and CURRENCY_CODE = ?
and PRICE_TYPE = ?
and FROM_TIME = ?
总共有1万行，14列，处理时间为35分钟。
如何优化上述代码的执行时间？
英文:
System.out.println("multipleBatchUpdateCheckErroreValues.update starting time------"+ new DateTime());
multipleBatchUpdateCheckError(
vSql.toString(), //sql query
pPriceMap.toList(), //rows
50, //batch size
new ObjectPreparedStatementSetter() {
public void setValues(Object row, PreparedStatement ps, int rowNo) throws SQLException {
Price vPrice = (Price) row;
prepareValues(vPrice, ps, UPDATE);
}
});
}
System.out.println("multipleBatchUpdateCheckErroreValues.update ending time------"
+ new DateTime());
In the above code, Update is taking too much time to finish. How to optimize the below code execution time?
SQL Query is
UPDATE PRICE_T
SET TO_TIME = ?,
PRICE_EXCLUDING_TAX = ?,
PRICE_INCLUDING_TAX = ?,
REASON = ?,
PRIORITY_TYPE = ?,
TRANS_TIME = ?,
DEL_TIME = ?,
UPD_TIME = ?
WHERE CLASS = ?
and CLASS_TYPE = ?
and COUNTRY_CODE = ?
and ITEM_NO = ?
and ITEM_TYPE = ?
and CURRENCY_CODE = ?
and PRICE_TYPE = ?
and FROM_TIME = ?
>Total rows 10k, columns 14, time taking to process is 35 mins.
答案1
得分: 0
在对所有8个"where"子句列创建索引后，执行时间减少到1秒。
英文:
After creating the indexes on all the 8 columns of where clause , the execution time decreased to 1 second.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论