SQLiteTest.c

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <assert.h>
#include <sys/time.h>
#include "sqlite3.h"

/**
 * A bunch of tests to try to understand why selecting 999 keys in a single statement is faster than selecting 1 key 999 times
 * 
 * Using https://www.sqlite.org/2020/sqlite-amalgamation-3340000.zip
 * Run on a Pixel 3 XL
 *
 * (chunkSize=1) Took: 399264 us
 * (chunkSize=999) Took: 10103 us
 * (chunkSize=1) Took: 599973 us
 * (chunkSize=999) Took: 11376 us
 */
void exec(sqlite3 *db, const char *sql) {
    char *err_msg = 0;
    int rc = sqlite3_exec(db, sql, 0, 0, &err_msg);  
    if (rc != SQLITE_OK ) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
                
        exit(1);
    } 
}

sqlite3 *create_db(char *db_name) {
    unlink(db_name);
    sqlite3 *db;

    int rc = sqlite3_open(db_name, &db);
    
    if (rc != SQLITE_OK) {
        
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        
        exit(1);
    }

    printf("creating db...\n");
    exec(db, "CREATE TABLE records (_id INTEGER PRIMARY KEY AUTOINCREMENT, key TEXT NOT NULL, record TEXT NOT NULL);");
    exec(db, "CREATE INDEX idx_records_key ON records(key);");
    
    exec(db, "BEGIN TRANSACTION;");
    for (int i = 0; i < 100000; i++) {
        char buf[4096];
        sprintf(buf, "INSERT INTO records (key, record) VALUES ('%d', 'lorem %d ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum');", i, i);
        exec(db, buf);
    }
    exec(db, "COMMIT;");
    
    return db;
}

int readChunked(int chunkSize, sqlite3 *db){
    sqlite3_stmt *res;
    int rc;

    char buf[8192];
    sprintf(buf, "SELECT key, record FROM records WHERE key IN(");
    for (int i = 0; i < chunkSize - 1; i++) {
        strcat(buf, "?,");
    }
    strcat(buf, "?);");

    struct timespec start, end;
    clock_gettime(CLOCK_MONOTONIC_RAW, &start);

    rc = sqlite3_prepare_v2(db, buf, -1, &res, 0);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
        exit(1);
    }

    int loops = 999/chunkSize;
    for (int loop = 0; loop < loops; loop++) {
        for (int i = 0; i < chunkSize; i++) {
            char *num = malloc(64);

            sprintf(num, "%d", loop + i);
            sqlite3_bind_text(res, i + 1, num, -1, free);
        }

        
        while(1) {
            int step = sqlite3_step(res);
            if (step == SQLITE_ROW) {    
                if (1) {
                    sqlite3_column_text(res, 0);
                    sqlite3_column_text(res, 1);
                } else {
                    printf("%s: ", sqlite3_column_text(res, 0));
                    printf("%s\n", sqlite3_column_text(res, 1));
                }
                
            } else if (step == SQLITE_DONE) {
                sqlite3_reset(res);
                break;
            }
        }
    }


    clock_gettime(CLOCK_MONOTONIC_RAW, &end);

    uint64_t delta_us = (end.tv_sec - start.tv_sec) * 1000000 + (end.tv_nsec - start.tv_nsec) / 1000;

    printf("(chunkSize=%d) Took: %llu us\n", chunkSize, delta_us);

    sqlite3_finalize(res);
    
    return 0;
}

int  main(int argc, char **argv){
    char *db_name;
    if (argc > 1) {
        db_name = argv[1];
    } else {
        db_name = ":memory:";
    }
    
    sqlite3 *db = create_db(db_name);
    readChunked(1, db);
    readChunked(999, db);
    readChunked(1, db);
    readChunked(999, db);

    sqlite3_close(db);
}