Should prepared statements cache indexes of named parameters?

[gschlager]

I wanted to know how fast each type of parameter binding is, so I wrote a benchmark and compared it to the sqlite3 gem.

Here's the result:

Extralite array:           919.2 i/s
Extralite regular:         874.8 i/s - 1.05x  slower
Extralite index:           801.2 i/s - 1.15x  slower
Extralite named:           538.7 i/s - 1.71x  slower
SQLite3 regular:           328.8 i/s - 2.80x  slower
SQLite3 named:             267.7 i/s - 3.43x  slower

Extralite array

This is the fastest if you can bind an existing array. It's slower when you need to create the array especially for binding.

user = [1, "John", "john@example.com", "2023-12-29T11:10:04Z"]
stmt = db.prepare("INSERT INTO users VALUES (?, ?, ?, ?)")
stmt.execute(user)

Extralite regular

This is slightly slower than binding an array, but still very fast. If you can bind values directly in the order they are needed, use this.

stmt = db.prepare("INSERT INTO users VALUES (?, ?, ?, ?)")
stmt.execute(1, "John", "john@example.com", "2023-12-29T11:10:04Z")

Extralite index

Binding a hash with numbers as keys is 1.15x slower than binding arrays. I mostly implemented this because I wanted to know how much slower named parameters are.

user = { 1 => 1, 2 => "John", 3 => "john@example.com", 4 => "2023-12-29T11:10:04Z" }
stmt = db.prepare("INSERT INTO users VALUES (?, ?, ?, ?)")
stmt.execute(user)

Extralite named

Binding named parameters is the slowest. Mapping parameter names to the parameter position comes with overhead.

user = { id: 1, name: "John", email: "john@example.com", created_at: "2023-12-29T11:10:04Z" }
stmt = db.prepare("INSERT INTO users VALUES (:id, :name, :email, :created_at)")
stmt.execute(user)

All those methods are a lot faster than the sqlite3 gem. Still, I wonder if we should cache the mapping of named parameters to parameter position within prepared statements. In theory that should make it roughly as fast as "extralite index", but I'm not sure if it's worth the effort. After all, if performance is key, there's no way around positional binding.

[noteflakes]

Still, I wonder if we should cache the mapping of named parameters to parameter position within prepared statements.

Cache them how? If we start mapping C-strings to integers, that introduces a certain complexity that I'm not sure is worth the trouble. Also, I think the time spent on binding parameters might be not really siginificant when looking at total time of execution.

[noteflakes]

@gschlager I'm closing this issue for lack of follow through. If you want to act on it feel free to create a PR. Thanks!

[gschlager]

That's fine. Currently it's not a high priority for me as I have a workaround, but when I have some free time I'm going to look into it.