Speeding up a SQLite dump restore from days to a few hours. Home

I had a 23GB SQLite database that went "corrupt" due to an application glitch. I knew it wasn't completely lost because the glitch had affected only a tiny part of it, a single page perhaps. In order to restore it, I dumped its contents in a text file with 10.1 GB. It took almost THREE WHOLE DAYS to reach 30%. So my options were to wait more seven whole days, to use last week's backup, to get a SSD or to optimize the restore process. So optimize I did. This is all information readily available on the web, but I hope to save someone else's time by aggregating it all here.

First of all, you must disable write-cache flush in devmgmt.msc to maximize drive performance on Windows. You probably won't lose power or have to force shutdown the computer during this process.

Next, you must preallocate the necessary database size in a non-fragmented file. I used Sysinternals' CONTIG (link here: contig.exe) with this command-line for a 24GB file:

contig.exe -v -l -n restore.db 25769803776

This new file contains garbage and SQLite won't accept it. You must copy data from an empty template database to make the prepared file valid. This template database will also contain a page size optimized to one NTFS cluster (4096 bytes).

PRAGMA page_size = 4096;
create table dummy (id integer);
drop table dummy;

This will create an empty 8KB database. You must overwrite your contigous database file with this data while keeping the full file size. It is easily done in C:

#include <stdio.h>

void main(void)
	FILE *restore=fopen("restore.db", "rb+");
	FILE *template=fopen("template.db", "rb");
	int n;
	fseek(template, 0, SEEK_END);
	fseek(template, 0, SEEK_SET);
	void *buffer=malloc(n);
	fread(buffer, n, 1, template);
	fseek(restore, 0, SEEK_SET);
	fwrite(buffer, n, 1, restore);

Your database is now ready to be restored. Before using the .read command, apply these options:

PRAGMA foreign_keys=OFF; -- it is normally off 
PRAGMA cache_size=350000; -- sqlite3.exe memory can only grow up to 2,097,152 KB in taskmgr.exe
PRAGMA locking_mode=EXCLUSIVE; -- minor speed-up
PRAGMA synchronous=OFF; -- MAJOR speed-up
PRAGMA temp_store = MEMORY; -- best use of memory
PRAGMA ignore_check_constraints = ON; -- minor speed-up
PRAGMA journal_mode = OFF; -- MAJOR speed-up (WAL was sluggish as hell)

Since journal_mode is OFF, you'll have to reapply the template database and start over if any problem arises. Use HexEdit to quickly modify a new copy of the dump file if needed. My indexes were taking too long to recreate with the full data imported (9 GB, leaving 14 GB for indexes -- don't ask), so in my case it was better to recreate the full schema with indexes before importing the data, but the recommended is indeed recreating the indexes last.