In SQLite, do prepared statements really improve performance?

Question

I have heard that prepared statements with SQLite should improve performance. I wrote some code to test that, and did not see any difference in performance with using them. So, I thought maybe my code was incorrect. Please let me know if you see any errors in how I'm doing 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 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.

Answer 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.