Android(安卓)sqlite3插入速度优化方案

先贴上SQLiteOpenHelper的代码，我们创建测试的表是user，它有四个字段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 的时候@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubLog.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");}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubLog.d(TAG, "onUpgrade");}}

1。使用ContentValues插入。完成时间: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) {
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;
}

更多相关文章

随机推荐