在Android開(kāi)發(fā)中我們常會(huì)遇到這樣一種情況：在建立一個(gè)SQLite數(shù)據(jù)庫(kù)時(shí)，想同時(shí)插入大量數(shù)據(jù)。那么應(yīng)該怎么做呢？
下面筆者以插入20條記錄為例：
- 將同時(shí)插入大量的數(shù)據(jù)寫(xiě)成一條SQL語(yǔ)句
- 最笨的方法用insert語(yǔ)句一條一條的輸入
- 使用事務(wù)
代碼如下：
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
以上三種方式測(cè)試結(jié)果，及運(yùn)行效率：
第一種方式為9ms
用事務(wù)的為：86ms
第三種直接用insert插入的為：29ms
這是因?yàn)楸敬螠y(cè)試用了20條數(shù)據(jù)，所以用事務(wù)的反而比不用的開(kāi)銷大時(shí)間長(zhǎng)。若1000條以上則明顯快于直接用insert插入的。
今天有個(gè)朋友測(cè)試 SQLite，然后得出的結(jié)論是：SQLite 效率太低，批量插入1000條記錄，居然耗時(shí)2分鐘！
下面是他發(fā)給我的測(cè)試代碼。我暈~~~~~~
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Data.SQLite;
//創(chuàng)建數(shù)據(jù)庫(kù)文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
DbProviderFactory factory=SQLiteFactory.Instance;
using(DbConnection conn=factory.CreateConnection())
{
//連接數(shù)據(jù)庫(kù)
conn.ConnectionString="Data Source=test1.db3";
conn.Open();
//創(chuàng)建數(shù)據(jù)表
stringsql="create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
DbCommand cmd=conn.CreateCommand();
cmd.Connection=conn;
cmd.CommandText=sql;
cmd.ExecuteNonQuery();
//添加參數(shù)
cmd.Parameters.Add(cmd.CreateParameter());
//開(kāi)始計(jì)時(shí)
Stopwatch watch=newStopwatch();
watch.Start();
//連續(xù)插入1000條記錄
for(inti=0; i<1000; i++)
{
cmd.CommandText="insert into [test1] ([s]) values (?)";
cmd.Parameters[0].Value=i.ToString();
cmd.ExecuteNonQuery();
}
//停止計(jì)時(shí)
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
哎~~~~一個(gè)常識(shí)性的錯(cuò)誤，我加幾行代碼 (新增代碼標(biāo)記"http:// <-------------------")。
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Data.SQLite;
//創(chuàng)建數(shù)據(jù)庫(kù)文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
DbProviderFactory factory=SQLiteFactory.Instance;
using(DbConnection conn=factory.CreateConnection())
{
//連接數(shù)據(jù)庫(kù)
conn.ConnectionString="Data Source=test1.db3";
conn.Open();
//創(chuàng)建數(shù)據(jù)表
stringsql="create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
DbCommand cmd=conn.CreateCommand();
cmd.Connection=conn;
cmd.CommandText=sql;
cmd.ExecuteNonQuery();
//添加參數(shù)
cmd.Parameters.Add(cmd.CreateParameter());
//開(kāi)始計(jì)時(shí)
Stopwatch watch=newStopwatch();
watch.Start();
DbTransaction trans=conn.BeginTransaction();//<-------------------
try
{
//連續(xù)插入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;//<-------------------
}
//停止計(jì)時(shí)
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
執(zhí)行一下，耗時(shí)0.2秒。這差距是不是太大了點(diǎn)？
為什么只是簡(jiǎn)單啟用了一個(gè)事務(wù)會(huì)有這么大的差距呢？很簡(jiǎn)單，SQLite 缺省為每個(gè)操作啟動(dòng)一個(gè)事務(wù)，那么原代碼1000次插入起碼開(kāi)啟了1000個(gè)事務(wù)，"事務(wù)開(kāi)啟 + SQL 執(zhí)行 + 事務(wù)關(guān)閉"自然耗費(fèi)了大量的時(shí)間，這也是后面顯示啟動(dòng)事務(wù)后為什么如此快的原因。其實(shí)這是數(shù)據(jù)庫(kù)操作的基本常識(shí)，大家要緊記，不好的代碼效率差的不是一點(diǎn)半點(diǎn)。
最近在做android項(xiàng)目的時(shí)候遇到一個(gè)問(wèn)題，應(yīng)用程序初始化時(shí)需要批量的向sqlite中插入大量數(shù)，導(dǎo)致應(yīng)用啟動(dòng)過(guò)慢。
android使用的是sqlite數(shù)據(jù)庫(kù)，sqlite是比較輕量級(jí)的數(shù)據(jù)庫(kù)，在Google了之后發(fā)現(xiàn)，sqlite事務(wù)處理的問(wèn)題，在sqlite插入數(shù)據(jù)的時(shí)候默認(rèn)一條語(yǔ)句就是一個(gè)事務(wù)，有多少條數(shù)據(jù)就有多少次磁盤(pán)操作。我的應(yīng)用初始5000條記錄也就是要5000次讀寫(xiě)磁盤(pán)操作。
解決方法：
添加事務(wù)處理，把5000條插入作為一個(gè)事務(wù)
dataBase.beginTransaction(); //手動(dòng)設(shè)置開(kāi)始事務(wù)
//數(shù)據(jù)插入操作循環(huán)
dataBase.setTransactionSuccessful(); //設(shè)置事務(wù)處理成功，不設(shè)置會(huì)自動(dòng)回滾不提交
dataBase.endTransaction(); //處理完成
SQLite的數(shù)據(jù)庫(kù)本質(zhì)上來(lái)講就是一個(gè)磁盤(pán)上的文件，所以一切的數(shù)據(jù)庫(kù)操作其實(shí)都會(huì)轉(zhuǎn)化為對(duì)文件的操作，而頻繁的文件操作將會(huì)是一個(gè)很好時(shí)的過(guò)程，會(huì)極大地影響數(shù)據(jù)庫(kù)存取的速度。
例如：向數(shù)據(jù)庫(kù)中插入100萬(wàn)條數(shù)據(jù)，在默認(rèn)的情況下如果僅僅是執(zhí)行
sqlite3_exec(db, “insert into name values ‘lxkxf', ‘24'; ”,0,0,&zErrMsg);
將會(huì)重復(fù)的打開(kāi)關(guān)閉數(shù)據(jù)庫(kù)文件100萬(wàn)次，所以速度當(dāng)然會(huì)很慢。因此對(duì)于這種情況我們應(yīng)該使用“事務(wù)”。
具體方法如下：在執(zhí)行SQL語(yǔ)句之前和SQL語(yǔ)句執(zhí)行完畢之后加上
rc=sqlite3_exec(db,"BEGIN;",0,0,&zErrMsg);
//執(zhí)行SQL語(yǔ)句
rc=sqlite3_exec(db,"COMMIT;",0,0,&zErrMsg);
這樣SQLite將把全部要執(zhí)行的SQL語(yǔ)句先緩存在內(nèi)存當(dāng)中，然后等到COMMIT的時(shí)候一次性的寫(xiě)入數(shù)據(jù)庫(kù)，這樣數(shù)據(jù)庫(kù)文件只被打開(kāi)關(guān)閉了一次，效率自然大大的提高。有一組數(shù)據(jù)對(duì)比：
測(cè)試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
測(cè)試2: 使用事務(wù)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
可見(jiàn)使用了事務(wù)之后卻是極大的提高了數(shù)據(jù)庫(kù)的效率。但是我們也要注意，使用事務(wù)也是有一定的開(kāi)銷的，所以對(duì)于數(shù)據(jù)量很小的操作可以不必使用，以免造成而外的消耗。
