我听说准备好的
SQLite声明应该提高表现。我写了一些代码来测试,并且没有看到使用它们的性能有任何差异。所以,我以为也许我的代码是不正确的。如果您发现我在做什么错误,请通知我…
[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); 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,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,NULL); } sql = @"BEGIN"; sqlite3_exec(dbConn,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,-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,NULL); } sqlite3_bind_int(stmt,1); sqlite3_bind_int(stmt,2); sqlite3_bind_int(stmt,3); sqlite3_bind_int(stmt,4); sqlite3_bind_int(stmt,5); sqlite3_bind_int(stmt,6); sqlite3_bind_int(stmt,7); sqlite3_bind_int(stmt,8); sqlite3_bind_int(stmt,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,NULL); NSDate *after = [NSDate date]; NSLog([after description]); }
准备的声明通过在
query optimizer发现最佳方案之后缓存
execution plan for a query来提高性能。
如果您使用的查询没有复杂的计划(例如简单的选择/插入,没有加入),则准备好的语句将不会给您一个很大的改进,因为优化器将快速找到最佳方案。