問(wèn)題描述
在使用開(kāi)源ORMLite數(shù)據(jù)庫(kù)組件時(shí)，為了測(cè)試需要，寫(xiě)了個(gè)異步任務(wù)循環(huán)生成10000條數(shù)據(jù)，代碼如下：
/**
* 創(chuàng)建數(shù)據(jù)測(cè)試數(shù)據(jù)
* @author JacenChiu
*/
private class CreateTestDataForDb extends AsyncTask<Integer, Void, Long>{
public int countPerTime = 10000;
@Override
protected void onPreExecute(){
super.onPreExecute();
}
@Override
protected Long doInBackground(Integer... params){
long startTime = System.currentTimeMillis();
for(int i=1; i<=10000; i++){
ClaxxDao.createOrUpdate(new Claxx("測(cè)試班級(jí)" + i));
Message message = new Message();
message.what = 1;
message.obj = i + "/" + countPerTime;
mCreateProgressHandler.sendMessage(message);
}
long endTime = System.currentTimeMillis();
return endTime - startTime;
}
@Override
protected void onPostExecute(Long result){
Message message = new Message();
message.what = 1;
message.obj = "耗時(shí)【" + (result/1000) + "】秒";
mCreateProgressHandler.sendMessage(message);
Toast.makeText(getApplicationContext(),
"創(chuàng)建數(shù)據(jù)完成翁垂，耗時(shí)【" + (result/1000) + "】秒！", Toast.LENGTH_LONG).show();
Log.d("MainActivity", "--創(chuàng)建數(shù)據(jù)完成硝桩，耗時(shí)【" + (result/1000) + "】秒沿猜！");
Intent intent = new Intent(Constant.ACTION_REFREASH_CLASS);
mContext.sendBroadcast(intent);
super.onPostExecute(result);
}
}
在GalaxyS4執(zhí)行上面的代碼，發(fā)現(xiàn)這1w條數(shù)據(jù)居然要耗時(shí)195s才完成碗脊，如下圖：
插入1w條數(shù)據(jù)耗時(shí)
原因分析
之所以會(huì)耗時(shí)那么久啼肩，是因?yàn)镺RMLite每次執(zhí)行ClaxxDao.createOrUpdate(new Claxx("測(cè)試班級(jí)" + i))時(shí)都會(huì)自動(dòng)提交數(shù)據(jù)，而不是在最后統(tǒng)一提交數(shù)據(jù)的，這樣相當(dāng)于commit了1w次疟游。
如果要提高效率就必須關(guān)閉該DAO的自動(dòng)提交功能呼畸，并開(kāi)啟事務(wù)痕支，在所有數(shù)據(jù)的insert語(yǔ)句都生成后颁虐，統(tǒng)一一次commit。
解決方案
通過(guò)下面的ORM事務(wù)的代碼改造卧须，可以將1w條數(shù)據(jù)插入時(shí)間縮短到14s：
/**
* 創(chuàng)建數(shù)據(jù)測(cè)試數(shù)據(jù)
* @author JacenChiu
*/
private class CreateTestDataForDb extends AsyncTask<Integer, Void, Long> {
public int countPerTime = 10000;
@Override
protected void onPreExecute() {
super.onPreExecute();
}
@Override
protected Long doInBackground(Integer... params) {
// ORMLite的數(shù)據(jù)連接封裝類(lèi)
AndroidDatabaseConnection adc = null;
adc = new AndroidDatabaseConnection(DatabaseHelper.getHelper().getWritableDatabase(), true);
// 設(shè)置要開(kāi)啟事務(wù)的DAO不自動(dòng)提交代碼
RuntimeExceptionDao<Claxx, String> dao = DatabaseHelper.getHelper().getClaxxDao();
dao.setAutoCommit(adc, false);
// 存儲(chǔ)點(diǎn)名稱(chēng)為create_claxx
Savepoint sp = null;
try {
sp = adc.setSavePoint("create_claxx");
long startTime = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
dao.createOrUpdate(new Claxx("測(cè)試班級(jí)" + i));
Message message = new Message();
message.what = 1;
message.obj = i + "/" + countPerTime;
mCreateProgressHandler.sendMessage(message);
}
// 成功添加后統(tǒng)一提交數(shù)據(jù)
adc.commit(sp);
long endTime = System.currentTimeMillis();
return endTime - startTime;
} catch (SQLException e) {
e.printStackTrace();
try {
// 發(fā)生異常時(shí)進(jìn)行回滾
adc.rollback(sp);
} catch (SQLException e1) {
e1.printStackTrace();
}
return 0l;
}
}
@Override
protected void onPostExecute(Long result) {
Message message = new Message();
message.what = 1;
message.obj = "耗時(shí)【" + (result / 1000) + "】秒";
mCreateProgressHandler.sendMessage(message);
Toast.makeText(getApplicationContext(),
"創(chuàng)建數(shù)據(jù)完成另绩，耗時(shí)【" + (result / 1000) + "】秒！", Toast.LENGTH_LONG).show();
Log.d("MainActivity", "--創(chuàng)建數(shù)據(jù)完成花嘶，耗時(shí)【" + (result / 1000) + "】秒笋籽！");
Intent intent = new Intent(Constant.ACTION_REFREASH_CLASS);
mContext.sendBroadcast(intent);
super.onPostExecute(result);
}
}
本文為JacenChiu原創(chuàng)，轉(zhuǎn)載請(qǐng)注明出處椭员。
