From b0c7e5a3bd1e590d49f793da65a74d646eadb538 Mon Sep 17 00:00:00 2001
From: Stefan Majewsky <majewsky@gmx.net>
Date: Wed, 29 Apr 2026 23:36:28 +0200
Subject: benchmark: compare InsertAndDelete to plain SQLite using QueryRow for
 INSERT

---
 benchmark/benchmark_test.go | 48 ++++++++++++++++++++++++++++++++++++++-------
 1 file changed, 41 insertions(+), 7 deletions(-)

(limited to 'benchmark/benchmark_test.go')

diff --git a/benchmark/benchmark_test.go b/benchmark/benchmark_test.go
index 7cf94b7..98d5818 100644
--- a/benchmark/benchmark_test.go
+++ b/benchmark/benchmark_test.go
@@ -23,7 +23,7 @@ import (
 // This is not a real benchmark (obviously).
 // Its purpose is to be the first line that is printed, while having one of the longest names,
 // so that all other results are aligned with it and the table looks nice.
-func BenchmarkHeadingHeadingHeadingHeadingHeadingHeadingHeading(b *testing.B) {
+func BenchmarkHeadingHeadingHeadingHeadingHeadingHeadingHeadingHeading(b *testing.B) {
 	for b.Loop() {
 		time.Sleep(time.Microsecond)
 	}
@@ -312,7 +312,7 @@ func BenchmarkInsertAndDelete(b *testing.B) {
 				assert.Equal(b, result.RowsAffected, int64(batchSize))
 			}
 
-			insertAndDeleteWithStraightSqlite := func(b *testing.B) {
+			insertAndDeleteWithStraightExec := func(b *testing.B) {
 				ids := make([]int64, batchSize)
 				for idx := range ids {
 					result := must.Return(db.Exec(`INSERT INTO entries (message) VALUES (?)`, "hello"))(b)
@@ -323,7 +323,7 @@ func BenchmarkInsertAndDelete(b *testing.B) {
 				}
 			}
 
-			insertAndDeleteWithPreparedSqlite := func(b *testing.B) {
+			insertAndDeleteWithPreparedExec := func(b *testing.B) {
 				ids := make([]int64, batchSize)
 				stmtInsert := must.Return(db.Prepare(`INSERT INTO entries (message) VALUES (?)`))(b)
 				defer stmtInsert.Close()
@@ -338,6 +338,30 @@ func BenchmarkInsertAndDelete(b *testing.B) {
 				}
 			}
 
+			insertAndDeleteWithStraightQueryRow := func(b *testing.B) {
+				ids := make([]int64, batchSize)
+				for idx := range ids {
+					must.Succeed(b, db.QueryRow(`INSERT INTO entries (message) VALUES (?) RETURNING id`, "hello").Scan(&ids[idx]))
+				}
+				for _, id := range ids {
+					_ = must.Return(db.Exec(`DELETE FROM entries WHERE id = ?`, id))(b)
+				}
+			}
+
+			insertAndDeleteWithPreparedQueryRow := func(b *testing.B) {
+				ids := make([]int64, batchSize)
+				stmtInsert := must.Return(db.Prepare(`INSERT INTO entries (message) VALUES (?) RETURNING id`))(b)
+				defer stmtInsert.Close()
+				for idx := range ids {
+					must.Succeed(b, stmtInsert.QueryRow("hello").Scan(&ids[idx]))
+				}
+				stmtDelete := must.Return(db.Prepare(`DELETE FROM entries WHERE id = ?`))(b)
+				defer stmtDelete.Close()
+				for _, id := range ids {
+					_ = must.Return(stmtDelete.Exec(id))(b)
+				}
+			}
+
 			// run once to prewarm caches (if any)
 			insertAndDeleteWithOblast(b)
 			insertAndDeleteWithGorp(b)
@@ -358,14 +382,24 @@ func BenchmarkInsertAndDelete(b *testing.B) {
 					insertAndDeleteWithOblast(b)
 				}
 			})
-			b.Run("just SQLite (straight)", func(b *testing.B) {
+			b.Run("just SQLite (straight Exec)", func(b *testing.B) {
 				for b.Loop() {
-					insertAndDeleteWithStraightSqlite(b)
+					insertAndDeleteWithStraightExec(b)
 				}
 			})
-			b.Run("just SQLite (prepared)", func(b *testing.B) {
+			b.Run("just SQLite (prepared Exec)", func(b *testing.B) {
+				for b.Loop() {
+					insertAndDeleteWithPreparedExec(b)
+				}
+			})
+			b.Run("just SQLite (straight QueryRow)", func(b *testing.B) {
+				for b.Loop() {
+					insertAndDeleteWithStraightQueryRow(b)
+				}
+			})
+			b.Run("just SQLite (prepared QueryRow)", func(b *testing.B) {
 				for b.Loop() {
-					insertAndDeleteWithPreparedSqlite(b)
+					insertAndDeleteWithPreparedQueryRow(b)
 				}
 			})
 		})
-- 
cgit v1.2.3