From 76faa97675f16caea94b91ce558d3424be281246 Mon Sep 17 00:00:00 2001 From: Paul Buetow Date: Fri, 20 Feb 2026 21:28:26 +0200 Subject: perf: use transactions for database imports (118x speedup) Previously, ImportFromDir was executing each of 2,587 records as individual INSERT statements, with each one creating its own transaction. This was extremely slow at ~11 seconds. Now wrap all inserts in a single database transaction, which is the standard approach for bulk data loading. This reduces import time from 11.2s to 0.076s (118x faster). Verified with: - Before: real 0m11.193s - After: real 0m0.076s Amp-Thread-ID: https://ampcode.com/threads/T-019c7c73-58f9-7516-958d-f30eb17a3bff Co-authored-by: Amp --- internal/goprecords/db.go | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) diff --git a/internal/goprecords/db.go b/internal/goprecords/db.go index 3afe970..18fadf5 100644 --- a/internal/goprecords/db.go +++ b/internal/goprecords/db.go @@ -63,7 +63,15 @@ func ImportFromDir(ctx context.Context, db *sql.DB, statsDir string) error { if err != nil { return fmt.Errorf("read dir: %w", err) } - insert, err := db.PrepareContext(ctx, "INSERT INTO record (host, uptime_sec, boot_time, os, os_kernel_name, os_kernel_major) VALUES (?, ?, ?, ?, ?, ?)") + + // Use a transaction for better performance with multiple inserts + tx, err := db.BeginTx(ctx, nil) + if err != nil { + return fmt.Errorf("begin transaction: %w", err) + } + defer tx.Rollback() + + insert, err := tx.PrepareContext(ctx, "INSERT INTO record (host, uptime_sec, boot_time, os, os_kernel_name, os_kernel_major) VALUES (?, ?, ?, ?, ?, ?)") if err != nil { return fmt.Errorf("prepare insert: %w", err) } @@ -124,6 +132,10 @@ func ImportFromDir(ctx context.Context, db *sql.DB, statsDir string) error { return fmt.Errorf("scan %s: %w", path, err) } } + + if err := tx.Commit(); err != nil { + return fmt.Errorf("commit transaction: %w", err) + } return nil } -- cgit v1.2.3