On 22 April 2016 at 21:24, Adam Devita <adevita at verifeye.com> wrote: > > That said, why is the dropping of a table dependent on the size of > the table? Does Sqlite have to mark every block of memory it used as > dropped? (This is obvious for high security mode, but otherwise?)
In rollback journal mode, every modified page of the database is first written to the rollback journal. When the transaction is committed the actual database pages are updated on disk and the rollback journal removed to finalise the commit. Obviously the larger the table, the more database pages required to store it. I'm less familiar with WAL mode but I believe it's a similar story; modified pages will be written to the write-ahead log. The i/o cost may be spread out more in this mode though, as the database itself won't be updated until the next checkpoint. I've written this under the presumption that sqlite touches every database page that was associated with a table during the delete/drop... I can think of some optimisations allowing much of the i/o to be skipped (at least least when secure_delete isn't set), but I'm not sure exactly what sqlite does. -Rowan
