drop or delete a sqlite - table as fast as possible on Android device

Question

I have a table with 1400 rows. Every row has a blob field which holds data between 10kb and 500kb. I need to delete that table. It takes me 3.5 minutes to delete the table and 3 minutes to drop the table. Thats too long for the users. How can I remove that table as fast as possible ? ( No rollback needed or any security, just remove it. )

I already tried the following. 1. Set pagesize :

sqlitedatabase.setPageSize(8000);
sqlitedatabase.execSQL("DROP TABLE IF EXISTS " + sTableName);

2. deactivate journallog which did not work.

sqlitedatabase.rawQuery("PRAGMA journal_mode=OFF",null);
sqlitedatabase.execSQL("DROP TABLE IF EXISTS " + sTableName);

this doesn't work for me. journal log, which I guess takes a lot of time, is still be written on to the disk.

Answer 1

From the SQLite manual (with emphasis added):

SQLite is slower than the other databases when it comes to dropping tables. This probably is because when SQLite drops a table, it has to go through and erase the records in the database file that deal with that table. MySQL and PostgreSQL, on the other hand, use separate files to represent each table so they can drop a table simply by deleting a file, which is much faster.

You do have the option of creating and storing multiple database files, which you can then manage from a single connection with ATTACH and DETACH queries.

Here's an example I just ran in SQLite3's command-line client:

sqlite> ATTACH 'example.sqlite' AS example;
sqlite> CREATE TABLE example.ex ( a INTEGER );
sqlite> INSERT INTO example.ex VALUES (1),(2),(3);
sqlite> SELECT * FROM example.ex;
1
2
3
sqlite> DETACH example;
sqlite>

Since the ex table is in its own file, example.sqlite, I can simply detach that DB from the connection and delete the entire file, which will be much faster.

Bear in mind that the number of DBs you can attach is fairly low (with default compile options: 7). I've also read that foreign keys aren't supported in this scenario, though that info might be out of date.