[sqlite] Recipe to safely move/rename a database?
I have a scenario where I want to move 99+% of the records from one database to another, initially empty but for a set of table definitions (in practice, copied from a template file). On my Linux platform, I find that the INSERT INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably long (it involves about 30MB of data). What I would rather do is: 1) move the current database file from its current location to the archive location, 2) create a new current database (from the same template I use now for the archive) and 3) copy back, from archive to current, the rows that should *not* be archived (deleting them from the archive afterward). Clearly, I'll need to create a lock on the current database before moving it, but I can foresee complications related to the behind-the curtain filesystem operations being performed. If someone has worked out all the pitfalls of this scenario, I'd appreciate a recipe. Thanks, Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recipe to safely move/rename a database?
On 7 Dec 2009, at 9:58pm, Chris Eich wrote: On my Linux platform, I find that the INSERT INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably long (it involves about 30MB of data). Do you have a transaction around all the INSERT commands ? This will speed it up many fold. Another thing which will increase speed is to DROP all indexes (apart from PRIMARY KEY) before the INSERT commands, and recreate them afterwards. There's no reason why you shouldn't pursue the technique you mentioned, but I thought I'd give you an alternative you might prefer. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recipe to safely move/rename a database?
Simon Slavin wrote: On 7 Dec 2009, at 9:58pm, Chris Eich wrote: On my Linux platform, I find that the INSERT INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably long (it involves about 30MB of data). Do you have a transaction around all the INSERT commands ? This will speed it up many fold. there aren't multiple insert commands. only a single insert command was mentioned. it would be a single transaction already. i have no advice, i'm just pointing out that this advice seems to be based on the false assumption that multiple transactions are involved (because this is the most common reason why things slow down). Another thing which will increase speed is to DROP all indexes (apart from PRIMARY KEY) before the INSERT commands, and recreate them afterwards. There's no reason why you shouldn't pursue the technique you mentioned, but I thought I'd give you an alternative you might prefer. Simon. cheers, raf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
