問(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)名稱為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)注明出處。