Android Sqlite Performance

Question

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 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;
}

I don't see `endTransaction` or `setTransactionSuccessful` if you just added `beginTransaction` in your `insert` method then you missed the whole point. — M-Wajeeh, Jan 28 '15 at 09:24
@M-WaJeEh You are right. I edited my post. Thank you for constructive criticism!! — Mert Serimer, Jan 28 '15 at 09:27
Nope, still wrong. You have to do `beginTransaction` and then 10.000 insertions and then `setTransactionSuccessful` and then `endTransaction`. Modify your `AddStudentsTask` for this to work. — M-Wajeeh, Jan 28 '15 at 09:30
I understand it, yeah you are saying i am reopening 10k times for insertion. I get it thank you=). I edited post for future problem havers to give them some idea about usage. — Mert Serimer, Jan 28 '15 at 09:31
@M-WaJeEh I understanded your point but lazy to change what it should be =)) Ok i edited my post, thank you. — Mert Serimer, Jan 28 '15 at 09:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/69725/discussion-between-m-wajeeh-and-mert-serimer). — M-Wajeeh, Jan 28 '15 at 09:40
See this link,perhaps help you https://stackoverflow.com/questions/38867973/insert-over-6000-recods-in-sqlite-android — , Mar 04 '17 at 06:24
Shouldn't your db.compileStatement(... Be outside of the for loop? I think you only need to call that once and bind, execute, and clearBindings on each iteration of the loop. — user1760150, Jul 04 '19 at 14:32
There is nice performance benchmark on Jason Feinstein's blog: https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-insertions-971aff98eef2 — Mariusz, Feb 20 '22 at 11:21

score 30 · Accepted Answer · edited May 23 '17 at 12:10

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

above just a link nothing else so will you provide some explanation — duggu, Jan 28 '15 at 09:10
Thank you for answer. It speeded up 20 times lol. it took 7.5 seconds — Mert Serimer, Jan 28 '15 at 09:16
I edited my post for future problem havers so they can change their code. — Mert Serimer, Jan 28 '15 at 09:18

score 12 · Answer 2 · edited Apr 02 '20 at 14:23

You can use SQL transactions in Android like this. It's better to insert multiple rows into the database in larger batches then making single commit (write into SQLlite datafile which is very slow) for every inserted row.

public void insert(List<Student> students)
{
    SQLiteDatabase db = sh.getWritableDatabase();
    ContentValues cv = new ContentValues();

    db.beginTransaction();

    try {
        for (Student s : students) {
            cv.put(StudentHelper.FIRSTNAME,s.getFirstName());
            cv.put(StudentHelper.LASTNAME,s.getLastName());
            cv.put(StudentHelper.ADDRESS,s.getAddress());
            cv.put(StudentHelper.GPA,s.getGpa());

            db.insertOrThrow(StudentHelper.TABLE_NAME, null, cv)
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

thanks you so much for this solution. This really improved the performance. — Parmendra Singh, Mar 16 '20 at 03:34

score 2 · Answer 3 · edited May 23 '17 at 12:18

Well, you have so many records and for every record you call getWritableDatabase() and then insert it, which is bad for performance.

You need to use transactions. Look at this question Android SQLite database: slow insertion. Basically you need to call beginTransaction(), do your insertions and then call setTransactionSuccessful() preceded by endTransaction(). You will be amazed to see how much performance gain you will get with this.

Android Sqlite Performance

3 Answers3

Linked