android批量插入数据效率对比

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

1、 一个一个插入

 

/**
	 * 向表中插入数据
	 * 
	 * @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)

方法

 

 

	/**
	 * 向表中插入一串数据
	 * 
	 * @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

 

 

/**
	 * 第二种方式批量插入(插入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