SQLite - 在Android应用中加速4,000行插入 [英] SQLite - Speed up 4,000 row insert in Android app
问题描述
public synchronized void updateStop(ArrayList< StopBean> beanList){
SQLiteDatabase db = getWritableDatabase();
String query =DELETE FROM'stop';
db.execSQL(查询);
for(StopBean bean:beanList){
String atco_code = bean.getAtco_code();
String name = bean.getName();
String locality = bean.getLocality();
String bearing = bean.getBearing();
String latitude = bean.getLatitude()+;
String longitude = bean.getLongitude()+;
ContentValues values = new ContentValues();
values.put(atco_code,atco_code);
values.put(name,name);
values.put(locality,locality);
values.put(方位,轴承);
values.put(纬度,纬度);
values.put(经度,经度);
db.insert(stop,null,values);
}
db.close();
}
目前我们的应用程序通过上述方法插入4,000行。这种方法的问题是实际执行需要10-15秒。显然,这对于简单的4,000行插入来说太长了。
如何更改此方法以便大大加快这些插入的执行时间?
使用 beginTransaction()
,<$>将整个事物包裹在事务中c $ c> endTransaction()和 markTransactionAsSuccessful()
。现在,你每次插入都要做一个事务,这意味着每个插入一个磁盘I / O脉冲,这将非常慢。
除此之外:
-
不要在循环的每次传递中分配新的
ContentValues
/ p> -
摆脱局部变量(例如
atco_code
),以防它们没有被优化掉 -
使用更有效的方法将数字转换为字符串而不是(例如,
Double.toString()
-
尝试将纯SQL
INSERT
语句编译为SQLiteStatement
viacompileStatement()
,看看这是否比调用insert()
更好
如果您仍然不满意,请使用Traceview并确定其中的位置花费你的时间。
public synchronized void updateStop(ArrayList<StopBean> beanList){
SQLiteDatabase db = getWritableDatabase();
String query = "DELETE FROM 'stop'";
db.execSQL(query);
for(StopBean bean : beanList){
String atco_code = bean.getAtco_code();
String name = bean.getName();
String locality = bean.getLocality();
String bearing = bean.getBearing();
String latitude = bean.getLatitude()+"";
String longitude = bean.getLongitude()+"";
ContentValues values = new ContentValues();
values.put("atco_code", atco_code);
values.put("name", name);
values.put("locality", locality);
values.put("bearing", bearing);
values.put("latitude", latitude);
values.put("longitude", longitude);
db.insert("stop", null, values);
}
db.close();
}
Currently our application inserts 4,000 rows via the method above. The problem with this method is that it takes 10-15 seconds to actually execute. Clearly this is far too long for a simple 4,000 row insert.
How can I change this method so that it vastly speeds up the execution time of these inserts?
Wrap the whole thing in a transaction, using beginTransaction()
, endTransaction()
, and markTransactionAsSuccessful()
. Right now, you are doing one transaction per insert, which means one pulse of disk I/O per insert, and that will be very slow.
Beyond that:
Don't allocate a fresh
ContentValues
in each pass of the loopGet rid of the local variables (e.g.,
atco_code
), in case they are not being optimized awayUse more efficient ways of converting a number to a string than "" (e.g.,
Double.toString()
Experiment with compiling a plain SQL
INSERT
statement to aSQLiteStatement
viacompileStatement()
, and see if that is better than just callinginsert()
If you are still not happy, use Traceview and determine where the rest of your time is being spent.
这篇关于SQLite - 在Android应用中加速4,000行插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!