Re: [sqlite] Re: SQLite :memory: performance difference between v2 and v3?
Dennis Cote wrote: Erik Jensen wrote: very interesting. Do you have the chance to run the same test with version 3.2.1? Like i wrote in an earlier message, i noticed a similar performance drop when i upgraded my application from sqlite 3.2.1 to 3.3.5. Eric, I reran these test using 3.2.1 dll downloaded from the sqlite website. It is very similar to the results from 3.3.5. It is slightly slower for all except the memory insert without transaction case. So it doesn't look like the big change happened after 3.2.1. On a whim, I tested 3.0.8 and 3.3.4: SQLite DB TX records inserts/sec === 3.3.5 :memory:no 1M 2778 3.3.5 :memory:yes 1M 22727 3.3.5 fileno 1K 10 3.3.5 fileyes 1M 24390 3.2.1 :memory:no 1M 2857 3.2.1 :memory:yes 1M 21739 3.2.1 fileno 1K 9 3.2.1 fileyes 1M 22727 2.8.17 :memory:no 1M 62500 2.8.17 :memory:yes 1M 58824 2.8.17 fileno 1K 13 2.8.17 fileyes 1M 23256 3.3.4 :memory:yes 1M 25866 3.3.4 fileyes 1M 28587 3.0.8 :memory:yes 1M 20573 3.0.8 fileyes 1M 22903 It seems there were some significant performance improvements in between the two versions! Arun
Re: [sqlite] Re: SQLite :memory: performance difference between v2 and v3?
--- Dennis Cote <[EMAIL PROTECTED]> wrote: > 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. There is a reason my program is like that: in production, queries will have no correlation and therefore cannot use statement preperation or transactions (I need the "autocommit" mode). > * transactions speed up memory inserts on version 3 > by a factor of 10, > but slow down memory insert on version 2 by about > 6%. I think this behaviour is conter-intuitive for a memory database - there's no need for locking and IMHO (not an expert :) ) these should run almost the same as without transactions. For now, I'll use sqlite2 for my application. Thanks to everyone for interest and feedback! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Re: SQLite :memory: performance difference between v2 and v3?
Erik Jensen wrote: very interesting. Do you have the chance to run the same test with version 3.2.1? Like i wrote in an earlier message, i noticed a similar performance drop when i upgraded my application from sqlite 3.2.1 to 3.3.5. Eric, I reran these test using 3.2.1 dll downloaded from the sqlite website. It is very similar to the results from 3.3.5. It is slightly slower for all except the memory insert without transaction case. So it doesn't look like the big change happened after 3.2.1. SQLite DB TX records inserts/sec === 3.3.5 :memory:no 1M 2778 3.3.5 :memory:yes 1M 22727 3.3.5 fileno 1K 10 3.3.5 fileyes 1M 24390 3.2.1 :memory:no 1M 2857 3.2.1 :memory:yes 1M 21739 3.2.1 fileno 1K 9 3.2.1 fileyes 1M 22727 2.8.17 :memory:no 1M 62500 2.8.17 :memory:yes 1M 58824 2.8.17 fileno 1K 13 2.8.17 fileyes 1M 23256 Dennis Cote
Re[2]: [sqlite] Re: SQLite :memory: performance difference between v2 and v3?
Hello Dennis, DC> My results are summarized below. DC> SQLite DB TX records inserts/sec DC> === DC> 3.3.5 :memory:no 1M 2778 DC> 3.3.5 :memory:yes 1M 22727 DC> 3.3.5 fileno 1K 10 DC> 3.3.5 fileyes 1M 24390 DC> 2.8.17 :memory:no 1M 62500 DC> 2.8.17 :memory:yes 1M 58824 DC> 2.8.17 fileno 1K 13 DC> 2.8.17 fileyes 1M 23256 very interesting. Do you have the chance to run the same test with version 3.2.1? Like i wrote in an earlier message, i noticed a similar performance drop when i upgraded my application from sqlite 3.2.1 to 3.3.5. If the performance of 3.2.1 is still ok, then the slowness must have happened anywhere between 3.2.1 and 3.3.5. Regards, Eric
Re: [sqlite] Re: SQLite :memory: performance difference between v2 and v3?
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
#include
#include
#include
int main(int argc, char** argv) {
int i, count = 10, t, time1, time2;
sqlite3 *db;
char *msg;
char sql[200];
if (sqlite3_open(":memory:", ) != 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, ) !=
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 fileno 1K 10
3.3.5 fileyes 1M 24390
2.8.17 :memory:no 1M 62500
2.8.17 :memory:yes 1M 58824
2.8.17 fileno 1K 13
2.8.17 fileyes 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
#include
#include
[sqlite] Re: SQLite :memory: performance difference between v2 and v3?
> 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
#include
#include
#include
int main(int argc, char** argv) {
int i, count = 10, t, time1, time2;
sqlite3 *db;
char *msg;
char sql[200];
if (sqlite3_open(":memory:", ) != 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, ) !=
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
