Android 4.2中sqlite操作問題(二)：某些情況下顯式啟用事務能提高操作效率

在sqlite插入資料的時候預設一條語句就是一個事務，有多少條資料就有多少次磁碟操作

如果需要頻繁的執行sql語句，可以顯式的開啟事務，提高效率。

dataBase.beginTransaction();       //手動設定開始事務

//資料插入操作迴圈

dataBase.setTransactionSuccessful();       //設定交易處理成功，不設定會自動復原不提交

dataBase.endTransaction();       //處理完成

 

 

這篇文章分析的很透徹，推薦一下：

android中SQLite資料庫中用insert同時插入多條記錄的方法和效率比較

 在Android開發中我們常會遇到這樣一種情況：在建立一個SQLite資料庫時，想同時插入大量資料。那麼應該怎麼做呢？

下面筆者以插入20條記錄為例：

1.   將同時插入大量的資料寫成一條SQL語句
2.  最笨的方法用insert語句一條一條的輸入
3. 使用事務

代碼如下：

public class DateBaseOpenHelper extends SQLiteOpenHelper {

     public static final String DBNAME="radiomap";
     public static final int VERSION=1;
     public DateBaseOpenHelper(Context context){
      super(context,DBNAME,null,VERSION);
     }
 @Override
 public void onCreate(SQLiteDatabase db) {
  // TODO Auto-generated method stub
       db.execSQL("create table radiomap(location varchar(20),ap1 int,ap2 int)");
       long a=System.currentTimeMillis();
       db.execSQL("insert into radiomap(location,ap1,ap2) select 'x=1,y=1',-80,-73 " +
         "union all select 'x=2,y=3',80,40 union all select 'x=3,y=5',30,20 "+
         "union all select 'x=4,y=5',3,2 union all select 'x=30,y=50',30,20 union all select 'x=3,y=5',40,20"
         +" union all select 'x=3,y=5',6,20 union all select 'x=3,y=5',6,7 union all select 'x=3,y=5',7,8 union all select 'x=3,y=5',8,9 union all select 'x=3,y=5',9,9" +
           " union all select 'x=3,y=5',3,5 union all select 'x=3,y=5',7,20 union all select 'x=3,y=5',4,20 union all select 'x=3,y=5',5,20 union all select 'x=3,y=5',6,20" +
           " union all select 'x=3,y=5',3,6 union all select 'x=3,y=5',7,7 union all select 'x=3,y=5',3,8 union all select 'x=3,y=5',8,2");
       long b=System.currentTimeMillis();
       long c=b-a;
       Log.i("LocationActivity", String.valueOf(c));
       
       a=System.currentTimeMillis();
       db.beginTransaction();
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',1,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',1,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',1,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',2,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',2,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',3,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',3,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',3,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',4,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=7',5,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',4,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',5,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',5,5)");
       db.setTransactionSuccessful();
       db.endTransaction();
       b=System.currentTimeMillis();
       Log.i("LocationActivity", String.valueOf(b-a));
        
       a=System.currentTimeMillis();
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=7,y=8',7,8)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=8,y=9',8,9)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=9,y=10',9,10)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',2,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',2,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',3,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',3,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',3,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',4,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,4)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',4,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',5,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=7',6,5)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',5,7)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',6,3)");
       db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',6,5)");
       b=System.currentTimeMillis();
       Log.i("LocationActivity", String.valueOf(b-a));
 }

 

第一種方式及：

insert into 表名(列名1,列名2)    
 select  值1,值2
 union all  
 select 值1,值2  
 union all  
 select 值1,值2 

以上三種方式測試結果，及運行效率：

第一種方式為9ms

用事務的為：86ms

第三種直接用insert插入的為：29ms

這是因為本次測試用了20條資料，所以用事務的反而比不用的開銷大時間長。若1000條以上則明顯快於直接用insert插入的。

 

 

今天有個朋友測試 SQLite，然後得出的結論是：SQLite 效率太低，批量插入1000條記錄，居然耗時2分鐘！
下面是他發給我的測試代碼。我暈~~~~~~
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Data.SQLite;
//建立資料庫檔案
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
DbProviderFactory factory=SQLiteFactory.Instance;
using(DbConnection conn=factory.CreateConnection())
{
//串連資料庫
conn.ConnectionString="Data Source=test1.db3";
conn.Open();
//建立資料表
stringsql="create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
DbCommand cmd=conn.CreateCommand();
cmd.Connection=conn;
cmd.CommandText=sql;
cmd.ExecuteNonQuery();
//添加參數
cmd.Parameters.Add(cmd.CreateParameter());
//開始計時
Stopwatch watch=newStopwatch();
watch.Start();
//連續插入1000條記錄
for(inti=0; i<1000; i++)
{
cmd.CommandText="insert into [test1] ([s]) values (?)";
cmd.Parameters[0].Value=i.ToString();
cmd.ExecuteNonQuery();
}
//停止計時
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
哎~~~~一個常識性的錯誤，我加幾行代碼 (新增代碼標記"// <-------------------")。
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Data.SQLite;
//建立資料庫檔案
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
DbProviderFactory factory=SQLiteFactory.Instance;
using(DbConnection conn=factory.CreateConnection())
{
//串連資料庫
conn.ConnectionString="Data Source=test1.db3";
conn.Open();
//建立資料表
stringsql="create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
DbCommand cmd=conn.CreateCommand();
cmd.Connection=conn;
cmd.CommandText=sql;
cmd.ExecuteNonQuery();
//添加參數
cmd.Parameters.Add(cmd.CreateParameter());
//開始計時
Stopwatch watch=newStopwatch();
watch.Start();
DbTransaction trans=conn.BeginTransaction();//<-------------------
try
{
//連續插入1000條記錄
for(inti=0; i<1000; i++)
{
cmd.CommandText="insert into [test1] ([s]) values (?)";
cmd.Parameters[0].Value=i.ToString();
cmd.ExecuteNonQuery();
}
trans.Commit();//<-------------------
}
catch
{
trans.Rollback();//<-------------------
throw;//<-------------------
}
//停止計時
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
執行一下，耗時0.2秒。這差距是不是太大了點？
為什麼只是簡單啟用了一個事務會有這麼大的差距呢？很簡單，SQLite 預設為每個操作啟動一個事務，那麼原代碼1000次插入起碼開啟了1000個事務，"事務開啟 + SQL 執行 + 事務關閉"自然耗費了大量的時間，這也是後面顯示啟動事務後為什麼如此快的原因。其實這是資料庫操作的基本常識，大家要緊記，不好的代碼效率差的不是一點半點鐘。

 

 

最近在做android項目的時候遇到一個問題，應用程式初始化時需要批量的向sqlite中插入大量數，導致應用啟動過慢。
android使用的是sqlite資料庫，sqlite是比較輕量級的資料庫，在Google了之後發現，sqlite交易處理的問題，在sqlite插入資料的時候預設一條語句就是一個事務，有多少條資料就有多少次磁碟操作。我的應用程式初始5000條記錄也就是要5000次讀寫磁碟操作。

解決方案：

添加交易處理，把5000條插入作為一個事務

dataBase.beginTransaction();       //手動設定開始事務

//資料插入操作迴圈

dataBase.setTransactionSuccessful();       //設定交易處理成功，不設定會自動復原不提交

dataBase.endTransaction();       //處理完成

 

SQLite的資料庫本質上來講就是一個磁碟上的檔案，所以一切的資料庫操作其實都會轉化為對檔案的操作，而頻繁的檔案操作將會是一個很好時的過程，會極大地影響資料庫存取的速度。
例如：向資料庫中插入100萬條資料，在預設的情況下如果僅僅是執行
sqlite3_exec(db, “insert into name values ‘lxkxf', ‘24'; ”,0,0,&zErrMsg);
將會重複的開啟關閉資料庫檔案100萬次，所以速度當然會很慢。因此對於這種情況我們應該使用“事務”。
具體方法如下：在執行SQL語句之前和SQL語句執行完畢之後加上
rc=sqlite3_exec(db,"BEGIN;",0,0,&zErrMsg);
//執行SQL語句
rc=sqlite3_exec(db,"COMMIT;",0,0,&zErrMsg);
這樣SQLite將把全部要執行的SQL語句先緩衝在記憶體當中，然後等到COMMIT的時候一次性的寫入資料庫，這樣資料庫檔案只被開啟關閉了一次，效率自然大大的提高。有一組資料對比：
測試1:1000INSERTs
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');
...995lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');
SQLite2.7.6:
13.061
SQLite2.7.6(nosync):
0.223

測試2: 使用事務25000INSERTs
BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');
...24997lines omitted
INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');
INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');
COMMIT;
SQLite2.7.6:
0.914
SQLite2.7.6(nosync):
0.757

可見使用了事務之後卻是極大的提高了資料庫的效率。但是我們也要注意，使用事務也是有一定的開銷的，所以對於資料量很小的操作可以不必使用，以免造成而外的消耗。

 http://hi.baidu.com/duxikuan/item/f75583dd61d54a3be3108f8d