Android / SQLite - Fastest insert?

Question

Long story short:

I got a CSV file with something like 8.000 records (and 4 fields). I have to download it and after that process it and insert each record in a sqllite table.

So I do with it a transaction:

    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    try
    {
        String line;
        int i=0;
        do {
            line = buffreader.readLine();

            i++;
            if(i==1)
                continue; //Header of the CSV

            if(line != null)
            {
                String[] values = line.split(";");
                if(values.length != 4 )
                    continue;

                sql = String.format("INSERT INTO TABLE (FIELD_1, FIELD_2, FIELD_3, FIELD_4) VALUES (%s, %s, %s, %s)",
                        values[0],
                        values[1],
                        values[2],
                        values[3]);
                db.execSQL(sql);
            }
        }
        while (line != null);

        db.setTransactionSuccessful();
    }
    catch (SQLiteException ex)
    {
        Log.d(TAG, "Well.. : " + ex.getMessage());
        throw ex;
    }
    finally
    {
        db.endTransaction();
    }

Everything works fine, it takes like 8-9 seconds on my cellphone and other cellphone. Sadly on the Android device where this app have to run ( a white label device with a dualcore processor ) it takes 6-7 MINUTES!!!

Of course my boss is not happy about it, he do agree that on "regular" cellphone with a quadcore process everything is faster but we have to make it working where on this dualcore and 6-7 minutes looks like a problem.. Any idea about how to solve it ?

Use method tracing to determine where you are spending your time. Most likely, the problem is not with the CPU, but with the flash storage, as your designated device may have cheap-and-slow on-board flash. You can probably improve performance a bit by getting rid of `String.format()` and using query parameters (`execSQL("INSERT INTO TABLE (FIELD_1, FIELD_2, FIELD_3, FIELD_4) VALUES (?, ?, ?, ?)`", values)`). Or, have your server generate the SQLite database and download that, instead of a CSV file. — CommonsWare, May 22 '17 at 15:25

score 1 · Accepted Answer · answered May 22 '17 at 15:35

1

1) Separate your processes (file read and db inserts). You need to consume a lower quantity of memory.

2) Insert multiple records: INSERT INTO ... VALUES (1,2,3,4),(5,6,7,8),(9,10,11,12). In this way, you get a lower I/O.

3) Use query parameters

answered May 22 '17 at 15:35

Lucas Moretto

404
1
6
18

First of all thanks for your time :-) > 1) Separate [...] Done, I split the "reading CSV part" and the "insert into SQL" parte. Now inside the DBTransaction there is a List running, but looks like doesn't change anything.. It takes the same amount of time > 2) Insert multiple records: [...] Good idea, now on the "white label" the time drastically change, it takes like 50% less than before.. > 3) Use query parameters Will try – StCa May 23 '17 at 10:10

score 0 · Answer 2 · answered May 25 '17 at 15:30

So, there we're with some experiment.

I remove the "split CSV part". The "record_list" variable is 27358 record. I comment the DB operation 'cause as suggested I try to determine where the time is spent. I add two Date variable so I can see how much it really takes.

Well, it takes 159 seconds to populate the SQL query with the white label device.. If I uncomment the db operation it takes the same amount of time ( 165 seconds ). So the problem is in the String creation and I think that it's already optimized at his best..

Here is the code

    String[] record_list = Split_CSV(); 

    Date StartDate = new Date();
    //SQLiteDatabase db = this.getWritableDatabase();
    //db.beginTransaction();
    try
    {
        StringBuilder sql = new StringBuilder();

        int i=0;

        for (String line : record_list)
        {
            String[] values = line.split(";");

            if (i==0)
            {
                sql.append("INSERT INTO TABLE (FIELD_1, FIELD_2, FIELD_3, FIELD_4) VALUES ");
            }

            i = i+1;
            sql.append(String.format("(%s,%s,%s,%s), ",
                    values[0],
                    values[1],
                    values[2],
                    values[3]));

            if (i==500)
            {
                i = 0;
                //db.execSQL(sql.substring(0,sql.length()-2));
                sql.setLength(0);
            }
        }

        if (sql.length()!=0) {
            //db.execSQL(sql.substring(0, sql.length() - 2));
            sql.setLength(0);
        }

        //db.setTransactionSuccessful();
    }
    catch (SQLiteException ex)
    {
        Log.d(TAG, "addAnagraficheClienti : " + ex.getMessage());
        throw ex;
    }
    finally
    {
        //db.endTransaction();
    }

    Date EndDate = new Date();

If anyone don't know: the "INSERT" is splitted every 500 record 'cause of this comment:

Is it possible to insert multiple rows at a time in an SQLite database?

I was reading that:

As a further note, sqlite only seems to support upto 500 such union selects per query so if you are trying to throw in more data than that you will need to break it up into 500 element blocks

Android / SQLite - Fastest insert?

2 Answers2