Sqlite import sql script is very slow

[rcdevice]

Feature Description

When I use cmd [ sqlite3 gitea.db < gitea-db.sql ] to restore the Sqlite database from gitea-db.sql , It takes about 5-6 minutes, unbearably slow, and the data that I recovered was only a few hundred records .

Here's my solution,add two command sets at the beginning and end of the gitea-db.sql file：

PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = OFF;
PRAGMA ignore_check_constraints = OFF;
PRAGMA auto_vacuum = NONE;
PRAGMA secure_delete = OFF;
BEGIN TRANSACTION;

<gitea-db.sql CONTENT>

COMMIT;
PRAGMA ignore_check_constraints = ON;
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

---

After the above modification, run same again, less than 1 second, very fast.

I think install Gitea with SQLite very slow to initialize is the same reason.

Screenshots

No response

[seedform]

As a temporary workaround for my small (less than 20mb) sqlite database, I was able to get away by simply adding BEGIN; to the top of the script (this disables auto commits in sqlite) and COMMIT; at the end.

This made it easy to automate restoration via shell script and Ansible:

cat <(echo 'BEGIN;') gitea-db.sql <(echo 'COMMIT;') | sqlite3 gitea.db

Runtimes

Unfortunately, this never finished in a reasonable amount of time on an HDD due to the sheer number writes to disk, so I ran it on an SSD instead:

# Default gitea script

$ time sh -c 'cat gitea-db.sql | sqlite3 gitea.db; sync'

real	7m10.470s
user	0m6.704s
sys	0m24.534s

# With BEGIN + COMMIT to disable auto commit

$ time sh -c 'cat <(echo "BEGIN;") gitea-db.sql <(echo "COMMIT;") | sqlite3 gitea.db'

real	0m0.882s
user	0m0.174s
sys	0m0.020s