Android Sqlite 性能 [英] Android Sqlite Performance
问题描述
我一直在做一些实验来测量 android 上的 sqlite 性能.我对结果有点失望.我所做的是向表中插入 10.000 个查询，花了 130-140 秒 但是在这些条件下;
I have been doing some experiments to measure sqlite performance on android. I was disappointed a little bit with the results. What i did was inserting 10.000 queries to table and it took 130-140 seconds but with these conditions;
1.三星galaxy s3处于省电模式
1. Samsung galaxy s3 in power saving mode
2. 插入的数据(或类)有 3 个字符串和一个浮点数(对于 sqlite 是实数)
2. Inserted data(or class)has 3 strings and one float(real for sqlite)
3. 插入事件正在异步任务中完成.
3. Insert event is being done in asynctask.
4. 在异步任务中，我显示了一个进度对话框，其中包含传递的计时器文本(System.currentTimeMillis - seconds 等)
4. In asynctask, i am showing a progress dialog with passed timer text in it (System.currentTimeMillis - seconds etc blala)
class AddStudentsTask extends AsyncTask<Void,Integer,Void>
{
ProgressDialog prgDialog;
int max = 10000;
Student s;
long seconds = System.currentTimeMillis();
@Override
protected void onPreExecute() {
super.onPreExecute();
prgDialog = new ProgressDialog(MainActivity.this);
prgDialog.setProgressStyle(ProgressDialog.STYLE_HORIZONTAL);
prgDialog.setMessage(seconds+"");
prgDialog.setMax(max);
prgDialog.setCancelable(false);
prgDialog.show();
}
@Override
protected void onProgressUpdate(Integer... values) {
super.onProgressUpdate();
prgDialog.setProgress(values[0]);
sList.add(s);
String s = (System.currentTimeMillis()-seconds)/100+"";
if(s.length()>2)
s = s.substring(0,s.length()-1) + "." + s.charAt(s.length()-1);
else if(s.length() == 2)
s = s.charAt(0) + "." + s.charAt(1);
prgDialog.setMessage(s + " seconds passed.");
}
@Override
protected Void doInBackground(Void... voids) {
for(int a = 0;a< max; a++ )
{
Random r = new Random();
s = new Student();
s.setGpa(r.nextFloat()*4);
s.setLastName("asdasd");
s.setFirstName("Oh My God");
s.setAddress("1sadasd");
s.setId(sda.insert(s));
publishProgress(a);
}
return null;
}
@Override
protected void onPostExecute(Void aVoid) {
super.onPostExecute(aVoid);
prgDialog.dismiss();
sa.notifyDataSetChanged();
}
}
5. 我在 helperdb 类中使用 contentValues 和 insertOrThrow 方法.这是旧的慢代码
5. I am using contentValues with insertOrThrow method in helperdb class. THIS IS OLD SLOW CODE
public long insert(Student s)
{
SQLiteDatabase db = sh.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(StudentHelper.FIRSTNAME,s.getFirstName());
cv.put(StudentHelper.LASTNAME,s.getLastName());
cv.put(StudentHelper.ADDRESS,s.getAddress());
cv.put(StudentHelper.GPA,s.getGpa());
s.setId(db.insertOrThrow(StudentHelper.TABLE_NAME, null, cv));
return s.getId();
}
6.此任务在活动的 onCreate 方法中完成.
6. This task is done in onCreate method of activity.
那么我在这里做错了什么还是对它期望过高?这些结果好还是不好?
我可以做些什么来改进我的代码?
编辑
所以我把我的插入代码改成了这个，它减少到 4.5 秒！！！
So i changed my insert code to this and it reduced to 4.5 seconds!!!
public ArrayList<Long> insertMany(ArrayList<Student> stus)
{
ArrayList<Long> ids = new ArrayList();
String sql = "INSERT INTO "+StudentHelper.TABLE_NAME+"" +
"("+StudentHelper.FIRSTNAME+","+StudentHelper.LASTNAME+"," +
" "+StudentHelper.GPA+") values(?,?,?)";
SQLiteDatabase db = sh.getWritableDatabase();
db.beginTransaction();
for(Student s:stus) {
SQLiteStatement stmt = db.compileStatement(sql);
stmt.bindString(1, s.getFirstName());
stmt.bindString(2, s.getLastName());
stmt.bindDouble(3, s.getGpa());
s.setId(stmt.executeInsert());
ids.add(s.getId());
stmt.clearBindings();
}
db.setTransactionSuccessful();
db.endTransaction();
return ids;
}
推荐答案
使用SQLite事务来加速
使用开始交易 &END TRANSACTION 用于 SQLite 优化
Use BEGIN TRANSACTION & END TRANSACTION for SQLite Optimization
默认情况下，每个 SQL 语句都被 SQLite 运行时包含在一个新的事务块中.因此，当您执行诸如 INSERT 之类的基本数据库操作时，将创建一个事务块并将其包裹起来.
Each SQL statement is enclosed in a new transaction block by SQLite runtime, by default. Sowhen you perform a basic DB operation such as INSERT, a transaction block will be created and wrapped around it.
仅当您的例程仅对数据集执行一项 DB 操作时，才建议让 SQLite 运行时为您管理事务.但是，如果您正在执行大量数据库操作(例如在 for 循环内进行 INSERT)，这将变得非常昂贵，因为它需要为每个语句重新打开、写入和关闭日志文件.你可以参考
Letting SQLite runtime manage the transaction for you is advisable only if your routine performs only one DB operation on a data set. However, if you are doing numerous DB operations (say INSERT inside for loop), this becomes very expensive, since it requires reopening, writing to, and closing the journal file for each statement. You may refer
http://www.androidcode.ninja/android-sqlite-交易教程/
http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/
这篇关于Android Sqlite 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
