Ivan Voras wrote:
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.
Ivan,
I can confirm what you are seeing. The same thing happens under Win XP.
Originally I thought what you may be seeing is a difference in the speed
of the SQL parser between versions. In your test program you have SQLite
reparsing nearly identical insert statements for each iteration. If the
parser has slowed down for version 3 then this could explain your results.
I made a modified version of your test program. It uses the preferred
prepare/bind/step/finalize call family to execute the SQL. This way it
only parses the SQL once, and executes it many times, each time using
different parameter values. I created a similar program using the
equivalent sqlite version 2 calls. Both programs are included below.
I also added calls to start and end a transaction around the insert
loop. This makes a substantial difference in the execution speed in the
various cases I tested.
Because of the very large difference in the run time of the various
cases I had to adjust the total number of loops executed to get
reasonable run times (i.e. more than 10 seconds and less than 10
minutes) for the various cases. This means they were creating files or
memory images of different sizes.
I ran the programs using both :memory: and a real file. The database
file was deleted before each run. I also ran each case with and without
a transaction surrounding the insert loop (by commenting out the BEGIN
TRANSACTION and COMMIT TRANSACTION lines). I used the version 3.3.5
sqlite3.dll and version 2.8.17 sqlite.dll libraries downloaded from the
sqlite website. My test programs were compiled with GCC 3.4.2. I have a
generic 7200 RPM IDE drive.
My results are summarized below.
SQLite DB TX records inserts/sec
=======================================================
3.3.5 :memory: no 1M 2778
3.3.5 :memory: yes 1M 22727
3.3.5 file no 1K 10
3.3.5 file yes 1M 24390
2.8.17 :memory: no 1M 62500
2.8.17 :memory: yes 1M 58824
2.8.17 file no 1K 13
2.8.17 file yes 1M 23256
The interesting things I noticed are:
* transactions speed up memory inserts on version 3 by a factor of 10,
but slow down memory insert on version 2 by about 6%.
* memory inserts are a factor of 22 slower in version 3 than version 2
without transactions (this is what Ivan originally reported).
* memory inserts are a factor of 2.5 slower in version 3 than version 2
with transactions.
* transactions speed up file inserts by a factor of about 2400 for
version 3, and a factor of 1800 for version 2 (this is common knowledge).
* version 2 is faster than version 3 for file access without a
transaction, but version 3 is faster than version 2 with a transaction.
I'm surprised at the slowdowns given the code optimizations that have
gone into version 3. This code practically eliminates the parser, so the
difference must be in the VDBE execution time and/or the back end. I
have noticed that the VDBE code generated by sqlite version 3 contains
several GOTO opcodes that simply jump around from the beginning to the
end and back again. I didn't think they would have a significant impact
on the execution time, but for simple commands this overhead may add up.
Dennis Cote
#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;
sqlite3_stmt* s;
int rc;
char p1[20];
char p2[20];
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);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
sqlite3_prepare(db, "INSERT INTO cache(key, value, time) VALUES (?,
?, ?)", -1, &s, NULL);
t = time1 = time(NULL);
sqlite3_bind_int(s, 3, t);
for (i = 0; i < count; i++) {
sprintf(p1, "key%d", i);
sprintf(p2, "value %d", i);
sqlite3_bind_text(s, 1, p1, -1, SQLITE_STATIC);
sqlite3_bind_text(s, 2, p2, -1, SQLITE_STATIC);
rc = sqlite3_step(s);
if (rc != SQLITE_DONE) {
fprintf(stderr, "sqlite error %s\n", sqlite3_errmsg(db));
exit(1);
}
sqlite3_reset(s);
}
time2 = time(NULL);
sqlite3_finalize(s);
sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);
sqlite3_close(db);
printf("%0.0f ips\n", (float)count / (time2-time1));
return 0;
}
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <sqlite.h>
int main(int argc, char** argv) {
int i, count = 1000000, t, time1, time2;
sqlite *db;
sqlite_vm *s;
int rc;
char p1[20];
char p2[20];
char p3[20];
char *msg;
int columns;
const char **values;
const char **names;
db = sqlite_open(":memory:", 0, &msg);
if (db == NULL)
exit(1);
sqlite_exec(db, "CREATE TABLE cache (key varchar not null, value
varchar not null, time integer not null, primary key(key))", NULL, NULL,
NULL);
sqlite_exec(db, "CREATE INDEX cache_time ON cache(time)", NULL,
NULL, NULL);
sqlite_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
sqlite_compile(db, "INSERT INTO cache(key, value, time) VALUES (?,
?, ?)", NULL, &s, &msg);
t = time1 = time(NULL);
sprintf(p3, "%d", t);
sqlite_bind(s, 3, p1, strlen(p3) + 1, 0);
for (i = 0; i < count; i++) {
sprintf(p1, "key%d", i);
sprintf(p2, "value %d", i);
sqlite_bind(s, 1, p1, strlen(p1) + 1, 0);
sqlite_bind(s, 2, p2, strlen(p2) + 1, 0);
rc = sqlite_step(s, &columns, &values, &names);
if (rc != SQLITE_DONE)
exit(1);
sqlite_reset(s, &msg);
}
time2 = time(NULL);
sqlite_finalize(s, &msg);
sqlite_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);
sqlite_close(db);
printf("%0.0f ips\n", (float)count / (time2-time1));
return 0;
}
