北大青鸟

北大青鸟泰安校区

青鸟热线

Android批量插入数据效率对比

在线咨询 来源:admin

1、一个一个插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/**
     * 向表中插入数据
     *
     * @param openHelper
     * @param appInfo
     * @return
     */
    public static boolean insert(SQLiteOpenHelper openHelper,
            RemoteAppInfo appInfo) {
        if (null == appInfo) {
            return true;
        }
        SQLiteDatabase db = null;
        try {
            db = openHelper.getWritableDatabase();
            ContentValues values = appInfo.getContentValues();
            return -1 != db.insert(RemoteDBHelper.TABLE_APP_REMOTE, null,
                    values);
        catch (Exception e) {
            e.printStackTrace();
        finally {
            if (null != db) {
                db.close();
            }
        }
        return false;
    }
 
 
    for (RemoteAppInfo remoteAppInfo : list) {
                    RemoteDBUtil.insert(helper, remoteAppInfo);
                }

耗时:106524ms,也就是106s

 

2、 开启事务批量插入,使用

SqliteDateBase中的

insert(String table, String nullColumnHack, ContentValues values)

方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/**
 * 向表中插入一串数据
 *
 * @param openHelper
 * @param appInfo
 * @return 如果成功则返回true,否则返回flase
 */
public static boolean insert(SQLiteOpenHelper openHelper,
        List<RemoteAppInfo> list) {
    boolean result = true;
    if (null == list || list.size() <= 0) {
        return true;
    }
    SQLiteDatabase db = null;
 
    try {
        db = openHelper.getWritableDatabase();
        db.beginTransaction();
        for (RemoteAppInfo remoteAppInfo : list) {
            ContentValues values = remoteAppInfo.getContentValues();
            if (db.insert(RemoteDBHelper.TABLE_APP_REMOTE, null, values) < 0) {
                result = false;
                break;
            }
        }
        if (result) {
            db.setTransactionSuccessful();
        }
    catch (Exception e) {
        e.printStackTrace();
        return false;
    finally {
        try {
            if (null != db) {
                db.endTransaction();
                db.close();
            }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
    return true;
}

耗时:2968ms

 

3、 开启事务批量插入,使用

SQLiteStatement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
/**
     * 第二种方式批量插入(插入1W条数据耗时:1365ms)
     * @param openHelper
     * @param list
     * @return
     */
    public static boolean insertBySql(SQLiteOpenHelper openHelper,
            List<RemoteAppInfo> list) {
        if (null == openHelper || null == list || list.size() <= 0) {
            return false;
        }
        SQLiteDatabase db = null;
        try {
            db = openHelper.getWritableDatabase();
            String sql = "insert into " + RemoteDBHelper.TABLE_APP_REMOTE + "("
                    + RemoteDBHelper.COL_PKG_NAME + ","// 包名
                    + RemoteDBHelper.COL_USER_ACCOUNT + ","// 账号
                    + RemoteDBHelper.COL_APP_SOURCE + ","// 来源
                    + RemoteDBHelper.COL_SOURCE_UNIQUE + ","// PC mac 地址
                    + RemoteDBHelper.COL_MOBILE_UNIQUE + ","// 手机唯一标识
                    + RemoteDBHelper.COL_IMEI + ","// 手机IMEI
                    + RemoteDBHelper.COL_INSTALL_STATUS + ","// 安装状态
                    + RemoteDBHelper.COL_TRANSFER_RESULT + ","// 传输状态
                    + RemoteDBHelper.COL_REMOTE_RECORD_ID // 唯一标识
                    ") " "values(?,?,?,?,?,?,?,?,?)";
            SQLiteStatement stat = db.compileStatement(sql);
            db.beginTransaction();
            for (RemoteAppInfo remoteAppInfo : list) {
                stat.bindString(1, remoteAppInfo.getPkgName());
                stat.bindString(2, remoteAppInfo.getAccount());
                stat.bindLong(3, remoteAppInfo.getFrom());
                stat.bindString(4, remoteAppInfo.getFromDeviceMd5());
                stat.bindString(5, remoteAppInfo.getMoblieMd5());
                stat.bindString(6, remoteAppInfo.getImei());
                stat.bindLong(7, remoteAppInfo.getInstallStatus());
                stat.bindLong(8, remoteAppInfo.getTransferResult());
                stat.bindString(9, remoteAppInfo.getRecordId());
                long result = stat.executeInsert();
                if (result < 0) {
                    return false;
                }
            }
            db.setTransactionSuccessful();
        catch (Exception e) {
            e.printStackTrace();
            return false;
        finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

耗时:1365ms

微信扫一扫

联系我们

电话:0538-5888018      手机:18605385803
学校地址:擂鼓石大街659-4号6楼(高射炮兵团对过)
乘坐8路、11路、15路、17路、28路公交车到华泰大酒店下车,过红绿灯向东步行200米路北

北大青鸟泰安校区 鲁ICP备15008306号-1