This blogpost is an example of one of countless ways one can collect measurements and present them using nice easy to read charts by using a compiled MIMERPascal program to collect precise measurements of SQLite queries, and MIMERCode to make the final statistical analysis and chart generation.

An MPC-built MIMERPascal program collected 144 SQLite timings. A MIMERCode program read the CSV, fitted a runtime model, and rendered the chart from the held-out forecasts.

The diagram shows four held-out measurements from batch 4 at eight clients. The line shows forecasts and the shaded band shows the nominal 95% prediction interval for a single run. Each row count is a separate category.

Time prepared queries on native threads

The collector targets Windows x64 and calls SQLite through the sqlite3.dll C API. It creates a deterministic table and runs the same aggregate over four row limits, from 20,000 to 240,000 rows per client, with 1, 4, or 8 concurrent clients.

Each client gets its own native thread and read-only connection. It prepares the query, binds the row limit, runs a warmup, and checks the result. The collector opens the start gate after every client signals readiness. Each client then times the prepared query with QueryPerformanceCounter.

SetEvent(Worker^.Ready);
if WaitForSingleObject(Worker^.Gate, WaitMs) <> WAIT_OBJECT_0 then
raise Exception.Create('Timed start gate failed');
QueryPerformanceCounter(Worker^.Started);
Value := ExecuteQuery(Statement);
QueryPerformanceCounter(Worker^.Finished);

Elapsed time spans the earliest timed start to the latest timed finish. Thread creation, connection setup, statement preparation, and warmup happen before that window. The collector checks each timed result against the expected checksum.

Hand the timings to MIMERCode as CSV

Each of the four batches contains three repetitions of every row-limit and client-count combination. A fixed shuffle seed per batch makes the trial order reproducible. Each CSV row represents one timed trial.

batch,order,rows_per_client,clients,replicate,elapsed_ms
1,1,60000,4,2,1.903400
1,2,240000,1,1,7.218300
1,3,60000,1,1,1.766000

MIMERCode fits the model on the 36 runs in batch 1 and scores the 108 runs in batches 2–4 without refitting. Rows per client and client count are the two predictors. linreg_predict supplies the forecast and interval bounds. Both charts use the same scored rows.

local Fit := linreg_multiple(
map(Training, λ Run => Predictors(Run)), map(Training, λ Run => Run.elapsed_ms))
local Forecasts := linreg_predict(Fit, map(HeldOut, λ Run => Predictors(Run)))
local ScoredRuns := zip(HeldOut, Forecasts) |> map(λ Pair => ScoreRun(Pair[0], Pair[1]))

λ Pair => ScoreRun(Pair[0], Pair[1]) is a lambda, a small function written inline. For each pair, it calls ScoreRun with the measured run at index 0 and its forecast at index 1. The pipe operator |> passes the result on its left as the first argument to the function on its right. Here, zip pairs the runs with their forecasts, then map applies the lambda to every pair to build ScoredRuns.

Run it on Windows

Put the source files, the report sample’s read-only file policy, and the matching x64 sqlite3.dll in the working directory. With MPC and MIMERCodeRunner on the path, set $MpcRoot to the MPC directory containing rtl and compat, then run these commands in PowerShell 7.

$MpcRoot = "C:\path\to\mpc"
mpc -target=win64 -r CollectSQLiteBenchmark.mpr -u "$MpcRoot/rtl" -u "$MpcRoot/rtl/pal" -u "$MpcRoot/compat" -o CollectSQLiteBenchmark.exe
.\CollectSQLiteBenchmark.exe > sqlite-benchmark-runs.csv
MIMERCodeRunner MeasuredBenchmark.mc html > sqlite-benchmark.html

The collector creates a local mpc-benchmark.sqlite database. The renderer writes HTML with embedded SVG. Omit the html argument to get forecasts and validation metrics as JSON.

Read the result

The held-out mean absolute error was 0.41 ms. 106 of 108 runs fell within their nominal 95% prediction intervals. The median interval extended ±1.13 ms from the forecast.

These timings came from four back-to-back batches using SQLite 3.53.3 on one Windows machine with a warm database. The full report breaks down forecast error and coverage by batch and client count.

MIMERPascal collector

MIMERPascal code, directly using SQLite dll to measure query performance and collecting measurements.

program CollectSQLiteBenchmark;
// Windows x64. Build with MPC and place the MIMERCode sqlite3.dll beside the executable.
uses Windows, SysUtils;
type
PSQLiteHandle = ^Pointer;
TWorker = record
Rows: Integer;
Expected, Started, Finished: Int64;
Ready, Gate: THandle;
ErrorCode: Integer;
end;
PWorker = ^TWorker;
TTrial = record
Rows, Clients, Replicate: Integer;
end;
const
DatabaseName: AnsiString = 'mpc-benchmark.sqlite';
Query: AnsiString = 'SELECT sum((value * value) % 104729) FROM samples WHERE id <= ?';
SQLiteOK = 0;
SQLiteRow = 100;
SQLiteDone = 101;
WaitMs = 15000;
function sqlite3_open_v2(Name: PAnsiChar; DB: PSQLiteHandle; Flags: Integer; VFS: Pointer): Integer;
cdecl; external 'sqlite3.dll';
function sqlite3_close(DB: Pointer): Integer; cdecl; external 'sqlite3.dll';
function sqlite3_exec(DB: Pointer; SQL: PAnsiChar; Callback, User, Error: Pointer): Integer;
cdecl; external 'sqlite3.dll';
function sqlite3_prepare_v2(DB: Pointer; SQL: PAnsiChar; Bytes: Integer;
Statement: PSQLiteHandle; Tail: Pointer): Integer; cdecl; external 'sqlite3.dll';
function sqlite3_bind_int(Statement: Pointer; Number, Value: Integer): Integer; cdecl; external 'sqlite3.dll';
function sqlite3_step(Statement: Pointer): Integer; cdecl; external 'sqlite3.dll';
function sqlite3_reset(Statement: Pointer): Integer; cdecl; external 'sqlite3.dll';
function sqlite3_finalize(Statement: Pointer): Integer; cdecl; external 'sqlite3.dll';
function sqlite3_column_int64(Statement: Pointer; Number: Integer): Int64; cdecl; external 'sqlite3.dll';
function sqlite3_libversion: PAnsiChar; cdecl; external 'sqlite3.dll';
procedure Check(Status: Integer; const Operation: string);
begin
if Status <> SQLiteOK then
raise Exception.Create(Operation + ' failed with SQLite status ' + IntToStr(Status));
end;
procedure InitializeDatabase;
const
Setup: AnsiString =
'CREATE TABLE IF NOT EXISTS samples(id INTEGER PRIMARY KEY, value INTEGER NOT NULL);' +
'WITH RECURSIVE ids(i) AS (VALUES (1) UNION ALL SELECT i+1 FROM ids WHERE i<240000) ' +
'INSERT OR IGNORE INTO samples SELECT i, (i*37+13)%4093 FROM ids;';
var
DB: Pointer;
begin
DB := nil;
try
Check(sqlite3_open_v2(PAnsiChar(DatabaseName), @DB, 6, nil), 'Open database');
Check(sqlite3_exec(DB, PAnsiChar(Setup), nil, nil, nil), 'Initialize benchmark rows');
finally
if DB <> nil then sqlite3_close(DB);
end;
end;
function ExecuteQuery(Statement: Pointer): Int64;
begin
if sqlite3_step(Statement) <> SQLiteRow then
raise Exception.Create('Query failed to return its checksum');
Result := sqlite3_column_int64(Statement, 0);
if sqlite3_step(Statement) <> SQLiteDone then
raise Exception.Create('Query returned more than one row');
end;
function MeasureClient(Param: Pointer): NativeInt;
var
Worker: PWorker;
DB, Statement: Pointer;
Value: Int64;
begin
Worker := PWorker(Param);
DB := nil;
Statement := nil;
try
try
Check(sqlite3_open_v2(PAnsiChar(DatabaseName), @DB, 1, nil), 'Open read-only connection');
Check(sqlite3_prepare_v2(DB, PAnsiChar(Query), -1, @Statement, nil), 'Prepare query');
Check(sqlite3_bind_int(Statement, 1, Worker^.Rows), 'Set row limit');
if ExecuteQuery(Statement) <> Worker^.Expected then
raise Exception.Create('Warmup checksum mismatch');
Check(sqlite3_reset(Statement), 'Reset warmed statement');
SetEvent(Worker^.Ready);
if WaitForSingleObject(Worker^.Gate, WaitMs) <> WAIT_OBJECT_0 then
raise Exception.Create('Timed start gate failed');
QueryPerformanceCounter(Worker^.Started);
Value := ExecuteQuery(Statement);
QueryPerformanceCounter(Worker^.Finished);
if Value <> Worker^.Expected then
raise Exception.Create('Timed checksum mismatch');
except
Worker^.ErrorCode := 1;
end;
finally
SetEvent(Worker^.Ready);
if Statement <> nil then sqlite3_finalize(Statement);
if DB <> nil then sqlite3_close(DB);
end;
Result := Worker^.ErrorCode;
end;
function MeasureTrial(const Trial: TTrial; Frequency: Int64): Double;
var
Workers: array[0..7] of TWorker;
Threads: array[0..7] of THandle;
Gate: THandle;
I: Integer;
Began, Ended, Expected: Int64;
begin
FillChar(Workers, SizeOf(Workers), 0);
FillChar(Threads, SizeOf(Threads), 0);
Expected := 0;
for I := 1 to Trial.Rows do
Inc(Expected, Sqr((I * 37 + 13) mod 4093) mod 104729);
Gate := CreateEventW(nil, True, False, nil);
if Gate = 0 then raise Exception.Create('Cannot create start gate');
try
for I := 0 to Trial.Clients - 1 do
begin
Workers[I].Ready := CreateEventW(nil, True, False, nil);
if Workers[I].Ready = 0 then raise Exception.Create('Cannot create ready event');
Workers[I].Gate := Gate;
Workers[I].Rows := Trial.Rows;
Workers[I].Expected := Expected;
Threads[I] := THandle(BeginThread(MeasureClient, @Workers[I]));
if (Threads[I] = 0) or (Threads[I] = THandle(-1)) then
begin
Threads[I] := 0;
raise Exception.Create('Cannot start SQLite client');
end;
end;
for I := 0 to Trial.Clients - 1 do
if WaitForSingleObject(Workers[I].Ready, WaitMs) <> WAIT_OBJECT_0 then
raise Exception.Create('Client warmup timed out');
SetEvent(Gate);
Began := High(Int64);
Ended := 0;
for I := 0 to Trial.Clients - 1 do
begin
if WaitForSingleObject(Threads[I], WaitMs) <> WAIT_OBJECT_0 then
raise Exception.Create('Timed query did not finish');
if Workers[I].ErrorCode <> 0 then
raise Exception.Create('Timed client failed or returned the wrong checksum');
if Workers[I].Started < Began then Began := Workers[I].Started;
if Workers[I].Finished > Ended then Ended := Workers[I].Finished;
end;
Result := (Ended - Began) * 1000.0 / Frequency;
finally
SetEvent(Gate);
for I := 0 to Trial.Clients - 1 do
begin
if Threads[I] <> 0 then
begin
WaitForSingleObject(Threads[I], INFINITE);
CloseHandle(Threads[I]);
end;
if Workers[I].Ready <> 0 then CloseHandle(Workers[I].Ready);
end;
CloseHandle(Gate);
end;
end;
const
RowLimits: array[0..3] of Integer = (20000, 60000, 120000, 240000);
ClientCounts: array[0..2] of Integer = (1, 4, 8);
var
Trials: array[0..35] of TTrial;
Temp: TTrial;
Batch, I, J, R, C, Rep: Integer;
Frequency: Int64;
begin
if (ParamCount = 1) and (ParamStr(1) = '--sqlite-version') then
begin
WriteLn(AnsiString(sqlite3_libversion));
Halt(0);
end;
InitializeDatabase;
if not QueryPerformanceFrequency(Frequency) then
raise Exception.Create('Performance counter unavailable');
WriteLn('batch,order,rows_per_client,clients,replicate,elapsed_ms');
for Batch := 1 to 4 do
begin
I := 0;
for R := Low(RowLimits) to High(RowLimits) do
for C := Low(ClientCounts) to High(ClientCounts) do
for Rep := 1 to 3 do
begin
Trials[I].Rows := RowLimits[R];
Trials[I].Clients := ClientCounts[C];
Trials[I].Replicate := Rep;
Inc(I);
end;
RandSeed := 404 + Batch;
for I := High(Trials) downto 1 do
begin
J := Random(I + 1);
Temp := Trials[I];
Trials[I] := Trials[J];
Trials[J] := Temp;
end;
for I := Low(Trials) to High(Trials) do
WriteLn(Batch, ',', I + 1, ',', Trials[I].Rows, ',', Trials[I].Clients,
',', Trials[I].Replicate, ',', MeasureTrial(Trials[I], Frequency):0:6);
end;
end.

MIMERCode analysis and charts

MeasuredBenchmark.mc

Can output a rendered HTML graph or the collected and calculated data as JSON depending on command line argument.

// Run MIMERCodeRunner MeasuredBenchmark.mc from this folder.
// Keep the CSV and read-only file policy beside it. Pass html to render the charts.
program MeasuredBenchmark
view BenchmarkReport as Report
props
ScoredRuns: required Array
IntervalRuns: required Array
end
render
page
chart type: "range_area", width: 760, height: 320, data: IntervalRuns,
title: "Measured and forecast runtime in batch 4 (ms)",
x: "row_label", low: "prediction_low", high: "prediction_high",
chart_padding: (14, 24, 48, 40), marker_size: 8
x_axis label: "Rows per client · 8 clients, replicate 1" end
series field: "fitted", label: "95% prediction interval", color: "#A4BFDE" end
series field: "fitted", label: "Forecast", type: "line", color: "#245891" end
series field: "elapsed_ms", label: "Measured", type: "scatter", color: "#9E472B" end
end
chart type: "scatter_xy", width: 760, height: 340, data: ScoredRuns,
title: "Held-out residuals vs. fitted runtime", x: "fitted", label: "description",
chart_padding: (14, 24, 42, 40), marker_size: 8
x_axis label: "Fitted runtime (ms)" end
y_axis label: "Residual (ms)", grid: true end
series field: "clients_1", label: "1 client", color: "#276A96", marker: "circle" end
series field: "clients_4", label: "4 clients", color: "#257A5A", marker: "square" end
series field: "clients_8", label: "8 clients", color: "#9E472B", marker: "diamond" end
end
end
end
end
func ParseRun(Row)
return { batch: StrToInt(Row["batch"]), clients: StrToInt(Row["clients"]),
rows: StrToInt(Row["rows_per_client"]), replicate: StrToInt(Row["replicate"]),
elapsed_ms: StrToFloat(Row["elapsed_ms"]) }
end
func Predictors(Run)
return [Run.rows / 100000.0, Run.clients]
end
func ScoreRun(Run, Forecast)
local Residual := Run.elapsed_ms - Forecast.predicted
local Scored := { batch: Run.batch, clients: Run.clients, rows: Run.rows,
replicate: Run.replicate, elapsed_ms: Run.elapsed_ms, fitted: Forecast.predicted,
prediction_low: Forecast.prediction_low, prediction_high: Forecast.prediction_high,
residual: Residual,
covered: Run.elapsed_ms >= Forecast.prediction_low and Run.elapsed_ms <= Forecast.prediction_high,
row_label: Str(Run.rows),
description: f"Batch {Run.batch}, {Run.clients} clients, {Run.rows} rows" }
Scored[f"clients_{Run.clients}"] := Residual
return Scored
end
func Summarize(ScoredRuns, Fit)
local BatchesByClient := groupby(ScoredRuns, λ Run => f"{Run.batch}/{Run.clients}")
local Groups := {}
for Key in BatchesByClient.keys() do
local Runs := BatchesByClient[Key]
Groups[Key] := { batch: Runs[0].batch, clients: Runs[0].clients, runs: Runs.length,
hits: Length(filter(Runs, λ Run => Run.covered)),
absolute_error: reduce(Runs, λ(Total, Run) => Total + Abs(Run.residual), 0.0),
signed_error: reduce(Runs, λ(Total, Run) => Total + Run.residual, 0.0) }
end
return { training_runs: Fit.n, coefficients: Fit.coefficients, test_runs: ScoredRuns.length,
hits: Length(filter(ScoredRuns, λ Run => Run.covered)),
mae: reduce(ScoredRuns, λ(Total, Run) => Total + Abs(Run.residual), 0.0) / ScoredRuns.length,
groups: Groups, forecasts: ScoredRuns }
end
local Csv := bytes_to_str(file_read_bytes("sqlite-benchmark-runs.csv"), "utf-8")
local Runs := csv_parse(Csv, {headers: true}) |> map(λ Row => ParseRun(Row))
local Training := filter(Runs, λ Run => Run.batch = 1)
local HeldOut := filter(Runs, λ Run => Run.batch > 1)
local Fit := linreg_multiple(
map(Training, λ Run => Predictors(Run)), map(Training, λ Run => Run.elapsed_ms))
local Forecasts := linreg_predict(Fit, map(HeldOut, λ Run => Predictors(Run)))
local ScoredRuns := zip(HeldOut, Forecasts) |> map(λ Pair => ScoreRun(Pair[0], Pair[1]))
if Args.length > 0 and Args[0] = "html" then
local IntervalRuns := ScoredRuns
|> filter(λ Run => Run.batch = 4 and Run.clients = 8 and Run.replicate = 1)
|> sorted(λ(A, B) => A.rows - B.rows)
WriteLn(RenderReportHtml("BenchmarkReport", { ScoredRuns: ScoredRuns, IntervalRuns: IntervalRuns }))
else
WriteLn(json_stringify(Summarize(ScoredRuns, Fit)))
end
end

MIMERCode security policy file

MeasuredBenchmark.security.json

Placed in same directory as MeasuredBenchmark.mc.
Allow read access to the collected csv file. If no security policy file is available, the MeasuredBenchmark.mc would be denied attempting to read the file.

{
  "file": {
    "rules": [
      {
        "path": "./sqlite-benchmark-runs.csv",
        "allow_read": true
      }
    ]
  }
}

Loading

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.