В SQLite действительно ли подготовленные заявления действительно улучшают производительность?

Я слышал, что подготовленные заявления с 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,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]);
}

Ответ 1

Подготовленные заявления улучшают производительность, кэшируя план выполнения запроса после оптимизатор запросов нашел лучший план.

Если запрос, который вы используете, не имеет сложного плана (например, простые selects/inserts без соединений), то подготовленные инструкции не дадут вам большого улучшения, так как оптимизатор быстро найдет лучший план.

Однако, если вы запустили один и тот же тест с запросом, имеющим несколько подключений, и использовали некоторые индексы, вы увидите разницу в производительности, так как оптимизатор не будет запускаться каждый раз, когда будет выполнен запрос.

Ответ 2

Да - это имеет огромное значение: используете ли вы sqlite3_exec() vs. sqlite3_prepare_v2() / sqlite3_bind_xxx() / sqlite3_step() для объемных вставок.

sqlite3_exec() - только метод удобства. Внутри он просто вызывает ту же последовательность sqlite3_prepare_v2() and sqlite3_step(). Ваш примерный код вызывает sqlite3_exec() over-and-over в литеральной строке:

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);
}

Я не знаю внутренней работы синтаксического анализа SQLite, но, возможно, анализатор достаточно умен, чтобы распознать, что вы используете одну и ту же литеральную строку, а затем пропускаете повторную парсинг/повторную компиляцию с каждой итерацией.

Если вы попробуете тот же эксперимент со значениями, которые меняются - вы увидите гораздо большую разницу в производительности.

Ответ 3

Использование метода подготовки + вместо выполнения огромных улучшений производительности возможно. В некоторых случаях прирост производительности составляет более 100% во время выполнения.