On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson <pri...@gmail.com> wrote:
> Simon - instead of using vacuum, it's much faster to create a new > database from the old one, then rename it. It's easy to do this in > Python using iterdump(), or you can connect to the new (empty) > database, do your create table statements, attach the old database as > olddb, then do: > > insert into table1 select * from olddb.table1; > insert into table2 select 8 from olddb.table2; > > This also lets you do the create table stmts w/o indexes, and add the > indexes after the inserts. Not sure if that is faster or not for your > data. If you look at code for VACUUM, that’s more or less what it does… only it is very smart about it, properly preserving ROWID values, as well as exact table definitions, sequences, analytics, and all the meta-data in the database. The “copy to new database” accounts for the 2x size requirement. In the case of VACUUM, the data is then copied from the fresh DB back to the original database file in a transaction-safe way; this touches every page in the original file, requiring a rollback log of some sort (journal or WAL)— and that accounts for the 3x space requirement. While you can roll your own, the method you propose is not transaction safe and is difficult to use in a production environment unless you know you can shut down all services using the DB and force them to re-open connections to the new file. That’s true of some applications, but far from all of them. About five years ago I proposed a VACUUM TO <file> variant of VACUUM that would more or less do what you’re talking about, without the copy-back operation. It would only require 2x the disk space, and be much faster for applications that were able to use it. I still think it would be a useful, low-cost feature. http://www.mail-archive.com/sqlite-users@sqlite.org/msg50941.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
