sqlite插入速度优化方案

先贴上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,version);
	}

	// 必须要有这一个构造方法
	public MySQLiteOpenHelper(Context context,String name,CursorFactory factory,int version) {
		super(context,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",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,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,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,'无'),"
					+ "('zhangsan','无')";
			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,'无')";
			db.execSQL(sql);
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		db.close();

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!