What is the most efficient way to RESET an SQLite Table

Question

I am having problems with reseting a particular table in SQLite. This table contains more than 2000 rows and I need to update it each time I launch the app. As the data I have to put is not always the same, I think the best I can do is reset the whole table and add the 2000 rows again. The problem is that it takes to much time. I've tried everything but the time it needs to work is still to much.

Does anyone know a better way for doing this?

Here is the code for reseting the table that is working best for me:

     public void resetSearchableTable(){

    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("DELETE FROM "+TABLE_SEARCHABLE_COINS);
}

Here is how I use it every time:

      db.resetSearchableTable();
                //add coins to db
                for (Coin coinInList : listCoins) {

                    db.addSearchableCoin(coinInList);


                    //Log.d("searchable coin ", coinInList.getName() + "  " + coinInList.getShortName());
                }

If you need to reset the table every time you open the app, do you really need to store these data in a database as you obviously don't need data persistence? — Eselfar, Jan 29 '18 at 15:22
I don't want to speak for the asker, but in my experience there are plenty of good reasons to do that. SQLite can be used entirely in-memory, which allows developers to use SQL-like semantics (or SQL-requiring tools) to address transient runtime data. The ubiquity of something like LINQ demonstrates that this is a common need. Crash/power-loss resilience of an application can also be greatly helped by using a persistent database. Even if non-crashed restarts should reset the internal state, recoverability may be desirable. — Zac B, Jan 29 '18 at 15:52

Zac B · Accepted Answer · 2018-01-29T20:31:54.357

0

This is something of a general answer to a general question, but one of these solutions may be useful for you:

EDIT: Using TRUNCATE is not supported; using the truncate optimization does not add anything to the solution that was already tried in the question. If the unconditional DELETE FROM [table] is too slow, try using TRUNCATE TABLE. It may be much faster; it has been so for me in certain scenarios. You'll have to benchmark it for your own situation, though.
Alternatively, try dropping and re-creating the table (this can be tricky if multiple threads are talking to the table at the same time; in this case you may want an idempotent table-creation function, or a lock around it).

If all of those are too slow, the performance issue may be caused by the amount of time it takes to talk to the persistent storage used by your database. In that case, you have a few other options:

Ensure your sqlite database is tuned for performance, if possible. This may include switching it to/from WAL mode, and/or controlling synchronization chunk sizes or other performance tricks.
If all else fails, a modification to your code's behavior may be in order. If you can make a new table with a temporary/unique name, e.g. searchable_coins_489 (and tell code that writes to the table the new name), then you can "fork and forget" the deletion: create a new thread, issue a DROP TABLE in it, and then forget about it (don't wait for it to complete) in your main thread. Since the database is backed by the same files, this may not yield quite the same performance benefits as it would if there were a multiprocess remote database, but it may help your situation some--especially if you're in WAL mode. Again, benchmarking is key here.

edited Jan 29 '18 at 20:31

answered Jan 29 '18 at 15:21

Zac B

3,796
3
35
52

SQLite does not have TRUNCATE. And dropping/recreating a table would not be tricky at all if done in a transaction. – CL. Jan 29 '18 at 16:39
Whoops, I'll edit my answer to remove the `TRUNCATE` suggestion. However, dropping a table with multiple accesses often encounters trouble with transactions in my (and apparently others' https://stackoverflow.com/questions/17733419) experiences. If there's not multi-process access, just multiple threads in the same process, I find it generally easier to coordinate locking around whole-table operations in SQLite outside of the database. – Zac B Jan 29 '18 at 20:31
The linked question was a bug in the application. And using automatic transactions around the drop and recreate, instead of a single transaction around both, just ensures that other connections might see an intermediate state, but cannot change the act of dropping itself. – CL. Jan 29 '18 at 20:42
With the edit regarding `TRUNCATE`, is your downvote due to the parenthetical regarding drop-table and concurrency? If so, do you think that the rest of my answer is useful/sufficient? I'm happy to edit or delete it if not. – Zac B Jan 31 '18 at 16:35
This answer is not useful because it ignores the inserts, which take more than 99 % of the time. – CL. Jan 31 '18 at 17:26
It seems ambiguous whether the questioner is interested in the performance of their "reset" code (as indicated by the title), or the performance of their INSERTs. @nlsLS97, are you experiencing bad performance with both parts together, or just one? – Zac B Jan 31 '18 at 20:33

What is the most efficient way to RESET an SQLite Table

1 Answers1