SQL批量插入数据

SQL批量插入数据

(1)

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: 1000 INSERTs

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'); ... 995 lines 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');

SQLite 2.7.6:

13.061

SQLite 2.7.6

(nosync):

0.223

测试2: 使用事务 25000

INSERTs

BEGIN;

CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100)); INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two'); ... 24997 lines 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;

SQLite 2.7.6:

SQL批量插入数据

0.914

SQLite 2.7.6

(nosync):

0.757

可见使用了事务之后却是极大的提高了数据库的效率。但是我们也要注意，使用事务也是有一定的开销的，所以对于数据量很小的操作可以不必使用，以免造成而外的消耗。

android中的方法，

解决方法：

添加事务处理，把5000条插入作为一个事务

dataBase.beginTransaction(); //手动设置开始事务

//数据插入操作循环

dataBase.setTransactionSuccessful(); //设置事务处理成功，不设置会自动回滚不提交 dataBase.endTransaction(); //处理完成

PS，在android中一般是使用ContentResolver来包装下sqlite，自带一个批量插入的方法

(2)

我正在开发一个Android程序, 它使用SQLite存储大约6000行的数据, 这些数据会定期从网上更新. 在模拟器上, 从网络获取和解析CSV格式的数据所花的时间大概是20秒, 但是把数据插入的数据库的时间是71秒.

因为数据更新的操作差不多一个星期才有一次, 因此我认为1分多钟的操作时间是可以接受的. 但当我把程序在真机上跑的时候, 6000行的插入时间让我吓了一跳 -- 478秒, 差不多8分钟. 很奇怪, 一般来说, 真机要比模拟器快, 何况我用的是Sanmsung Galaxy S - 当时最快的Android设备之一. 这是我第一次使用SQLite, 我想我下一步得优化插入操作了.

Sql代码

String sql = "INSERT INTO table (number, nick) VALUES (?, ?)";

SQLiteStatement stmt = http://doc.guandang.netpileStatement(sql);

for (int i = 0; i < values.size(); i++) {

stmt.bindString(1, values.get(i).number);

stmt.bindString(2, values.get(i).nick);

stmt.execute();

stmt.clearBindings();

}

有了上面的改动后, 我在模拟器上测试, 时间从71秒减少到56秒, 但是在真机上测的时候, 时间反而多了几秒, 我反复测了几次, 还是差不多一样的结果.

进一步研究, 我从SQLite的一个文档页面看到“PRAGMA synchronous = OFF” 将会告诉

SQL批量插入数据

SQLite, 当它把数据传入操作系统的时候, 不要立即同步. 这个设置让插入时间从71秒减少的50秒, 在真机上的结果是361秒, 差不多快2分钟.

感觉到找到路子之后, 我认为是文件系统减慢了插入操作, 我在网上找到了很多关于Galaxy S I/O 性能问题的参考 - 所有的都与RFS文件系统相关. 对于SQLite而言, 它每插入一次数据, 都会执行一下fsync, 以保证数据写入了磁盘. 再看SQLite文档, 我发现用transactions能够将数据保存在内存中, 只有在commit时候才写入文件系统. 因此我改动如下:

Java代码

String sql = "INSERT INTO table (number, nick) VALUES (?, ?)";

db.beginTransaction();

SQLiteStatement stmt = http://doc.guandang.netpileStatement(sql);

for (int i = 0; i < values.size(); i++) {

stmt.bindString(1, values.get(i).number);

stmt.bindString(2, values.get(i).nick);

stmt.execute();

stmt.clearBindings();

}

db.setTransactionSuccessful();

db.endTransaction();

结合transactions和 compiled statements后, 性能有了巨大的提升: 从71秒到不可置信的5秒! 在Galaxy上的结果更是牛逼: 从478秒到1.5秒!

结论:

- 除非你只执行单次的insert, 或者你需要数据立即写入文件系统, 不然的话就用transactions

- 保证你的程序在真机上测试过, 最好是多台机器上测.

- 我上面的性能提升只在Samsung Galaxy S上测过, 不同的机器可能还是会有性能问题.

(3) 中在sqlite插入数据的时候默认一条语句就是一个事务，因此如果存在上万条数据插入的话，那就需要执行上万次插入操作，操作速度可想而知。因此在Android中插入数据时，使用批量插入的方式可以大大提高插入速度。

有时需要把一些数据内置到应用中，常用的有以下2种方式：其一直接拷贝制作好的SQLite数据库文件，其二是使用系统提供的数据库，然后把数据批量插入。我更倾向于使用第二种方式：使用系统创建的数据库，然后批量插入数据。批量插入数据也有很多方法，那么那种方法更快呢，下面通过一个demo比较一下各个方法的插入速度。

1、使用db.execSQL(sql)

这里是把要插入的数据拼接成可执行的sql语句，然后调用db.execSQL(sql)方法执行插入。

SQL批量插入数据

2、使用db.insert("table_name", null, contentValues)

这里是把要插入的数据封装到ContentValues类中，然后调用db.insert()方法执行插入。

3、使用InsertHelper类

这个类在API 17中已经被废弃了

SQL批量插入数据

4、使用SQLiteStatement

…… 此处隐藏：2450字，全部文档内容请下载后查看。喜欢就下载吧 ……