Android - 加快在数据库中插入数据 [英] Android - Speed up inserting data in database
问题描述
我目前有一个 CSV 文件,我正在解析并尝试将数据插入到 android 数据库中.我遇到的问题是插入所有数据花费的时间太长.这是一个很好的数据量,但我觉得它不应该需要 20 分钟左右才能完成.
I currently have a CSV file that I parse and am trying to insert the data into the android database. The problem I am having is that it is taking way too long to insert all of the data. It's a good amount of data but I feel like it shouldn't take 20min or so to complete.
基本上,我创建了我的数据库,然后开始解析.在解析每个单独的 CSV 行时,我抓取所需的数据并将其插入到数据库中.总共有大约 40000 行.
Basically, I create my database, then begin the parsing. While parsing through each individual CSV row, I grab the required data and insert it into the database. In total there are around 40000 rows.
有什么方法可以加快这个过程吗?我尝试过批量插入,但它从来没有真正帮助过(除非我做错了).
Is there any way I can speed up this process? I have tried batch inserts but it never really helped (unless I did it wrong).
下面的代码.
谢谢.
DatabaseHelper(根据每个 csv 行中的数据量,我有两个插入命令):
DatabaseHelper (i have two insert commands based on the amount of data in each csv row):
// add zipcode
public void add9Zipcode(String zip, String city, String state, String lat,
String longi, String decom) {
// get db and content values
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
db.beginTransaction();
try{
// add the values
values.put(KEY_ZIP, zip);
values.put(KEY_STATE, state);
values.put(KEY_CITY, city);
values.put(KEY_LAT, lat);
values.put(KEY_LONG, longi);
values.put(KEY_DECOM, decom);
// execute the statement
db.insert(TABLE_NAME, null, values);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
db.close();
}
public void add12Zipcode(String zip, String city, String state, String lat,
String longi, String decom, String tax, String pop, String wages) {
// get db and content values
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
db.beginTransaction();
try{
// add the values
values.put(KEY_ZIP, zip);
values.put(KEY_STATE, state);
values.put(KEY_CITY, city);
values.put(KEY_LAT, lat);
values.put(KEY_LONG, longi);
values.put(KEY_DECOM, decom);
values.put(KEY_TAX, tax);
values.put(KEY_POP, pop);
values.put(KEY_WAGES, wages);
// execute the statement
db.insert(TABLE_NAME, null, values);
db.setTransactionSuccessful();
} finally{
db.endTransaction();
}
db.close();
}
解析文件:
public void parse(ArrayList<String> theArray, DatabaseHandler db) {
String[] data = null;
// while loop to get split the data into new lines
// for loop to split each string in the array list of zipcodes
for (int x = 0; x < theArray.size(); x++) {
if(x == 10000 || x == 20000 || x == 30000 || x == 40000){
Log.d(TAG, "x is 10k, 20k, 30k, 40k");
}
// split string first into an array
data = theArray.get(x).split(",");
// separate based on the size of the array: 9 or 12
if (data.length == 9) {
db.add9Zipcode(data[0], data[2], data[3], data[5], data[6],
data[8]);
} else if (data.length == 12) {
db.add12Zipcode(data[0], data[2], data[3], data[5], data[6],
data[8], data[9], data[10], data[11]);
/*
* theZip.zip = data[0]; theZip.city = data[2]; theZip.state =
* data[3]; theZip.lat = data[5]; theZip.longi = data[6];
* theZip.decom = data[8]; theZip. = data[9]; theZip.population
* = data[10]; theZip.wages = data[11];
*/
}
}
推荐答案
参考我之前做的这个答案:Inserting 1000000 rows in sqlite3数据库
Refer to this answer I made previously: Inserting 1000000 rows in sqlite3 database
简而言之,使用 InsertHelper
并在每个事务中执行多次插入 - 除非您做了一些不靠谱的事情,否则速度应该会明显提高.
In short, use an InsertHelper
and do more than one insert per transaction - unless you did something wonky, the speed increase should be noticeable.
简而言之:
In short:
- 您的
SQLiteOpenHelper
应该是在整个应用程序中使用的单例. - 不要在
SQLiteDatabase
实例上调用close()
- 它缓存在SQLiteOpenHelper
中,每次关闭时都会强制执行帮手重新打开它. - 批量插入,在调用
addZipCode
方法之外启动事务,并在完成所有插入后将其标记为成功 - 然后提交事务. - 使用
InsertHelper
- 它会将插入正确格式化为准备好的语句,并且很好且可重复使用. - 请注意同步访问数据库 - 除非您打算在 UI 线程上完成所有数据库工作(不建议这样做) - 您需要启用锁定或保护对数据库的访问以避免并发访问.
- Your
SQLiteOpenHelper
should be a singleton used across your entire application. - Don't go around calling
close()
on yourSQLiteDatabase
instance - it's cached in theSQLiteOpenHelper
and every time you close you force the helper to reopen it. - Batch your inserts, start a transaction outside the call to the
addZipCode
methods and mark it as successful after you've done all the inserts - then commit the transaction. - Use an
InsertHelper
- it will format the insert properly as a prepared statement and is nice and reusable. - Be mindful of synchronizing access to the database - unless you intend to do all your database work on the UI-thread (which is not recommended) - you either need to enable locking or guard access to the database to avoid concurrent access.
这篇关于Android - 加快在数据库中插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!