Public
Edited
Jan 5, 2024
insert_n_rows = {
db.exec([
"DROP TABLE IF EXISTS insert_n_rows;",
"CREATE TABLE insert_n_rows (a PRIMARY KEY);",
"VACUUM;"
]);
const stmt = db.prepare("INSERT INTO insert_n_rows VALUES (?)");
let a = 0;
const trials = [1, 10, 100, 1_000, 5_000, 10_000]; // 100_000, 1_000_000 takes too long for SQLite (> 1 minute)
const sqliteNoTx = [];
const sqliteTx = [];
const js = [];
const jsPersistentTreap = [];
const comp = (l, r) => l.a - r.a;
for (const trial of trials) {
const arr = [];
let pTreap = new PersistentTreap(comp);
sqliteNoTx.push({
size: trial,
time: bench(() => {
for (let i = 0; i < trial; ++i) {
stmt.bind(1, ++a).stepReset();
}
})
});
sqliteTx.push({
size: trial,
time: bench(() => {
db.transaction(() => {
for (let i = 0; i < trial; ++i) {
stmt.bind(1, ++a).stepReset();
}
});
})
});
js.push({
size: trial,
time: bench(() => {
for (let i = 0; i < trial; ++i) {
// allocate a new js object which is our row. Add it to `arr` which is our relation.
arr.push({ a: ++a });
}
})
});
jsPersistentTreap.push({
size: trial,
time: bench(() => {
for (let i = 0; i < trial; ++i) {
pTreap = pTreap.add({ a: ++a });
}
})
});
}
stmt.finalize();
return {
sqliteNoTx,
sqliteTx,
js,
jsPersistentTreap
};
}
{
let dep = insert_n_rows; // set up a dependency to ensure this cell runs _after_ insertions.
const pageSize = db.exec({
sql: "PRAGMA page_size",
returnValue: "resultRows"
})[0][0];
const pageCount = db.exec({
sql: "PRAGMA page_count",
returnValue: "resultRows"
})[0][0];
return { pageSize, pageCount, dep };
}
// Select from a table indexed by primary key
// Select from a map by key
select_n_rows = {
db.exec([
"DROP TABLE IF EXISTS select_n_rows;",
"CREATE TABLE select_n_rows (key PRIMARY KEY, value);",
"VACUUM;"
]);
const stmt = db.prepare("SELECT value FROM select_n_rows WHERE key = ?");
const map = new Map();
function setup() {
const stmt = db.prepare(
"INSERT INTO select_n_rows (key, value) VALUES (?, ?)"
);
db.transaction(() => {
for (let i = 0; i < 100_000; ++i) {
stmt.bind(1, i);
stmt.bind(2, "val-" + i);
stmt.stepReset();
map.set(i, "val-" + i);
}
});
stmt.finalize();
}
setup();
const trials = [1, 10, 100, 1_000, 10_000, 100_000];
const sqliteNoTx = [];
const sqliteTx = [];
const js = [];
for (const trial of trials) {
let a = 0;
const arr = [];
sqliteNoTx.push({
size: trial,
time: bench(() => {
for (let i = 0; i < trial; ++i) {
stmt.bind(1, ++a).stepReset();
}
})
});
sqliteTx.push({
size: trial,
time: bench(() => {
db.transaction(() => {
for (let i = 0; i < trial; ++i) {
stmt.bind(1, ++a).stepReset();
}
});
})
});
js.push({
size: trial,
time: bench(() => {
for (let i = 0; i < trial; ++i) {
arr.push(map.get(i));
}
})
});
}
stmt.finalize();
return {
sqliteNoTx,
sqliteTx,
js
};
}
bench = (fn) => {
let best = Number.MAX_SAFE_INTEGER;
const iterations = 5;
const times = [];
for (let i = 0; i < iterations; ++i) {
const start = performance.now();
fn();
const end = performance.now();
times.push(end - start);
best = Math.min(best, end - start);
}
times.sort();
// return the median
return times[Math.floor(iterations / 2)];
// avg = avg / iterations;
// return best;
}
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
