Home Blog CV Projects Patterns Notes Book Colophon Search

Python SQLite3 Benchmark

13 Aug, 2022

This is to test batch inserts. Not a transaction per insert.

import threading
from multiprocessing import Pool
import time
import sqlite3
import sys

numt = int(sys.argv[1])
num = int(1000.0/numt)
num2 = 1000

def thread_function(name):
    con = sqlite3.connect('example'+str(name)+'.db')
    cur = con.cursor()
    cur.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''')
    for _ in range(num):
        cur.execute('begin')
        for _ in range(num2):
            cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
        cur.execute('commit')

if __name__ == "__main__":
    now = time.monotonic()
    if False:
        with Pool(4) as p:
            print(p.map(thread_function, [1, 2, 3, 4]))
    else:
        threads = []
        for name in range(numt):
            t = threading.Thread(target=thread_function, args=(name,))
            t.start()
            threads.append(t)
        [t.join() for t in threads]
    print((1.0*numt*num*num2)/(time.monotonic() - now))
    print(time.monotonic() - now)

Here's the same thing in Go as main.go but only using 1 CPU core I think:

package main

import (
        "database/sql"
        "fmt"
        _ "github.com/mattn/go-sqlite3"
        "time"
)

func main() {
        db, err := sql.Open("sqlite3", "./foo.db")
        checkErr(err)
        defer db.Close()

        start:=time.Now()

        stmt, err := db.Prepare("CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)")
        checkErr(err)
        _, err = stmt.Exec()
        checkErr(err)

        for i := 0; i < 1000; i++ {
                tx, err := db.Begin()
                checkErr(err)
                stmt, err := tx.Prepare("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
                checkErr(err)
                for j := 0; j < 1000; j++ {
                        _, err = stmt.Exec()
                        checkErr(err)
                }
                tx.Commit()
        }
       fmt.Printf("%s\n", time.Now().Sub(start))
}

func checkErr(err error) {
        if err != nil {
              panic(err)
        }
}

Then run:

go mod init example.com/sqlite3-bench
go mod tidy
go run main.go

Python SQLite3 Benchmark

Comments

Add Comment