Android : Android Sqlite Performance

on Wednesday, January 28, 2015


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 speeded up 20 times faster!!!



String sql = "INSERT INTO "+StudentHelper.TABLE_NAME+"" +
"("+StudentHelper.FIRSTNAME+","+StudentHelper.LASTNAME+"," +
" "+StudentHelper.GPA+") values(?,?,?)";
SQLiteDatabase db = sh.getWritableDatabase();
db.beginTransaction();
SQLiteStatement stmt = db.compileStatement(sql);

stmt.bindString(1,s.getFirstName());
stmt.bindString(2,s.getLastName());
stmt.bindDouble(3,s.getGpa());

s.setId(stmt.executeInsert());
stmt.clearBindings();

return s.getId();

0 comments:

Post a Comment