Add a print statement to vacuum.c to see what SQL statements are actually executed during VACUUM:
diff -u -3 -p -r1.64 vacuum.c --- src/vacuum.c 10 Oct 2006 13:07:36 -0000 1.64 +++ src/vacuum.c 18 Nov 2006 17:18:07 -0000 @@ -26,6 +26,7 @@ */ static int execSql(sqlite3 *db, const char *zSql){ sqlite3_stmt *pStmt; + printf("%s\n", zSql); if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ return sqlite3_errcode(db); } $ ./sqlite3.exe v.db vacuum ATTACH 'C:\TMP\etilqs_SOVEJE7Rni84Zzy' AS vacuum_db; PRAGMA vacuum_db.synchronous=OFF BEGIN EXCLUSIVE; CREATE TABLE vacuum_db.t1(a, b, primary key(b, a)) CREATE TABLE vacuum_db.t2(c, d) CREATE INDEX vacuum_db.t2i on t2(d, c) INSERT INTO vacuum_db.'t1' SELECT * FROM 't1'; INSERT INTO vacuum_db.'t2' SELECT * FROM 't2'; INSERT INTO vacuum_db.sqlite_master SELECT type, name, tbl_name, rootpage, sql FROM sqlite_master WHERE type='view' OR type='trigger' OR (type='table' AND rootpage=0) ----- Original Message ---- From: Nemanja Corlija <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Saturday, November 18, 2006 9:35:52 AM Subject: [sqlite] Why is VACUUM so slow? 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] ----------------------------------------------------------------------------- ____________________________________________________________________________________ Sponsored Link $420k for $1,399/mo. Think You Pay Too Much For Your Mortgage? Find Out! www.LowerMyBills.com/lre ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------