博客

在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