问题描述
我听说使用 SQLite 准备好的语句应该可以提高性能.我写了一些代码来测试它,并没有发现使用它们的性能有任何差异.所以,我想也许我的代码不正确.如果您发现我的操作有任何错误,请告诉我...
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]);
}
推荐答案
准备好的语句通过缓存 执行来提高性能在查询优化器找到最佳计划后,为查询计划.
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.
这篇关于在 SQLite 中,准备好的语句真的能提高性能吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!