数据库大批量操作数据耗时分析

数据库操作过程中经常会遇到需要大批量操作数据的情况，使用不同的方式会占用不同的时间，下面是我在OrmLite中的一些耗时记录

使用callBatchTasks加载数据

qfQbsCellDao.callBatchTasks(new Callable<Object>() {
            @Override
            public Object call() throws Exception {
                for (DataAdjustmentSyncCellQBSInfos cellItem : cellQBSInfos) {

                    TblQFQbsCell cellData = new TblQFQbsCell();
                    cellData.setCellId(cellItem.getId());
                    cellData.setCellCode(cellItem.getCode());
                    cellData.setCellName(cellItem.getName());
                    cellData.setCellTypeId(cellItem.getCellTypeId());
                    cellData.setPartId(cellItem.getPartQBSId());
                    cellData.setContractId(cellItem.getContractId());
                    cellData.setModifyTime(cellItem.getLastModTime());
                    cellData.setState(cellItem.getState());
                    cellData.setJsonData(gson.toJson(cellItem));

                    qfQbsCellDao
                            .createOrUpdate(cellData);
                }
                return null;
            }
        })

共7373ms

直接createOrUpdate

for (DataAdjustmentSyncCellQBSInfos cellItem : cellQBSInfos) {

            TblQFQbsCell cellData = new TblQFQbsCell();
            cellData.setCellId(cellItem.getId());
            cellData.setCellCode(cellItem.getCode());
            cellData.setCellName(cellItem.getName());
            cellData.setCellTypeId(cellItem.getCellTypeId());
            cellData.setPartId(cellItem.getPartQBSId());
            cellData.setContractId(cellItem.getContractId());
            cellData.setModifyTime(cellItem.getLastModTime());
            cellData.setState(cellItem.getState());
            cellData.setJsonData(gson.toJson(cellItem));

            qfQbsCellDao
                    .createOrUpdate(cellData);
        }

共29319ms

使用AndroidDatabaseConnection一次提交事务

// ORMLite的数据连接封装类
        AndroidDatabaseConnection adc = new AndroidDatabaseConnection(baseDBHelper.getWritableDatabase(),true);
        qfQbsCellDao.setAutoCommit(adc,false);
        // 存储点名称为create_claxx
        Savepoint sp = adc.setSavePoint("Create_Update_Cell");
        for (DataAdjustmentSyncCellQBSInfos cellItem : cellQBSInfos) {

            TblQFQbsCell cellData = new TblQFQbsCell();
            cellData.setCellId(cellItem.getId());
            cellData.setCellCode(cellItem.getCode());
            cellData.setCellName(cellItem.getName());
            cellData.setCellTypeId(cellItem.getCellTypeId());
            cellData.setPartId(cellItem.getPartQBSId());
            cellData.setContractId(cellItem.getContractId());
            cellData.setModifyTime(cellItem.getLastModTime());
            cellData.setState(cellItem.getState());
            cellData.setJsonData(gson.toJson(cellItem));

            qfQbsCellDao
                    .createOrUpdate(cellData);
        }
        adc.commit(sp);

共7574ms

本作品采用知识共享署名-相同方式共享 4.0 国际许可协议进行许可

栈外之地