How to optimize insertion of thousands records in SQLite

Question

I am inserting 3000 records in DB using mentioned code i am getting list of param from server and making query using them but the problem is on emulator it took 2.5 minutes to insert and it taking just double on real device.

So please tell me how should optimize insertion.

Here is my code

public boolean insertRowDynamically(TableData tableData, String tableName) {

        keysStringBuilder.delete(0,keysStringBuilder.length());
        valuesStringbuilder.delete(0,valuesStringbuilder.length());
        SQLiteStatement sqLiteStatement = null;
        response = true;
        flag = true;
        first=true;
        LinkedHashMap<String,Object> values= new LinkedHashMap<>();
        List<Map<String, Object>> rowValue = tableData.getListofMapRecords();
        try {
            openDataBase();
            myDataBase = this.getWritableDatabase();
            myDataBase.beginTransactionNonExclusive();


            int i=0;
        for (Map<String, Object> map : rowValue) {

            values.clear();
            values.putAll(map);
            Set keys = values.keySet();
            Iterator<String> it = keys.iterator();




                    i=1;
                while (it.hasNext()) {
                    String key = it.next();
                    if (flag) {

                        keysStringBuilder.append(key);
                        valuesStringbuilder.append("?");

                        flag = false;
                    } else {
                        keysStringBuilder.append(", ").append(key);
                        valuesStringbuilder.append(", ").append("?");
                    }
                    if (!it.hasNext()) {
                        String query = "INSERT INTO " + tableName + "(" + keysStringBuilder + ") VALUES(" + valuesStringbuilder + ")";
                        sqLiteStatement = myDataBase.compileStatement(query);
                    }




                }




            }





            first = false;
                i=1;


            for ( String key :values.keySet())
            {
                sqLiteStatement.bindString(i,values.get(key).toString());
                i++;

            }


            sqLiteStatement.executeInsert();
            sqLiteStatement.clearBindings();











            } catch (SQLException e) {
                Log.d("Syncing Data", "From insertRowDynamically " + e);
            } finally {

                close();
            }


        return response;
    }

have you looked at http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html ? — dsafcdsge4rfdse, Apr 14 '16 at 11:15
@GoltsevEugene Did some changes in the given code and started working for me. — Shivam Nagpal, Apr 15 '16 at 06:28

score 0 · Answer 1 · answered Apr 15 '16 at 06:29

Started using table level tranactions instead of row level transactions and it worked like charm for.

public boolean insertRowDynamically(TableData tableData, String tableName) {

        keysStringBuilder.delete(0,keysStringBuilder.length());
        valuesStringbuilder.delete(0, valuesStringbuilder.length());
        SQLiteStatement sqLiteStatement = null;
        response = true;
        flag = true;
        first=true;
        LinkedHashMap<String,Object> values= new LinkedHashMap<>();
        List<Map<String, Object>> rowValue = tableData.getListofMapRecords();
        try {
            openDataBase();
            myDataBase = this.getWritableDatabase();
            myDataBase.beginTransactionNonExclusive();
            int i=0;
            Log.d("Sync Time","Table Data Size "+rowValue.size());
        for (Map<String, Object> map : rowValue) {

            values.clear();
            values.putAll(map);
            Set keys = values.keySet();
            Iterator<String> it = keys.iterator();


            i = 1;
            if (first) {
                while (it.hasNext()) {
                    String key = it.next();
                    if (flag) {

                        keysStringBuilder.append(key);
                        valuesStringbuilder.append("?");

                        flag = false;
                    } else {
                        keysStringBuilder.append(", ").append(key);
                        valuesStringbuilder.append(", ").append("?");
                    }
                    if (!it.hasNext()) {
                        String query = "INSERT INTO " + tableName + "(" + keysStringBuilder + ") VALUES(" + valuesStringbuilder + ")";
                        sqLiteStatement = myDataBase.compileStatement(query);
                    }


                }


            }


            first = false;
            i=1;


            for ( String key :values.keySet())
            {
                sqLiteStatement.bindString(i,values.get(key).toString());
                i++;

            }


            sqLiteStatement.executeInsert();
            sqLiteStatement.clearBindings();

        }

            myDataBase.setTransactionSuccessful();
            myDataBase.endTransaction();

            } catch (SQLException e) {
                Log.d("Syncing Data", "From insertRowDynamically " + e);
            } finally {

                close();
            }


        return response;
    }

How to optimize insertion of thousands records in SQLite

1 Answers1