didn't try any of your tricks, but can confirm that VACUUM is very slow on a similar db I have...
table1 -- 210k rows x 6 cols, 4 indexes, 1 pk table2 -- 36k rows x 6 cols, 4 indexes, 1 pk table3 -- 16k rows x 6 cols, 4 indexes, 1 pk table4 -- 5M rows x 4 cols, 2 indexes, 1 pk total size on file 1.95 Gb VACUUM takes an hour+ on a 3 GHz x 4 Xeon (quad Xeon), 4 Gb RAM, Win XP, SQLite 3.3.7 On 11/18/06, Nemanja Corlija <[EMAIL PROTECTED]> wrote:
I have a db with one table that has a text primary key and 16 text columns in total. After importing data from CSV file db had 5M rows and file size was 833MB. After some big DELETEs db had around 3M rows and 500MB after "VACUUMing". Running VACUUM for more then an hour filled new db with ~300MB worth of data. I then aborted that and did something like this: PRAGMA page_size=4096; PRAGMA synchronous=OFF; PRAGMA cache_size=100000; PRAGMA temp_store=MEMORY; CREATE TABLE tbl1(same structure as in original db); ATTACH "original.db3" AS old; INSERT INTO main.tbl1 SELECT * FROM old.tbl1; This finished in less then 15 minutes. Timings are obviously very rough here, but time difference is obvious. Except for page_size, VACUUM had same PRAGMAs applied. Isn't what I did manually above very similar to what VACUUM does behind the scenes? If so, why is there such a big difference? One observation though, while VACUUM seemed to be completely I/O bound. INSERT seemed to be CPU bound, which is not surprising given that it was doing uniqueness check for PRIMARY KEY all over again. I guess VACUUM doesn't have to do that since its copying from existing db that already had uniqueness enforced. This was on Win2000, SQLite 3.3.7 via sqlite3.exe. Has anyone experienced similar performance difference? -- Nemanja Corlija <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
-- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ ------------------------------------------------------------------------------------------------------- collaborate, communicate, compete ==================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
