Hi, We're doing a massive tidy on our database which is approx 50GB.
One table is approx 49GB of that 50GB which we need to delete as we have recorded the information in a far better format thats approx 99% more efficient. If only we had been this clever when we started....
We've just 'dropped' the table and were assuming that dropping the table would be quite quick. It's not. So far we've been waiting for 30 mins and nothing has come back yet. We can see that the -wal file is upto 2.5GB. We have this terrible feeling that it'll need to get to 49GB or so before the table gets dropped. We can just about handle that in the current filesystem.
We're now getting nervous about dropping this table. We had assumed that it would be a really quick and easy operation based on absolutely no checking whatsoever. When we looked on line all we could see was a reference to a very, very old and outdated page (https://sqlite.org/speed.html) which talks about speed and at the bottom of that page the comments
"SQLite is slower than the other databases when it comes to dropping tables. This probably is because when SQLite drops a table, it has to go through and erase the records in the database file that deal with that table. MySQL and PostgreSQL, on the other hand, use separate files to represent each table so they can drop a table simply by deleting a file, which is much faster.
On the other hand, dropping tables is not a very common operation so if SQLite takes a little longer, that is not seen as a big problem."
Is this still the case, is it going to take a long time? If we assume that the table is 49GB then will we need to wait until the -wal file is at 49GB. By our estimates thats approximately 10 hours away.
Any help or idea or suggestions welcomed, but please be quick. Rob _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
