sql - What's the best way to delete all data from a table?

Question

I have a SQLite table with 6 million rows.

Doing a DELETE FROM TABLE is quite slow;

Dropping the table and then re-creating it seems quicker.

I'm using this for a database import.

Would dropping the table be a better approach or is there a way to delete all data quickly?

Answer 1

One big difference is that DELETE FROM TABLE is DML and DROP TABLE is DDL. This is very important when it comes to db transactions. The result at the end may be the same, but these operations are very different.

If it's just performance you've to be aware of then it may be ok to drop and recreate the table. If you need transactions in your imports then you've to be aware that DDL is not covered and cannot be rollbacked for example.