> Testing is difficult to do correctly. As several
people
> noted on this list just today the first time they
ran
> a query it had much different
> performance than subsequent
> runs of the query. Did you run these tests more
> than one time? What's your environment?
This is not a "first time" problem - it's a "first
100,000 times" problem :)
I tried the same thing many times, even under
different languages (C, PHP) and two different
machines and always get the same behaviour (sqlite2
much faster than sqlite3). This is on FreeBSD 6.
Since the attachments got stripped, here's pasted code
for the test program:
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <sqlite3.h>
int main(int argc, char** argv) {
int i, count = 100000, t, time1, time2;
sqlite3 *db;
char *msg;
char sql[200];
if (sqlite3_open(":memory:", &db) != 0) {
fprintf(stderr, "Error: %s\n",
sqlite3_errmsg(db));
exit(1);
}
sqlite3_exec(db, "CREATE TABLE cache (key varchar
not null, value varchar not null, time integer not
null, primary key(key))", NULL, NULL, NULL);
sqlite3_exec(db, "CREATE INDEX cache_time ON
cache(time)", NULL, NULL, NULL);
t = time1 = time(NULL);
for (i = 0; i < count; i++) {
sprintf(sql, "INSERT INTO cache(key, value,
time) VALUES ('key%d', 'value %d', %d)", i, i, t);
if (sqlite3_exec(db, sql, NULL, NULL, &msg) !=
SQLITE_OK) {
fprintf(stderr, "sqlite error %s\n", msg);
exit(1);
}
}
time2 = time(NULL);
printf("%0.1f qps\n", (float)count /
(time2-time1));
return 0;
}
The test program for sqlite2 is the same, only
sqlite3_* is replaced with sqlite_* (and different
libraries linked, of course). Granularity of time() is
coarse, but it doesn't matter here - performance
difference is in order of magnitude.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
