sqlite插入速度优化方案 申请加精

本帖最后由 yxmshaka 于 2013-4-10 17:52 编辑 我们创建测试的表是user，它有四个字段id,name,age,height,remark，测试用的小米2，默认条数为100行。 ? 代码片段，双击复制 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 //MySQliteOpenHelper作为一个访问SQLite的帮助类,提供两方面的功能 //1.getReadableDatabase(),getWritableDatabase()可以获得SQLiteDatatbase对象, //对这个对象进行相关操作 //2.提供了onCreate()和onUpgrade()两个回调函数，允许我们在创建和升级数据库时进行操作 public class MySQLiteOpenHelper extends SQLiteOpenHelper { public static final String DATABASE_NAME = "meacs.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(纳秒) ? 代码片段，双击复制 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 /** * 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(纳秒) ? 代码片段，双击复制 01 02 03 04 05 06 07 08 09 10 11 12 13 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(纳秒) ? 代码片段，双击复制 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 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(纳秒) ? 代码片段，双击复制 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 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(纳秒) ? 代码片段，双击复制 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 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; } 最近在忙cocos2d-x开发的事情，睡得比较晚，临睡前 看到了一篇文章 感觉很好 ，跟大家分享了，这个绝对是精华所在。  刚才有人说转帖要说明出处  又回去找了一遍  找到了  做人要厚道 哈 转自：http://blog.csdn.net/ihrthk/article/details/8741047

回帖推荐

laozhao 查看楼层

实际测试当同时使用事务时，循环内部使用ContentValues更快 ContentValues： 342224121 270477293 488250732 623504637 164733888 149871825 158111570 ------ SQL： 210998534 743438721 241638183 187103272 172088624

yxmshaka 查看楼层

ContentValues快 这个是必然的 但老程序员对t-sql都有情怀的