Android sqlite 3挿入速度最適化案
5415 ワード
SQLiteOpenHelperのコードを貼り付けて、私たちがテストを作成した表はuserで、4つのフィールドid、name、age、height、remark、テスト用の小米2があり、デフォルトの本数は100行です.
1.ContentValueを使用して挿入します.完了時間:4805493666(ナノ秒)
2.基本slq文を使用して挿入します.完了時間:3734808485(ナノ秒)
3.SQLliteStatementを使用して挿入します.完了時間:4754616203(ナノ秒)
4.複数のバーを一度に挿入する方法を使用します.完了時間:245414315(ナノ秒)
5.取引挿入方式を使用します.完了時間:229787881(ナノ秒)
[java] view plain copy print ?
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; }
//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(ナノ秒)
[java] view plain copy print ?
public long insert5(int sum) {
//データベース・オブジェクトの取得
db.beginTransaction();
String sql="insert into user(name,age,height,remark)values('zhangsan',23,1.78,'なし');
}
db.endTransaction();
return after - before;