CODEWITHSUNDEEP
androidperformancesqlite
mcfly soft
mcfly soft

Reputation: 11645

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

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.

Upvotes: 4

Views: 5442

Answers (3)

mcfly soft
mcfly soft

Reputation: 11645

I got a solution for me, which speeds up the deletion 6 times. with 

connection_read.enableWriteAheadLogging();

I drop my table in 30 Seconds. Without it it takes the mentoined 3 minutes. enableWriteAheadLogging is an alternative journal log which is way faster.

Upvotes: 3

Karim Varela
Karim Varela

Reputation: 7652

Why not just put the drop table code in a separate thread? The user shouldn't have to wait for the app to drop a table.

Upvotes: 0

rutter
rutter

Reputation: 11452

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.

Upvotes: 5

Related Questions