Skip to content

Instantly share code, notes, and snippets.

@martinbonnin
Last active January 19, 2021 00:20
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save martinbonnin/8eb6be75d7c4f8a70c41ca399dc560ce to your computer and use it in GitHub Desktop.
Save martinbonnin/8eb6be75d7c4f8a70c41ca399dc560ce to your computer and use it in GitHub Desktop.
#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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment