i am curious as to the discrepancies in 'sys' time between sQLite and mySQL (9.12s .vs 1.96s as reported below).
SQLite lacks a persistent server, so it has to flush its cache and reread every page of the database for each of the 100 queries. This takes time. MySQL, on the other hand gets to keep all those pages in memory.
If I change the query slightly so that it begins like this:
PRAGMA cache_size=20000; BEGIN;
That allows SQLite to hold all database pages in cache after the initial read. In that case, SQLite is much faster:
[EMAIL PROTECTED] bld]$ time ./sqlite test.db <speed1trans.sql >/dev/null real 0m13.403s user 0m13.190s sys 0m0.080s
also, the 'usr + sys' time for SQLite more or less equals the real time. but for mySQL, the 'real' time is over 5s longer than the respective 'usr + sys' times (25% greater).
With SQLite, all processing happens in a single process so you see it all with the "time" command. With MySQL, you are only seeing the time used by the client-side. The server-side processing is omitted from the "user" and "sys" times. But it is included in the real time, of course.
I took your data and loaded it into SQLite and MySQL databases. Then I create a script file that contains 100 instances of your query. Here is what I get:
[EMAIL PROTECTED] bld]# time mysql drh <speed1.sql >/dev/null
real 0m25.585s user 0m18.290s sys 0m1.960s [EMAIL PROTECTED] bld]# time ./sqlite test.db <speed1.sql >/dev/null
real 0m22.993s user 0m13.870s sys 0m9.120s
So in my test, SQLite is a little faster. Perhaps the difference might be in a bad implementation of the SQLite bindings for Perl, or perhaps the "mysql" command-line shell is less than optimal.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
