Slow ItemTable SQL queries

[tmm1]

SQLiteStorageDatabase uses a simple schema w/ ON CONFLICT REPLACE:

vscode/src/vs/base/parts/storage/node/storage.ts

Lines 329 to 331 in 0c69046

	return this.exec(connection, [
	'PRAGMA user_version = 1;',
	'CREATE TABLE IF NOT EXISTS ItemTable (key TEXT UNIQUE ON CONFLICT REPLACE, value BLOB)'

and then relies on INSERT queries only to update data:

vscode/src/vs/base/parts/storage/node/storage.ts

Lines 106 to 107 in 0c69046

	keysValuesChunks.forEach(keysValuesChunk => {
	this.prepare(connection, `INSERT INTO ItemTable VALUES ${new Array(keysValuesChunk.length / 2).fill('(?,?)').join(',')}`, stmt => stmt.run(keysValuesChunk), () => {

However this is quite slow, and can be optimized using an UPSERT mechanism

Here is a microbench showing the difference:

cat <<EOF > schema.sql
PRAGMA user_version = 1;
CREATE TABLE IF NOT EXISTS ItemTable (key TEXT UNIQUE ON CONFLICT REPLACE, value BLOB);
EOF

cat <<EOF > q1.sql
BEGIN;
INSERT INTO ItemTable (key, value) VALUES ('test', '123');
COMMIT;
EOF

cat <<EOF > q2.sql
BEGIN;
INSERT INTO ItemTable (key, value) VALUES ('test', '123') ON CONFLICT (key) DO UPDATE SET value = excluded.value WHERE value != excluded.value;
COMMIT;
EOF

bash -c 'cat schema.sql; for i in {1..1000}; do cat q1.sql; done' > q1-1000.sql
bash -c 'cat schema.sql; for i in {1..1000}; do cat q2.sql; done' > q2-1000.sql

❯ for i in {1..10}; do time sqlite3 /tmp/test.db < q1-1000.sql; done
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.18s system 82% cpu 0.230 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.17s system 70% cpu 0.261 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.17s system 69% cpu 0.257 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.17s system 54% cpu 0.333 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.18s system 42% cpu 0.444 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.17s system 84% cpu 0.220 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.17s system 84% cpu 0.217 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.17s system 83% cpu 0.217 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.17s system 84% cpu 0.220 total
sqlite3 /tmp/test.db < q1-1000.sql  0.01s user 0.17s system 80% cpu 0.231 total


❯ for i in {1..10}; do time sqlite3 /tmp/test.db < q2-1000.sql; done
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.01s system 75% cpu 0.018 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.01s system 81% cpu 0.015 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.01s system 81% cpu 0.014 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.00s system 81% cpu 0.013 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.00s system 81% cpu 0.013 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.00s system 86% cpu 0.013 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.00s system 81% cpu 0.013 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.00s system 87% cpu 0.013 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.00s system 85% cpu 0.013 total
sqlite3 /tmp/test.db < q2-1000.sql  0.01s user 0.00s system 81% cpu 0.013 total