Es gibt mehrere Ansätze Daten mit SQLite effektiv zu speichern und sie schnellstmöglich zu schreiben und abzufragen.
Chris Schirlinger hat einen recht umfangreichen Artikel zum Thema
Performancetuning unter Windows veröffentlicht. Er rät, SQLite so direkt
wie möglich zu verwenden und auf Wrapper zu verzichten. Da der Quellcode frei erhältlich ist,
ist es möglich, ihn in eine Anwendung, die Daten strukturiert speichern, soll zu integrieren und
mit dem Programm zusammen zu kompilieren.
Seinem Vorschlag, nach dem Anlegen einer DB ihre Seitengröße mit der Anweisung
PRAGMA page_size=4096;
auf 4 kB festzulegen, und sie somit für
NTFS-Dateisysteme zu optimieren, kann man umgehen, wenn man das ganze Programm incl.
SQLite mit dem Kompilerschalter
-DSQLITE_DEFAULT_PAGE_SIZE=4096
kompiliert.
Mir geht es in dieser Abhandlung um das schnellstmögliche Einfügen größerer Datenmengen in eine DB.
Im einfachsten Fall öffnen wir eine Datenbank und führen so viele INSERT-Statements aus, wie wir Daten haben.
So sieht's aus:
sqlite3 *DB;
char *zErrMsg=NULL;
int Zaehl;
char Buffer[64]={0};
char Abfrage[1024]={0};
int erg;
erg=sqlite3_open("Sample.db", &DB);
if(SQLITE_OK!=erg) {
printf("%s\n", sqlite3_errmsg(DB));
return sqlite3_errcode(DB);
}
sprintf(Abfrage, "CREATE TABLE tester01 ( \
ID INTEGER PRIMARY KEY, \
nummer int(11) not null default 0, \
strnummer varchar(64) not null default '' \
);"
);
erg=sqlite3_exec(DB, Abfrage, 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
/* TransaktionsBEGIN; */
erg=sqlite3_exec(DB, "BEGIN;", 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
if(SQLITE_OK!=erg) {
printf("%s\n", sqlite3_errmsg(DB));
} else {
for(Zaehl=0; Zaehl<10000; Zaehl++) {
sprintf(Buffer, "%d", Zaehl);
sprintf(Abfrage, "INSERT INTO tester01 (nummer, strnummer) \
VALUES(%d, '%s');", Zaehl, Buffer);
erg=sqlite3_exec(DB, Abfrage, 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
}
}
/* TransaktionsCOMMIT; */
erg=sqlite3_exec(DB, "COMMIT;", 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
Deutlich schneller geht das schonmal, wenn man
Transaktionen verwendet.
Hierzu führt man vor dem ersten INSERT den Befehl
BEGIN;
aus, und beendet die
Transaktion mit
COMMIT;
.
Mit
Prepared Statements sieht das
ganze dann so aus, und geht nochmal schneller.
sqlite3 *DB;
sqlite3_stmt *pStmt;
char *zErrMsg=NULL;
const char *zLeftover;
int Zaehl;
char Buffer[64]={0};
char Abfrage[1024]={0};
int erg;
erg=sqlite3_open("Sample.db", &DB);
if(SQLITE_OK!=erg) {
printf("%s\n", sqlite3_errmsg(DB));
return sqlite3_errcode(DB);
}
sprintf(Abfrage, "CREATE TABLE tester01 ( \
ID INTEGER PRIMARY KEY, \
nummer int(11) not null default 0, \
strnummer varchar(64) not null default '' \
);"
);
erg=sqlite3_exec(DB, Abfrage, 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
erg=sqlite3_exec(DB, "BEGIN;", 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
sprintf(Abfrage, "INSERT INTO tester01 (nummer, strnummer) VALUES(?, ?);");
erg=sqlite3_prepare(DB, Abfrage, strlen(Abfrage), &pStmt, &zLeftover);
if(SQLITE_OK!=erg) {
printf("%s\n", sqlite3_errmsg(DB));
} else {
for(Zaehl=0; Zaehl<10000; Zaehl++) {
sqlite3_bind_int(pStmt, 1, Zaehl);
sprintf(Buffer, "%d", Zaehl);
sqlite3_bind_text(pStmt, 2, Buffer, strlen(Buffer), SQLITE_STATIC);
erg=sqlite3_step(pStmt);
if(SQLITE_DONE!=erg) {
printf("%s\n", sqlite3_errmsg(DB));
}
sqlite3_reset(pStmt);
}
}
erg=sqlite3_exec(DB, "COMMIT;", 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
Am schnellsten ist es aber wohl mit Prepared Statements zu arbeiten, und die Parameter selber zu handeln.
Beispiel
sqlite3 *DB;
sqlite3_stmt *pStmt;
char *zErrMsg=NULL;
const char *zLeftover;
int Zaehl;
char Buffer[64]={0};
char Abfrage[1024]={0};
int erg;
erg=sqlite3_open("Sample.db", &DB);
if(SQLITE_OK!=erg) {
printf("%s\n", sqlite3_errmsg(DB));
return sqlite3_errcode(DB);
}
sprintf(Abfrage, "CREATE TABLE tester01 ( \
ID INTEGER PRIMARY KEY, \
nummer int(11) not null default 0, \
strnummer varchar(64) not null default '' \
);"
);
erg=sqlite3_exec(DB, Abfrage, 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
erg=sqlite3_exec(DB, "BEGIN;", 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
sprintf(Abfrage, "INSERT INTO tester01 (nummer, strnummer) VALUES(?, ?);");
erg=sqlite3_prepare(DB, Abfrage, strlen(Abfrage), &pStmt, &zLeftover);
// sqlite3_stmt ** ist in Wirklichkeit ein Zeiger auf eine Vdbe-Struktur
Vdbe *p = (Vdbe *)pStmt;
// aVar ist ein Array der vorbereiteten Parameter
p->aVar[0].flags=MEM_Int;
p->aVar[0].type=SQLITE_INTEGER;
p->aVar[1].z=Buffer;
p->aVar[1].flags=MEM_Str | MEM_Ephem | MEM_Term;
p->aVar[1].enc=SQLITE_UTF8;
p->aVar[1].type=SQLITE_TEXT;
if(SQLITE_OK!=erg) {
printf("%s\n", sqlite3_errmsg(DB));
} else {
for(Zaehl=0; Zaehl<10000; Zaehl++) {
p->aVar[0].i=Zaehl;
sprintf(Buffer, "%d", Zaehl);
p->aVar[1].n=strlen(Buffer);
erg=sqlite3_step(pStmt);
if(SQLITE_DONE!=erg) {
printf("%s\n", sqlite3_errmsg(DB));
}
sqlite3_reset(pStmt);
}
}
erg=sqlite3_exec(DB, "COMMIT;", 0, 0, &zErrMsg);
if(zErrMsg!=NULL) {
printf("%s\n", zErrMsg);
sqlite3_free(zErrMsg);
zErrMsg=NULL;
}
In der Datei SQLite3.h ist zwar eine Funktion
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
deklariert, diese ist aber nirgends definiert.
Obiges Beispiel basiert darauf, dass man die Parameter, die
sqlite3_prepare(...)
vorbereitet und
zur Verfügung stellt, direkt manipuliert. Die Angaben um welchen Datentyp es sich handelt etc.
nehmen wir hier für beliebig viele INSERTs genau einmal vor.
Für den einzufügenden String legen wir einmal einen Puffer fest, dessen Inhalt bei jedem
Schleifendurchlauf geändert wird. Die tatsächliche Länge des Strings muss in jedem Durchlauf
explizit angegeben werden. Für Strings nimmt die Variablenstruktur
Mem
(p->aVar ist ein Array von
Mem
s) einen Zeiger entgegen (Member
z
), Zahlenwerte speichert sie
direkt (Member
i
für Ganzzahlen und
r
für Fließkommazahlen).
/*
** Internally, the vdbe manipulates nearly all SQL values as Mem
** structures. Each Mem struct may cache multiple representations (string,
** integer etc.) of the same value. A value (and therefore Mem structure)
** has the following properties:
**
** Each value has a manifest type. The manifest type of the value stored
** in a Mem struct is returned by the MemType(Mem*) macro. The type is
** one of SQLITE_NULL, SQLITE_INTEGER, SQLITE_REAL SQLITE_FLOAT, SQLITE_TEXT or
** SQLITE_BLOB.
*/
struct Mem {
i64 i; /* Integer value */
int n; /* Number of characters in string value, including '\0' */
u16 flags; /* Some combination of MEM_Null, MEM_Str, MEM_Dyn, etc. */
u8 type; /* One of MEM_Null, MEM_Str
SQLITE_NULL, SQLITE_TEXT, etc. */
u8 enc; /* TEXT_Utf8, TEXT_Utf16le, or TEXT_Utf16be
SQLITE_UTF8, SQLITE_UTF16BE or SQLITE_UTF16LE */
double r; /* Real value */
char *z; /* String or BLOB value */
void (*xDel)(void *); /* If not null, call this function to delete Mem.z */
char zShort[NBFS]; /* Space for short strings */
};
typedef struct Mem Mem