Android数据库大批量数据插入优化

对比在android中批量插入数据的3中方式对比（各插入1W条数据所花费的时间）：

1、 一个一个插入

 1 public static boolean insert(SQLiteOpenHelper openHelper,  
 2             RemoteAppInfo appInfo) {  
 3         if (null == appInfo) {  
 4             returntrue;  
 5         }  
 6         SQLiteDatabase db = null;  
 7         try {  
 8             db = openHelper.getWritableDatabase();  
 9             ContentValues values = appInfo.getContentValues();  
10             return -1 != db.insert(RemoteDBHelper.TABLE_APP_REMOTE, null,  
11                     values);  
12         } catch (Exception e) {  
13             e.printStackTrace();  
14         } finally {  
15             if (null != db) {  
16                 db.close();  
17             }  
18         }  
19         returnfalse;  
20     }  
21 
22 
23     for (RemoteAppInfo remoteAppInfo : list) {  
24           RemoteDBUtil.insert(helper, remoteAppInfo); 
25         }

耗时：106524ms,也就是106s

2、 开启事务批量插入，使用SqliteDateBase中的insert(String table, String nullColumnHack, ContentValues values)方法

 1 public static boolean insert(SQLiteOpenHelper openHelper,  
 2         List<RemoteAppInfo> list) {  
 3     boolean result = true;  
 4     if (null == list || list.size() <= 0) {  
 5         returntrue;  
 6     }  
 7     SQLiteDatabase db = null;  
 8 
 9     try {  
10         db = openHelper.getWritableDatabase();  
11         db.beginTransaction();  
12         for (RemoteAppInfo remoteAppInfo : list) {  
13             ContentValues values = remoteAppInfo.getContentValues();  
14             if (db.insert(RemoteDBHelper.TABLE_APP_REMOTE, null, values) < 0) {  
15                 result = false;  
16                 break;  
17             }  
18         }  
19         if (result) {  
20             db.setTransactionSuccessful();  
21         }  
22     } catch (Exception e) {  
23         e.printStackTrace();  
24         returnfalse;  
25     } finally {  
26         try {  
27             if (null != db) {  
28                 db.endTransaction();  
29                 db.close();  
30             }  
31         } catch (Exception e) {  
32             e.printStackTrace();  
33         }  
34     }  
35     returntrue;  
36 }

耗时：2968ms

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

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

耗时：1365ms

posted @ 2015-09-08 09:32  残剑_  阅读(6186)  评论(0)  编辑  收藏  举报