On 7/16/18, Rob Willett <rob.sql...@robertwillett.com> wrote: > > It does look as if one of sqlite's weaknesses is dropping very, very > large tables. >
Right. If every table were stored in a separate file, a DROP TABLE could be translated to a relatively fast unlink(). But then a database would be a directory full of files, rather than a single file, which would undermine a lot of the usefulness of SQLite. Furthermore, DROP TABLE is an uncommon operation. We prefer to provide a database where all content is contained in a single file and that is optimized SELECTs and for INSERTs and DELETEs of a subset of the rows in the table, as that seems to be far more useful in most cases. There are always engineering trade-offs. SQLite provides single-file databases and fast queries in exchange for slower DROP TABLEs. A filesystem is able to implement unlink() quickly because it has the entire partition available for laying out the locations of files. Space can be allocated to a file in large chunks, which makes deallocation faster. In other words, in a filesystem, the files can be positioned sparsely on disk, with lots of unused space in between the various file to accommodate growth . But in SQLite, the goal is to keep the database file size as small as possible, and to not have unnecessary unused pages in the middle of the database. Hence, space for tables much be allocated in relatively small 1-page chunks, which means that there will be a large number of chunks to deallocate when dropping a large table. If you were to construct a filesystem that tried to keep all file content tightly packed at the beginning of a partition (say, for example, to make shrinking of a partition faster) then unlink() would necessarily be slower on that filesystem. That seems like a bad engineering trade-off for a filesystem, but it is (for most applications) a good trade-off for a database such as SQLite. You can work around this. If you have one or more tables that you think might need to be DROP-ed frequently, then consider storing them in a separate database files and ATTACH-ing those separate database files to your main database. Then, to drop those tables, you can DETACH them, and then unlink() the corresponding database file. That should go much faster. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
