CODEWITHSUNDEEP
databasesqliteandroid-sqlite
MBH
MBH

Reputation: 16609

dropping table vs removing its content sqlite

in my application I have to delete whole contents of a table and refill it many times

in that case I have two solutions :

1- drop , recreat and fill the table

"DROP TABLE IF EXISTS TABLE_NAME"

2- remove all its contents and refill it

"DELETE FROM TABLE_NAME"

now which solution more effective?

Upvotes: 4

Views: 2988

Answers (2)

jefflunt
jefflunt

Reputation: 33954

  • If you have any unique 1-based auto-incrementing ids in this table then dropping and recreating the table may very well reset that counter to 1, whereas deleting and refilling will continue the counter from the last used auto-incrementing value.
  • Deleting everything in a table without a WHERE clause will truncate the table, which is pretty fast, and avoids the process of SQLite deleting every row individually.

Regardless what you do, if you have any foreign keys pointing to this table be careful not to completely fubar those references.

Upvotes: 5

user359040
user359040

Reputation:

From http://www.sqlite.org/lang_delete.html :

The Truncate Optimization

When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the delete run much faster.

Upvotes: 8

Related Questions