现在做的App使用SQLite保存本地数据,通过OrmLite框架进行数据库的操作。由于本地保存的数据量较大,需要优化SQLite插入数据的性能。查阅了OrmLite官方文档,发现OrmLite自带批任务处理的方法,下面先比较一下批处理任务和单任务的效率差别。下面是需要插入数据库的Article表,包含6列数据,测试插入1万条Article的耗时。
package OrmLite.Bean;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
@DatabaseTable(tableName = "tb_article")
public class Article {
@DatabaseField(generatedId = true)
private int id;
@DatabaseField
private String title;
@DatabaseField
private String content;
@DatabaseField
private String author;
@DatabaseField
private int year;
@DatabaseField
private int count;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getYear() {
return year;
}
public void setYear(int year) {
this.year = year;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
}
分别通过两种方法向SQLite插入数据,首先是单条插入,代码如下:
public void addSingleTask(List<Article> list) {
try {
for (Article article : list) {
articleDaoOpe.create(article);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
批任务函数如下,使用了DAO中的callBatchTasks(Callable callable)方法:public void addBatchTask(final List<Article> list) {
try {
articleDaoOpe.callBatchTasks(new Callable<Void>() {
@Override
public Void call() throws Exception {
for (Article article : list) {
articleDaoOpe.create(article);
}
return null;
}
});
} catch (Exception e) {
e.printStackTrace();
}
}
两者的插入时间对比如下(单位:毫秒):
addSingleTask | addBatchTask |
---|---|
55947 | 1513 |
可以看到,批处理任务对SQL操作的效率提高非常大,那这背后的原因是什么?
首先来看一下文档里对callBatchTasks()方法的描述:
Call the call-able that will perform a number of batch tasks. This is for perfor-mance when you want to run a number of database operations at once -- maybe loading data from a file . This will turn o what databases call "auto-commit" mode, run the call-able and then re-enable "auto-commit". If auto-commit is not supported then it will try to use a database transaction to speed up the tasks.
关系型数据库事物必须具备ACID的特性,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。“auto-commit”模式表示每条SQL语句执行完毕后把它作出的修改立刻提交给数据库并使之永久化,事实上,这相当于把每一条语句都隐含地当做一个事务来执行。这样保证了数据库的ACID,但付出的代价就是降低SQL语句的执行效率。开启批任务处理之后,OrmLite关闭了数据库的“auto-commit”模式,在全部语句执行完之后才提交一次,因此带来SQL语句执行效率的大幅度提高,但执行的过程中如果出问题,这次批处理所有的插入数据会被回滚清除。