In SQLite, do prepared statements really improve performance?

Solution 1:

Prepared statements improve performance by caching the execution plan for a query after the query optimizer has found the best plan.

If the query you're using doesn't have a complicated plan (such as simple selects/inserts with no joins), then prepared statements won't give you a big improvement since the optimizer will quickly find the best plan.

However, if you ran the same test with a query that had a few joins and used some indexes, you would see the performance difference since the optimizer wouldn't be run every time the query is.

Solution 2:

Yes - it makes a huge difference whether your using sqlite3_exec() vs. sqlite3_prepare_v2() / sqlite3_bind_xxx() / sqlite3_step() for bulk inserts.

sqlite3_exec() is only a convenience method. Internally it just calls the same sequence of sqlite3_prepare_v2() and sqlite3_step(). Your example code is calling sqlite3_exec() over-and-over on a literal string:

for(int i=0;i<recs;i++){
    sql = @"INSERT INTO test (id,field1,field2,field3,field4,field5,field6,field7,field8,field9,field10) VALUES (%d,1,2,3,4,5,6,7,8,9,10)";
    sqlite3_exec(dbConn, [sql UTF8String],NULL,NULL,NULL);
}

I don't know the inner workings of the SQLite parser, but perhaps the parser is smart enough to recognize that you are using the same literal string and then skips re-parsing/re-compiling with every iteration.

If you try the same experiment with values that change - you'll see a much bigger difference in performance.