There's also a way to truncate the table, effectively replacing the table's rows with an empty table. This bypasses any database side integrity triggers like cascading deletes of foreign keys, it's usually faster than deleting rows from a very large table, and should be roughly equivalent in meaning to the "delete_all" command.
Books.connection.truncate("books")
This won't work in Sqlite (TRUNCATE is not supported), and won't call any of the database side triggers either, and may or may not fail based on foreign key constraints. It's sometimes faster than dropping the database and recreating it (since you don't need to reload the schema or migrations, and any keys or index definitions are already present, just the data has been removed).
More importantly, this doesn't permit any scoping - author.books.destroy_all sends a WHERE author_id = ? constraint to the DELETE command, but TRUNCATE always indiscriminately removes all data from the table and resets it with an empty table.
There's probably a size below which delete_all is faster than truncate, I haven't tested and suspect the shape of the table (columns and indexes) will contribute to the timing.
The biggest reason not to use a command like truncate (apart from the rarity of needing to wipe out all of the data) is that it's non-transactional, there's no way to rollback a truncation once you've done it, you can't guarantee atomic actions be bundled together (truncate authors but only if I can also truncate books, otherwise neither), and if you use a transaction rollback strategy around your test cases to provide isolation truncate will not be safe if you also expect to have seeded data present for tests.
There's also a way to truncate the table, effectively replacing the table's rows with an empty table. This bypasses any database side integrity triggers like cascading deletes of foreign keys, it's usually faster than deleting rows from a very large table, and should be roughly equivalent in meaning to the "delete_all" command.
This won't work in Sqlite (TRUNCATE is not supported), and won't call any of the database side triggers either, and may or may not fail based on foreign key constraints. It's sometimes faster than dropping the database and recreating it (since you don't need to reload the schema or migrations, and any keys or index definitions are already present, just the data has been removed).
More importantly, this doesn't permit any scoping -
author.books.destroy_allsends aWHERE author_id = ?constraint to theDELETEcommand, butTRUNCATEalways indiscriminately removes all data from the table and resets it with an empty table.There's probably a size below which
delete_allis faster thantruncate, I haven't tested and suspect the shape of the table (columns and indexes) will contribute to the timing.The biggest reason not to use a command like truncate (apart from the rarity of needing to wipe out all of the data) is that it's non-transactional, there's no way to rollback a truncation once you've done it, you can't guarantee atomic actions be bundled together (truncate authors but only if I can also truncate books, otherwise neither), and if you use a transaction rollback strategy around your test cases to provide isolation truncate will not be safe if you also expect to have seeded data present for tests.
Wow, new stuff I learned today. Thanks Daniel !!!