SQLite.jl appears to be 400 times slower than Python's `sqlite3`

[ForceBru]

I was trying to write 50988 entries to a database and noticed that it was really slow (tens of seconds). So I created a simple benchmark in Julia and Python.

Julia

begin
	import Pkg
	Pkg.activate(temp=true)
	Pkg.add(["DBInterface", "SQLite"])
end

using DBInterface, SQLite

const DB_FILE = "DB_JL.sqlite"
const DATA_SIZE = 50988
my_data = rand(DATA_SIZE)

conn = SQLite.DB(DB_FILE)

DBInterface.execute(conn, "CREATE TABLE test (number REAL)")

@info "Test with loop"
stmt = DBInterface.prepare(conn, "INSERT INTO test VALUES(?)")
@time for x ∈ my_data
	DBInterface.execute(stmt, (x, ))
end

@info "Test with executemany"
stmt = DBInterface.prepare(conn, "INSERT INTO test VALUES(:col1)")
@time DBInterface.executemany(stmt, (col1=my_data, ))

Output

...
  [a10d1c49] + DBInterface v2.4.1
  [0aa819cd] + SQLite v1.1.4
...
[ Info: Test with loop
 86.731634 seconds (892.30 k allocations: 58.522 MiB, 0.01% gc time, 0.22% compilation time)
[ Info: Test with executemany
 75.921188 seconds (478.19 k allocations: 43.262 MiB, 0.01% gc time, 0.11% compilation time)

Python

import sqlite3
import random, time

DB_FILE = "DB_PY.sqlite"
DATA_SIZE = 50988
my_data = [random.random() for _ in range(DATA_SIZE)]

conn = sqlite3.connect("DB_PY.sqlite")
conn.execute("CREATE TABLE test (number REAL)")
conn.commit()

print("Test with loop")
begin = time.time()
for x in my_data:
	conn.execute("INSERT INTO test VALUES(?)", (x, ))
# Measure the time it takes to write everything
conn.commit()
elapsed = time.time() - begin
print(f"Time: {elapsed:.6} seconds")

print("Test with executemany")
begin = time.time()
conn.executemany("INSERT INTO test VALUES(?)", [(x, ) for x in my_data])
conn.commit()
elapsed = time.time() - begin
print(f"Time: {elapsed:.6} seconds")

conn.close()

Output

Test with loop
Time: 0.190992 seconds
Test with executemany
Time: 0.0642009 seconds

---

I checked, both database files are 1.6 MB, so the data are being written by both.

• Julia's best timing is 75 (!) seconds using DBInterface.executemany
• Python's worst timing is 0.19 (!) seconds using a loop

...so Julia is about 400 times slower here.

• Julia's executemany timing: 75 seconds
• Python's executemany timing: 0.064 seconds

Apparently, Python's executemany is about 1171 times (!!) faster than Julia's.

---

What's going on here? What am I doing wrong? It looks like each call to DBInterface.execute is immediately writing stuff to disk, while neither conn.execute, nor conn.executemany write anything (at first I forgot to conn.close(), and the DB file was around 20 KB, so almost empty). Apparently, conn.commit() writes everything in one go, which is faster than writing one row at a time. Does SQLite.jl not do such caching? Or is there a way of enabling it somehow?

• Python 3.9.5
• Julia 1.6.1

[quinnj]

Yeah, this is just basics on how transactions work in transactional databases; it will always be orders of magnitude faster to do "bulk" transactions than lots of small transactions. Arguably, DBInterface.executemany should handle this automatically, but it looks like that's not working. But you can test better performance from SQLite.jl by using SQLite.load!, which does the bulk inserting in a single transaction before committing (similar to the python approach here) (https://github.com/JuliaDatabases/SQLite.jl/blob/master/src/tables.jl#L206).

[ForceBru]

Yeah, this is just basics on how transactions work in transactional databases; it will always be orders of magnitude faster to do "bulk" transactions than lots of small transactions. Arguably, DBInterface.executemany should handle this automatically, but it looks like that's not working. But you can test better performance from SQLite.jl by using SQLite.load!, which does the bulk inserting in a single transaction before committing (similar to the python approach here) (https://github.com/JuliaDatabases/SQLite.jl/blob/master/src/tables.jl#L206).

I would like not to resort to SQLite-specific functions, though. I would like my code to be as generic as possible, so that users of my library (well, mostly me) could use any database engine. AFAIK, DBInterface does exactly that - it lets me write code using only DBInterface, and it lets users work with any database engine that supports DBInterface interfaces. Right?

Is it not possible to continue using DBInterface without such slowdowns, then?

[ghostrider77]

I think the issue is that DBInterface expects that every database package implements (overloads) an execute function, but does not expect that a package implements an executemany function (and the SQLite package does not have an executemany function). From the source code it appears that DBInterface.executemany (line 168) checks if all the records have the same number of elements, then calls execute in a for-loop, so there is absolutely no performance gain here (unlike in Python). Last year I also faced a similar problem (played with Julia by rewriting a Python code that used executemany for bulk-inserting large lists of size ~50-100 MB many times into an SQLite database), I have also tried using the SQLite.load! function suggested above, but my observation was that its performance degrades a lot as the database is getting bigger.

The most efficient implementation I came up with back then is an SQLite-specific solution by using non-exported low-level functions (so this is not nice, it's like importing and using package-private functions in Python) of the same functionality which looks like this (for this specific data):

@info "Test with custom executemany"
stmt = DBInterface.prepare(conn, "INSERT INTO test VALUES (?)")
@time SQLite.transaction(conn) do
    for record in my_data
        SQLite.bind!(stmt, (record,))
        SQLite.sqlite3_step(stmt.handle)
        SQLite.sqlite3_reset(stmt.handle)
    end
end

So, to answer to your query, for me it seems that you cannot use DBInterface to achieve Python-like speed for bulk-inserting records into an SQLite database, I am afraid.

[quinnj]

Thanks for the additional info @ghostrider77; DBInterface.jl now has DBInterface.transaction that can be overloaded and is used in executemany now. Here's the SQLite PR that overloads it, so it should be much faster now: #268

[ghostrider77]

Thank you @quinnj , as I understand, as soon as you PR is accepted to the SQLite.jl library for the implementation of the DBInterface.connection overloaded method, executemany will work.