Android sqlite inserting 500 rows does not work or preserve INSERT order

Question

I can not get SQLite to support my begin/end transaction surrounding multiple inserts.

 Multiples INSERTs : 2500ms
 Using BEGIN and COMMIT : 90ms
 Using SELECT and UNION : 40ms

So I looked using begin and commit. What am I doing wrong?

  // pseudocode: 
  ArrayList<Integer> iList = new ArrayList<Integer>();
    for (int i = 1; i <= 500; i++) {
      iList.add(i);
    }
  Collections.shuffle(iList);

  StringBuilder sb = new StringBuilder("begin transaction;");
  for (Integer i: iList) {
    sb.append("insert into \"t_order\" (qid) values(");
    sb.append(i);
    sb.append(");");
  }
  sb.append(" end transaction;");

  // from docs: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL(java.lang.String)
  // Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.
  m_db.execSQL(sb.toString());

OK, I did a bit more research and it seems that "Multiple statements separated by semicolons are not supported." What can I do instead to insert and preserve insert order?

score 2 · Answer 1 · answered Jun 21 '12 at 02:20

2

Start a transaction, execute each of the INSERTs on separate execSQL() calls, and then commit the transaction.

You don't need to cluster the INSERTs together in the same execSQL() call.

answered Jun 21 '12 at 02:20

Robert Harvey

178,213
47
333
501

Ted Hopp · Answer 2 · 2012-06-21T03:24:26.037

2

Use the SQLiteDatabase.beginTransaction() and SQLiteDatabase.endTransaction() methods and issue your execSQL call(s) between them. It would also be better style to use a ContentValues structure instead of doing your own string concatenation:

ContentValues cv = new ContenValues();
m_db.beginTransaction();
try {
    for (Integer i: iList) {
        cv.put("qid", i);
        m_db.insert("t_order", null, cv);
    }
    m_db.setTransactionSuccessful();
} finally {
    m_db.endTransaction();
}

edited Jun 21 '12 at 03:24

answered Jun 21 '12 at 02:20

Ted Hopp

232,168
48
399
521

I tried that, but then the insert order is not preserved. For example try inserting "3" and then "1". This is what happens. `select rowid,* from t_order;` `1|1` `3|3` – AG1 Jun 21 '12 at 03:29
@AG1 - I don't think the behavior would have been any different if you had managed to get your multi-statement SQL query to work. From [this answer on another thread](http://stackoverflow.com/a/7597406/535871): _"The best-practices method of sorting rows by the temporal order in which they have been inserted is to use a timestamp column which is assigned as a default value the date-time corresponding to "now", as the row is being inserted."_ – Ted Hopp Jun 21 '12 at 03:39
It is a pity that sqlite does not preserve insert order. I realize there are some neat optimizations that can be done but adding an extra column of data where it is not needed seems wasteful. Is there any command that would tell sqlite to preserve insert order after I begin the transaction? – AG1 Jun 21 '12 at 04:15
@AG1: Why would you need to preserve insert order? If insert order is important, add a row counter field. If you're using Ted's code, you can simply `ORDER BY qid` in your SELECT statement. – Robert Harvey Jun 21 '12 at 20:58

score 0 · Answer 3 · answered Jun 21 '12 at 02:35

Have a look at the official Android documentation on beginTransaction(). Replace the "..." portion with a loop doing a separate execSQL() call -- there is no need to truncate the statements together in one buffer.

Also, it's often worth it to use a prepared statement. Prepare the statement, begin the transaction, loop for each item binding and executing the statement and finally commit.

score 0 · Answer 4 · answered Sep 28 '13 at 19:40

This may help :

public void putAll(Context context, LinkedList<HANDLEDOBJECT> objects) {

    if (objects.size() < 1 || objects.get(0) == null)
        return;

    Log.i("Database", "Starting to insert objects to " + getTableName());

    List<String> insertCommands = new ArrayList<String>();
    int t = 0;

    while (t < objects.size()) {
        int k = 0;
        StringBuilder sb = new StringBuilder();

        sb.append("INSERT OR REPLACE INTO ").append(getTableName())
                .append("('k', 'v') ");

        for (t = t + 0; t < objects.size() && k < 450; t++) {
            k++;
            if (t % 450 != 0)
                sb.append("UNION ");
            sb.append("SELECT " + objects.get(t).getId())
                    .append(" AS k, ")
                    .append("'"
                            + GsonSerializer.getInstance().toJson(
                                    objects.get(t), getHandledObjectType())
                            + "'").append(" AS v ");
        }

        insertCommands.add(sb.toString());
    }

    for (String insertCommand : insertCommands)
        SQLiteClient.getConnetion(context).execSQL(insertCommand);

    Log.i("Database", "Successfully inserted " + t + " objects to "
            + getTableName() + "!!!");

    System.gc();
}

Android sqlite inserting 500 rows does not work or preserve INSERT order

4 Answers4