SQLite Forum

Potential Vacuum speed up
Login

Potential Vacuum speed up

(1) By cornerStone-Dev on 2021-01-02 18:24:48 [source]

Hello Forum,

From my reading of the source vacuum.c there is a query to reconstruct the database by reading the schema for 'table' and 'index' statements separately.

/* Query the schema of the main database. Create a mirror schema
** in the temporary database.
*/
db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */
rc = execSqlF(db, pzErrMsg,
    "SELECT sql FROM \"%w\".sqlite_schema"
    " WHERE type='table'AND name<>'sqlite_sequence'"
    " AND coalesce(rootpage,1)>0",
    zDbMain
);
if( rc!=SQLITE_OK ) goto end_of_vacuum;
rc = execSqlF(db, pzErrMsg,
    "SELECT sql FROM \"%w\".sqlite_schema"
    " WHERE type='index'",
    zDbMain
);

Would it be beneficial (and possible) to delay the creation of the indexes in the copy database until after all the data has been copied over. I have read in the documentation and in the forums that for mass insert it is better to drop all indexes and then re-create them. I wondered if this applied here too?

Thanks,

Alex

(2) By Richard Hipp (drh) on 2021-01-02 18:42:23 in reply to 1 [link] [source]

VACUUM does not recompute the indexes. It instead copies the index data from the old database over into the new database. This is a special optimization that kicks in if you do:

  INSERT INTO t1 SELECT * FROM t2;

and tables t1 and t2 have identical column sets and constraints and indexes and if t1 is initially empty. (The conditions for when this optimization apply are actually more complex than that, but those are the conditions that apply in the case of VACUUM.)

Copying the index data is way faster than recomputing it. So that is what we want to do. But for that optimization to apply, we have to create the indexes before doing the insert.

(3) By cornerStone-Dev on 2021-01-03 11:49:17 in reply to 2 [link] [source]

Very cool, I had no idea that optimization was there. Thank you for all the work you have put into sqlite3, I have learned so much about coding from reading the source code.