sqlite插入速度优化方案
- - CSDN博客移动开发推荐文章先贴上SQLiteOpenHelper的代码,我们创建测试的表是user,它有四个字段id,name,age,height,remark,测试用的小米2,默认条数为100行. // 必须要有这一个构造方法. // 当数据库第一次创建的时候被调用,. // 当调用getReadableDatabase ()或getWritableDatabase 的时候.
先贴上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 的时候 @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。使用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; }
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; }
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; }
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; }