Created
April 29, 2023 11:18
-
-
Save lzap/752b324a5689ef57dd351be00ce23cbd to your computer and use it in GitHub Desktop.
Fixed benchmark and results from the https://blog.jetbrains.com/go/2023/04/27/comparing-db-packages/ blog post
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package main | |
import ( | |
"context" | |
"database/sql" | |
"fmt" | |
"log" | |
"time" | |
"gorm.io/gorm" | |
"github.com/glebarez/sqlite" | |
"github.com/jmoiron/sqlx" | |
sqlc "github.com/rexfordnyrk/go-db-comparison/benchmarks/sqlc_generated" | |
//_ "modernc.org/sqlite" | |
) | |
func init() { | |
var err error | |
db, err = sql.Open("sqlite", "file::memory:?cache=shared") | |
if err != nil { | |
panic(err) | |
} | |
dbx, err = sqlx.Open("sqlite", "file::memory:?cache=shared") | |
if err != nil { | |
panic(err) | |
} | |
db2, err = sql.Open("sqlite", "file::memory:?cache=shared") | |
if err != nil { | |
panic(err) | |
} | |
dbc = sqlc.New(db2) | |
gdb, err = gorm.Open(sqlite.Open("file::memory:?cache=shared"), &gorm.Config{}) | |
if err != nil { | |
panic(err) | |
} | |
} | |
var ( | |
gdb *gorm.DB | |
db *sql.DB | |
db2 *sql.DB | |
dbx *sqlx.DB | |
dbc *sqlc.Queries | |
) | |
func setup(db *sql.DB) { | |
clear() | |
table := `CREATE TABLE students ( | |
id integer, | |
first_name varchar(50) not null, | |
last_name varchar(50) not null, | |
date_of_birth datetime not null, | |
email varchar(50) not null, | |
address varchar(50) not null, | |
gender varchar(50) not null | |
);` | |
_, err := db.Exec(table) | |
if err != nil { | |
panic(err) | |
} | |
affected, err := db.Exec(records) | |
if err != nil { | |
panic(err) | |
} | |
fmt.Printf("Affected rows: %d\n", affected) | |
} | |
func clear() { | |
_, err := db.Exec(`DROP TABLE if exists students`) | |
if err != nil { | |
panic(err) | |
} | |
} | |
type Student struct { | |
ID int64 | |
Fname string `db:"first_name"` | |
Lname string `db:"last_name"` | |
DateOfBirth time.Time `db:"date_of_birth"` | |
Email string `db:"email"` | |
Address string `db:"address"` | |
Gender string `db:"gender"` | |
} | |
func DbSqlQueryStudentWithLimit(limit int) int { | |
var students []Student | |
rows, err := db.Query("SELECT * FROM students limit ?", limit) | |
if err != nil { | |
log.Fatalf("DbSqlQueryStudentWithLimit %d %v", limit, err) | |
} | |
defer rows.Close() | |
// Loop through rows, using Scan to assign column data to struct fields. | |
for rows.Next() { | |
var s Student | |
if err := rows.Scan(&s.ID, &s.Fname, &s.Lname, &s.DateOfBirth, &s.Email, &s.Address, &s.Gender); err != nil { | |
log.Fatalf("DbSqlQueryStudentWithLimit %d %v", limit, err) | |
} | |
students = append(students, s) | |
} | |
if err := rows.Err(); err != nil { | |
log.Fatalf("DbSqlQueryStudentWithLimit %d %v", limit, err) | |
} | |
return len(students) | |
} | |
func SqlxQueryStudentWithLimit(limit int) int { | |
var students []Student | |
err := dbx.Select(&students, "SELECT * FROM students LIMIT ?", limit) | |
if err != nil { | |
log.Fatalf("SqlxQueryStudentWithLimit %d %v", limit, err) | |
} | |
return len(students) | |
} | |
func SqlcQueryStudentWithLimit(limit int) int { | |
students, err := dbc.FetchStudents(context.Background(), int32(limit)) | |
if err != nil { | |
log.Fatalf("SqlcQueryStudentWithLimit %d %v", limit, err) | |
} | |
return len(students) | |
} | |
func GormQueryStudentWithLimit(limit int) int { | |
var students []Student | |
if err := gdb.Limit(limit).Find(&students).Error; err != nil { | |
log.Fatalf("GormQueryStudentWithLimit %d %v", limit, err) | |
} | |
return len(students) | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package main | |
import ( | |
"fmt" | |
"testing" | |
) | |
func Benchmark(b *testing.B) { | |
setup(db) | |
setup(dbx.DB) | |
setup(db2) | |
setup(db) | |
defer clear() | |
// Benchmark goes in here | |
limits := []int{ | |
1, | |
10, | |
100, | |
1000, | |
10000, | |
15000, | |
} | |
for _, lim := range limits { // Fetch varying number of rows | |
fmt.Printf("================================== BENCHMARKING %d RECORDS ======================================\n", lim) | |
// Benchmark Database/sql | |
b.Run(fmt.Sprintf("Database/sql limit:%d ", lim), func(b *testing.B) { | |
for i := 0; i < b.N; i++ { | |
if DbSqlQueryStudentWithLimit(lim) != lim { | |
b.Fatalf("DbSqlQueryStudentWithLimit did not return %d records", lim) | |
} | |
} | |
}) | |
// Benchmark Sqlx | |
b.Run(fmt.Sprintf("Sqlx limit:%d ", lim), func(b *testing.B) { | |
for i := 0; i < b.N; i++ { | |
if SqlxQueryStudentWithLimit(lim) != lim { | |
b.Fatalf("SqlxQueryStudentWithLimit did not return %d records", lim) | |
} | |
} | |
}) | |
// Benchmark Sqlc | |
/* | |
b.Run(fmt.Sprintf("Sqlc limit:%d ", lim), func(b *testing.B) { | |
for i := 0; i < b.N; i++ { | |
if SqlcQueryStudentWithLimit(lim) != lim { | |
b.Fatalf("SqlcQueryStudentWithLimit did not return %d records", lim) | |
} | |
} | |
})*/ | |
// Benchmark GORM | |
b.Run(fmt.Sprintf("GORM limit:%d ", lim), func(b *testing.B) { | |
for i := 0; i < b.N; i++ { | |
if GormQueryStudentWithLimit(lim) != lim { | |
b.Fatalf("GormQueryStudentWithLimit did not return %d records", lim) | |
} | |
} | |
}) | |
fmt.Println("=================================================================================================") | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Macbook-16-M1Pro% go test -bench=. . | |
================================== BENCHMARKING 1 RECORDS ====================================== | |
goos: darwin | |
goarch: arm64 | |
pkg: temp | |
Benchmark/Database/sql_limit:1_-10 94777 12491 ns/op | |
Benchmark/Sqlx_limit:1_-10 87447 13437 ns/op | |
Benchmark/GORM_limit:1_-10 68102 17199 ns/op | |
================================================================================================= | |
================================== BENCHMARKING 10 RECORDS ====================================== | |
Benchmark/Database/sql_limit:10_-10 33517 35899 ns/op | |
Benchmark/Sqlx_limit:10_-10 31227 38302 ns/op | |
Benchmark/GORM_limit:10_-10 27505 43593 ns/op | |
================================================================================================= | |
================================== BENCHMARKING 100 RECORDS ====================================== | |
Benchmark/Database/sql_limit:100_-10 4564 255637 ns/op | |
Benchmark/Sqlx_limit:100_-10 4304 277219 ns/op | |
Benchmark/GORM_limit:100_-10 3711 311985 ns/op | |
================================================================================================= | |
================================== BENCHMARKING 1000 RECORDS ====================================== | |
Benchmark/Database/sql_limit:1000_-10 484 2448140 ns/op | |
Benchmark/Sqlx_limit:1000_-10 457 2594044 ns/op | |
Benchmark/GORM_limit:1000_-10 400 2969418 ns/op | |
================================================================================================= | |
================================== BENCHMARKING 10000 RECORDS ====================================== | |
Benchmark/Database/sql_limit:10000_-10 46 24620349 ns/op | |
Benchmark/Sqlx_limit:10000_-10 45 26265074 ns/op | |
Benchmark/GORM_limit:10000_-10 39 29704668 ns/op | |
================================================================================================= | |
================================== BENCHMARKING 15000 RECORDS ====================================== | |
Benchmark/Database/sql_limit:15000_-10 31 36463359 ns/op | |
Benchmark/Sqlx_limit:15000_-10 27 39348892 ns/op | |
Benchmark/GORM_limit:15000_-10 26 43955534 ns/op | |
================================================================================================= | |
PASS | |
ok temp 24.824s |
Please do not make any conclusions from this "benchmark", I was just researching a bug in the benchmark code. This is NOT how you should benchmark SQL libraries.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The context:
I have found out that the benchmark contains a bug in benchmark.go line 65. There is incorrectly handled error which hides one significant problem which is that the setup function responsible for loading the example data fails with: SQL logic error: table students has no column named first_name
Therefore the whole benchmark is performed on an empty table, there is literally no data so all this tests are roundtrips to database with empty results and this is repeated over and over again. No mapping is performed since there is no data, all tested functions also return empty slices.
After I fixed this, another issue is that in the example data, id is not unique. Also are these real e-mails of real people?! Anyways, after removal of the primary key which is unused the benchmark now shows numbers which are closer to what I would expect (tested on sqlite3 with in-memory database):