In SQLite, do prepared statements really improve performance?

Question

I heard that prepared statements with SQLite should improve performance. I wrote code to test this and did not see any performance difference with their use. So, I thought my code was wrong. Please let me know if you see any errors in the way I do this ...

[self testPrep:NO dbConn:dbConn]; [self testPrep:YES dbConn:dbConn]; reuse=0 recs=2000 2009-11-09 10:39:18 -0800 processing... 2009-11-09 10:39:32 -0800 reuse=1 recs=2000 2009-11-09 10:39:32 -0800 processing... 2009-11-09 10:39:46 -0800 -(void)testPrep:(BOOL)reuse dbConn:(sqlite3*)dbConn{ int recs = 2000; NSString *sql; sqlite3_stmt *stmt; sql = @"DROP TABLE test"; sqlite3_exec(dbConn, [sql UTF8String],NULL,NULL,NULL); sql = @"CREATE TABLE test (id INT,field1 INT, field2 INT,field3 INT,field4 INT,field5 INT,field6 INT,field7 INT,field8 INT,field9 INT,field10 INT)"; sqlite3_exec(dbConn, [sql UTF8String],NULL,NULL,NULL); 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); } sql = @"BEGIN"; sqlite3_exec(dbConn, [sql UTF8String],NULL,NULL,NULL); if (reuse){ sql = @"select * from test where field1=?1 and field2=?2 and field3=?3 and field4=?4 and field5=?5 and field6=?6 and field6=?6 and field8=?8 and field9=?9 and field10=?10 and id=?11"; sqlite3_prepare_v2(dbConn, [sql UTF8String], -1, &stmt, NULL); } NSLog(@"reuse=%d",reuse); NSLog(@"recs=%d",recs); NSDate *before = [NSDate date]; NSLog([before description]); NSLog(@"processing..."); for(int i=0;i<recs;i++){ if (!reuse){ sql = @"select * from test where field1=?1 and field2=?2 and field3=?3 and field4=?4 and field5=?5 and field6=?6 and field6=?6 and field8=?8 and field9=?9 and field10=?10 and id=?11"; sqlite3_prepare_v2(dbConn, [sql UTF8String], -1, &stmt, NULL); } sqlite3_bind_int(stmt, 1, 1); sqlite3_bind_int(stmt, 2, 2); sqlite3_bind_int(stmt, 3, 3); sqlite3_bind_int(stmt, 4, 4); sqlite3_bind_int(stmt, 5, 5); sqlite3_bind_int(stmt, 6, 6); sqlite3_bind_int(stmt, 7, 7); sqlite3_bind_int(stmt, 8, 8); sqlite3_bind_int(stmt, 9, 9); sqlite3_bind_int(stmt, 10, 10); sqlite3_bind_int(stmt, 11, i); while(sqlite3_step(stmt) == SQLITE_ROW) { } sqlite3_reset(stmt); } sql = @"BEGIN"; sqlite3_exec(dbConn, [sql UTF8String],NULL,NULL,NULL); NSDate *after = [NSDate date]; NSLog([after description]); } 

Answer 1

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

If the query you use does not have a complex plan (for example, simple selects / inserts without connections), the prepared instructions will not give you much improvement, since the optimizer will quickly find the best plan.

However, if you run the same test with a query that has multiple connections and use some indexes, you will see a difference in performance, since the optimizer will not run every time the query is executed.

Answer 2

Yes - it matters a lot: do you use sqlite3_exec() vs. sqlite3_prepare_v2() / sqlite3_bind_xxx() / sqlite3_step() for bulk inserts.

sqlite3_exec() is just a convenience method. Inside, it just calls the same sequence sqlite3_prepare_v2() and sqlite3_step() . Your sample code calls sqlite3_exec() over-and-over in 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 SQLite parsing, but the parser is probably smart enough to recognize that you are using the same literal string and then skip re-parsing / recompiling with each iteration.

If you try the same experiment with values ​​that change, you will see a much larger difference in performance.

Answer 3

Using the preparation method + instead of making huge performance improvements is possible. In some cases, the performance gain is more than 100% at runtime.