Android的SQLite的性能 [英] Android Sqlite Performance
问题描述
我一直在做一些实验来衡量的android sqlite的性能。我很失望一点点的结果。我所做的就是插入10.000查询表和花了130-140秒 ，但这些条件的;
1 在省电模式下三星galaxy S3
2 插入数据（或类）具有3串和一个浮点（实为源码）</ P>
3。插入事件正在做的AsyncTask。
4 在AsyncTask的，我是呈现出进展顺利通过定时器的文本将对话框。（System.currentTimeMillis的 - 秒等blala）
类AddStudentsTask扩展的AsyncTask＆LT;太虚，整型，太虚＆GT;
{
ProgressDialog prgDialog;
INT最大值= 10000;
学生S;
长秒= System.currentTimeMillis的（）;
@覆盖
在preExecute保护无效（）{
super.on preExecute（）;
prgDialog =新ProgressDialog（MainActivity.this）;
prgDialog.setProgressStyle（ProgressDialog.STYLE_HORIZONTAL）;
prgDialog.setMessage（秒+）;
prgDialog.setMax（最大）;
prgDialog.setCancelable（假）;
prgDialog.show（）;
}
@覆盖
保护无效onProgressUpdate（整数...值）{
super.onProgressUpdate（）;
prgDialog.setProgress（值[0]）;
sList.add（多个）;
字符串s =（System.currentTimeMillis的（） - 秒）/ 100 +;
如果（s.length（）→2）
S = s.substring（0，s.length（） - 1）+。 + s.charAt（s.length（） - 1）;
否则如果（s.length（）== 2）
S = s.charAt（0）+。 + s.charAt（1）;
prgDialog.setMessage（S +秒通过。）;
}
@覆盖
保护无效doInBackground（虚空......空隙）{
对于（INT一= 0; A＆LT;最大; A ++）
{
随机R =新的随机（）;
S =新的学生（）;
s.setGpa（r.nextFloat（）* 4）;
s.setLastName（asdasd）;
s.setFirstName（哦，我的上帝Fuckig）;
s.setAddress（1sadasd）;
s.setId（sda.insert（多个））;
publishProgress（一）;
}
返回null;
}
@覆盖
保护无效onPostExecute（虚空避免）{
super.onPostExecute（避免）;
prgDialog.dismiss（）;
sa.notifyDataSetChanged（）;
}
}
5。我使用contentValues在helperdb类insertOrThrow方法。 这是老慢code
众长刀片（学生S）
{
SQLiteDatabase DB = sh.getWritableDatabase（）;
ContentValues CV =新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，空，CV））;
返回s.getId（）;
}
6。：此任务在活动的onCreate方法进行。
所以我在做什么错在这里还是期望太高了，从它？难道这些结果好还是不好？的
我能做些什么来提高我的code？
修改
所以我改变了我的插入code到这一点，它缩小到了4.5秒！
公开的ArrayList＆LT;龙＆GT; insertMany（ArrayList的＆LT;学生＆GT;屋顶盒）
{
ArrayList的＆LT;龙＆GT; IDS =新的ArrayList（）;
字符串SQL =INSERT INTO+ StudentHelper.TABLE_NAME ++
（+ StudentHelper.FIRSTNAME +，+ StudentHelper.LASTNAME +，+
？？？+ StudentHelper.GPA +）值（，）;
SQLiteDatabase DB = sh.getWritableDatabase（）;
db.beginTransaction（）;
对于（学生S：屋顶盒）{
SQLiteStatement语句= 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（）;
返回的ID;
}
使用 SQLite的交易速度可达
使用 BEGIN TRANSACTION ＆放大器; END TRANSACTION 作为SQLite的优化
每个SQL语句包含在由SQLite的运行一个新的事务块，默认情况下。 Sowhen您执行基本的数据库操作，如插入，一个事务块将被创建并包裹着它。
放生的SQLite运行管理交易对你来说是最好只有一个数据集的程序执行只有一个DB操作。但是，如果你正在做大量的数据库操作（比如插入内循环），这成为非常昂贵，因为它需要重新打开，写入和关闭日志文件的每个语句。 你可以参考
-
<一个href="https://www.$c$cofaninja.com/2013/12/android-sqlite-transaction-tutorial.html">https://www.$c$cofaninja.com/2013/12/android-sqlite-transaction-tutorial.html
-
<一个href="http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/">http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/
-
<一个href="http://www.android-app-market.com/sqlite-optimization-in-android-programming-sqlite-optimization-in-android-apps.html">http://www.android-app-market.com/sqlite-optimization-in-android-programming-sqlite-optimization-in-android-apps.html
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. Samsung galaxy s3 in power saving mode
2. Inserted data(or class)has 3 strings and one float(real for sqlite)
3. Insert event is being done in asynctask.
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 Fuckig 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. 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. This task is done in onCreate method of activity.
So what am i doing wrong here or expecting too much from it? Are these results ok or bad?
What can i do to improve my code?
EDIT
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;
}
Use SQLite transaction for speed up
Use BEGIN TRANSACTION & END TRANSACTION for SQLite Optimization
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.
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
https://www.codeofaninja.com/2013/12/android-sqlite-transaction-tutorial.html
http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/
这篇关于Android的SQLite的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
