CLSQL is slow

CLSQL is a pretty slow though sometimes convenient library. It does not support parameterized queries but rather sends interpolated text queries to the database. Due to this, it allocates a lot (for the query text itself as well as for intermediary strings) and causes the database to parse the query every time. That's why I decided to write my own binding to SQLite that would support parameterized queries.

In comparison with CLSQL, it works much faster. I've compared them on the following query to the in-memory SQLite database:

create table users (id integer primary key, n integer);
insert into users (n) values (?);

The query was executed 100000 times with the value of a single parameter being a number from 1 to 100000.

• CLSQL, using Symbol SQL Syntax: 13.114 seconds
• CLSQL, using textual SQL query (using string formatting): 6.109 seconds
• SQLITE, using manual textual SQL query formatting: 5.246 seconds
• SQLITE, using parameterized query (recreated on every iteration): 3.901 seconds
• SQLITE, using a single parameterized query (created once, before iterations): 1.255 seconds

It should be noted that the underlying database is the same - it's the same SQLite. But still, different libraries show vastly different performance figures.