GoSqliteBench/SqliteBench_test.go

package main

import (
	"context"
	"database/sql"
	"math/rand"
	"path/filepath"
	"strconv"
	"testing"

	_ "github.com/mattn/go-sqlite3"
	"github.com/stretchr/testify/require"
	"zombiezen.com/go/sqlite"
	"zombiezen.com/go/sqlite/sqlitex"
)

func Benchmark_Zombiezen(b *testing.B) {

	// Create and open database

	dbpool, err := sqlitex.Open(filepath.Join(b.TempDir(), "db.db"), sqlite.OpenCreate|sqlite.OpenReadWrite|sqlite.OpenWAL|sqlite.OpenNoMutex, 100)
	require.NoError(b, err)

	// Create table

	conn := dbpool.Get(context.Background())
	err = sqlitex.Exec(conn, "create table test(a text not null primary key, b text not null, c text not null default 'd');", nil)
	dbpool.Put(conn)
	require.NoError(b, err)

	// Insert 1000 entries

	for i := 0; i < 1000; i++ {
		str := strconv.Itoa(i)
		conn := dbpool.Get(context.Background())
		_ = sqlitex.Exec(conn, "insert into test(a, b) values (?, ?);", nil, str, str)
		dbpool.Put(conn)
	}

	// Queries

	b.Run("Parallel Queries with Pool", func(b *testing.B) {
		b.RunParallel(func(p *testing.PB) {
			for p.Next() {
				var as, bs, cs string
				conn := dbpool.Get(context.Background())
				_ = sqlitex.Exec(conn, "select a, b, c from test where a = ?;", func(stmt *sqlite.Stmt) error {
					as = stmt.ColumnText(0)
					bs = stmt.ColumnText(1)
					cs = stmt.ColumnText(2)
					return nil
				}, strconv.Itoa(rand.Intn(1000)))
				dbpool.Put(conn)
				if as == "" || bs == "" || cs == "" {
					b.FailNow()
				}
			}
		})
	})

	b.Run("Sequential Queries with Pool", func(b *testing.B) {
		for i := 0; i < b.N; i++ {
			var as, bs, cs string
			conn := dbpool.Get(context.Background())
			_ = sqlitex.Exec(conn, "select a, b, c from test where a = ?;", func(stmt *sqlite.Stmt) error {
				as = stmt.ColumnText(0)
				bs = stmt.ColumnText(1)
				cs = stmt.ColumnText(2)
				return nil
			}, strconv.Itoa(rand.Intn(1000)))
			dbpool.Put(conn)
			if as == "" || bs == "" || cs == "" {
				b.FailNow()
			}
		}
	})

	b.Run("Sequential Queries without Pool", func(b *testing.B) {
		conn := dbpool.Get(context.Background())
		defer dbpool.Put(conn)
		for i := 0; i < b.N; i++ {
			var as, bs, cs string
			_ = sqlitex.Exec(conn, "select a, b, c from test where a = ?;", func(stmt *sqlite.Stmt) error {
				as = stmt.ColumnText(0)
				bs = stmt.ColumnText(1)
				cs = stmt.ColumnText(2)
				return nil
			}, strconv.Itoa(rand.Intn(1000)))
			if as == "" || bs == "" || cs == "" {
				b.FailNow()
			}
		}
	})

}

func Benchmark_Mattn(b *testing.B) {

	// Create and open database

	db, err := sql.Open("sqlite3", filepath.Join(b.TempDir(), "db.db")+"?mode=rwc&_journal_mode=WAL")
	require.NoError(b, err)
	db.SetMaxOpenConns(100)

	// Create table

	_, err = db.Exec("create table test(a text not null primary key, b text not null, c text not null default 'd');")
	require.NoError(b, err)

	// Insert 1000 entries

	for i := 0; i < 1000; i++ {
		str := strconv.Itoa(i)
		_, _ = db.Exec("insert into test(a, b) values (?, ?);", str, str)
	}

	// Queries

	b.Run("Parallel Queries", func(b *testing.B) {
		b.RunParallel(func(p *testing.PB) {
			for p.Next() {
				var as, bs, cs string
				rows, _ := db.Query("select a, b, c from test where a = ?;", strconv.Itoa(rand.Intn(1000)))
				for rows.Next() {
					rows.Scan(&as, &bs, &cs)
				}
				if as == "" || bs == "" || cs == "" {
					b.FailNow()
				}
			}
		})
	})

	b.Run("Sequential Queries", func(b *testing.B) {
		for i := 0; i < b.N; i++ {
			var as, bs, cs string
			rows, _ := db.Query("select a, b, c from test where a = ?;", strconv.Itoa(rand.Intn(1000)))
			for rows.Next() {
				rows.Scan(&as, &bs, &cs)
			}
			if as == "" || bs == "" || cs == "" {
				b.FailNow()
			}
		}
	})

}