But now, here's the weird part ladies and gentlemen. I started this on one drive, vacuum into completed, but then I had to do real work on that drive, so copied the file over to an unused drive to do the previously reported timing without it being biased by other things going on. But I saw something weird, which I just ran again to confirm.
"vacuum into" created a larger file when the original file was read only than it did when the original file was writable. Have run this now twice with the same output file size, so my bad eyes are at least being consistent in what they're seeing. Am on Windows 7 here. When the original db file was read only (Windows properties, not any flag to the CLI) the resulting file from "vacuum into" was 6% bigger. When I undid the read only checkbox and ran the same thing again, the resulting file was the same size as the original. Original file size: 20,467,359,744 Vacuum into size when original is writable: 20,467,359,744 Vacuum into size when original is read only: 21,760,516,096 Timing was also between the vacuum into, and regular vacuum times for the writable file. Well, wall clock time anyway. "user" was way up. sqlite> vacuum into 'vac_into_from_ro_file.sqlite'; Run Time: real 1107.448 user 853.185069 sys 87.048558 Gonna run sqlite3_analyzer.exe on them now to see what's up. But closing in on the end of the work day/week here. -----Original Message----- From: David Raymond Sent: Friday, February 08, 2019 1:53 PM To: SQLite mailing list Subject: RE: [sqlite] Vacuum into Non-scientific "let's just try it" results Short version: Original file had been vacuumed already as the last thing that had happened to it. File size: 20,467,359,744 sqlite> vacuum into 'vac_into.sqlite'; Run Time: real 589.577 user 222.941029 sys 57.829571 sqlite> vacuum; Run Time: real 1429.063 user 236.325915 sys 199.322478 Synchronous was off, journal mode was normal rollback journal. Was using the computer for normal work while doing it, but nothing else was using the physical drive these were on. Resulting files were the exact same size as the originals (it had been previously vacuumed as mentioned) -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 08, 2019 11:29 AM To: SQLite mailing list Subject: Re: [sqlite] Vacuum into On 2/8/19, David Raymond <david.raym...@tomtom.com> wrote: > So to make sure I'm understanding it ok, with the new vacuum into command, > if I'm the only user of a file, then the sequence... > > open file1 > vacuum into file2 > close file1 > delete file1 > rename file2 to file1 > > ...is going to be potentially more than twice as fast as the old... Think you will be hard-pressed to get a 2x performance increase. Faster, yes. 2x faster, I'm doubtful. But perhaps I'm wrong. Please try it and see and report back. > > open file1 > vacuum > > ...as it saves the whole re-write of the original file, along with all the > rollback journal or wal writes that entails. Correct? > > (With of course the whole "make sure it actually finished and didn't just > die" caveats before doing the delete and rename) > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
