> 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