sqlite挿入速度最適化スキーム

SQLiteOpenHelperのコードを貼り付けて、私たちがテストを作成した表はuserで、4つのフィールドid、name、age、height、remark、テスト用の小米2があり、デフォルトの本数は100行です.

//MySQliteOpenHelper SQLite , 
//1.getReadableDatabase(),getWritableDatabase() SQLiteDatatbase ,
// 
//2. onCreate() onUpgrade() ， 
public class MySQLiteOpenHelper extends SQLiteOpenHelper {

	public static final String DATABASE_NAME = "test.db";

	public static final String TAG = "MySQLiteOpenHelper";

	public MySQLiteOpenHelper(Context context) {
		this(context, DATABASE_NAME, null, 1);
	}

	public MySQLiteOpenHelper(Context context, int version) {
		this(context, DATABASE_NAME, null, version);
	}

	//  
	public MySQLiteOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	//  ,
	//  getReadableDatabase () getWritableDatabase  
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		Log.d(TAG, "onCreate");

		String sql = "create table user(id integer primary key autoincrement,"
				+ "name varchar(20)," + "age integer," + "height long,"
				+ "remark varchar(12))";
		db.execSQL(sql);
	}

	public void close() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("drop table user");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.d(TAG, "onUpgrade");
	}

}

1.ContentValueを使用して挿入します.完了時間:4805493666(ナノ秒)

	/**
	 * ContentValues 
	 * 
	 * @param sum
	 * @return
	 */
	public long insert1(int sum) {
		long before = System.nanoTime();

		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);
		//  
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		for (int i = 0; i < sum; i++) {
			ContentValues cv = new ContentValues();
			cv.put("name", "zhangsan");
			cv.put("age", "23");
			cv.put("height", 1.78);
			cv.put("remark", " ");
			db.insert("user", null, cv);
		}

		db.close();
		long after = System.nanoTime();
		return after - before;

	}

2.基本slq文を使用して挿入します.完了時間:3734808485(ナノ秒)

public long insert2(int sum) {
		long before = System.nanoTime();

		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);
		//  
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		for (int i = 0; i < sum; i++) {
			String sql = "insert into user(name,age,height,remark) values('zhangsan',23,1.78,' ')";
			db.execSQL(sql);
		}
		db.close();

		long after = System.nanoTime();
		return after - before;
	}

3.SQLliteStatementを使用して挿入します.完了時間:4754616203(ナノ秒)

public long insert3(int sum) {
		long before = System.nanoTime();
		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);
		//  
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		String sql = "insert into user(name,age,height,remark) values(?,?,?,?)";
		SQLiteStatement stmt = db.compileStatement(sql);
		for (int i = 0; i < sum; i++) {
			stmt.clearBindings();
			stmt.bindString(1, "zhangsan");
			stmt.bindLong(2, 23);
			stmt.bindLong(3, 178);
			stmt.bindString(4, " ");
			stmt.execute();
		}
		db.close();

		long after = System.nanoTime();
		return after - before;
	}

4.複数のバーを一度に挿入する方法を使用します.完了時間:245414315(ナノ秒)

public long insert4(int sum) {
		long before = System.nanoTime();

		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);
		//  
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		for (int i = 0; i < sum / 10; i++) {
			String sql = "insert into user(name,age,height,remark) values('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' '),"
					+ "('zhangsan',23,1.78,' ')";
			db.execSQL(sql);
		}
		db.close();

		long after = System.nanoTime();
		return after - before;
	}

5.取引挿入方式を使用します.完了時間:229787881(ナノ秒)

public long insert5(int sum) {
		long before = System.nanoTime();

		MySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(MainActivity.this);
		//  
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		for (int i = 0; i < sum; i++) {
			String sql = "insert into user(name,age,height,remark) values('zhangsan',23,1.78,' ')";
			db.execSQL(sql);
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		db.close();

		long after = System.nanoTime();
		return after - before;
	}