android sqlite插入效率（转载）

 

 

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

1、 一个一个插入

 

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

耗时：106524ms,也就是106s

 

 

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

SqliteDateBase中的

insert(String table, String nullColumnHack, ContentValues values)

方法

 

 

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

耗时：2968ms

 

 

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

SQLiteStatement

 

 

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

 

 

耗时：1365ms

posted on 2014-03-30 12:56  denkis  阅读(5042)  评论(0)  编辑  收藏  举报

导航