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_ms1,1,60000,4,2,1.9034001,2,240000,1,1,7.2183001,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.csvMIMERCodeRunner 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))) endend
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
}
]
}
}
![]()








