android - What is the most efficient way to RESET an SQLite Table

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

Answer

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.