*** 2,8 ****
{link: http://www.sqlite.org/speed.html here}.
My modified version is attached to this page.
Please note that I'm not a TCL programmer and my mods are just poor hacks that
! were needed to make tests run on all databases. If you see any errors in there that would influence the
outcome of tests, please post your findings here.
All databases were installed with default settings. In case of SQLite, I used
--- 2,9 ----
{link: http://www.sqlite.org/speed.html here}.
My modified version is attached to this page.
Please note that I'm not a TCL programmer and my mods are just poor hacks that
! were needed to make tests run on all databases. If you see any errors in there
! that would influence the
outcome of tests, please post your findings here.
All databases were installed with default settings. In case of SQLite, I used
***************
*** 24,30 ****
I don't know how to flush disk buffers on windows. This script used sync
command available on Linux to do that. If you know how to do something like
! that on windows, please post here. You might check out NTSync from {link: http://www.checksum.org/
cso/downloads/ from here}.
If you want an explanation next to numbers, I'd recommend reading through
--- 25,32 ----
I don't know how to flush disk buffers on windows. This script used sync
command available on Linux to do that. If you know how to do something like
! that on windows, please post here. You might check out NTSync from {link:
! http://www.checksum.org/
cso/downloads/ from here}.
If you want an explanation next to numbers, I'd recommend reading through
***************
*** 41,50 ****
DROP INDEX t2_b;
Above query, when run on same db as query from test 6, completed in 0.5 seconds
! instead of 14+ seconds. Actually it was even a tiny bit faster then the time from test 7, but this could
be attributed to varying runtime conditions.
! I have no idea what's wrong with Postgres in test 8. If you do, and you think that SQL being fed to it
makes that particular test unfair to Postgres, please speak up.
*A Speak UP:* Postgres is designed to run on a shoebox for the default install.
--- 43,54 ----
DROP INDEX t2_b;
Above query, when run on same db as query from test 6, completed in 0.5 seconds
! instead of 14+ seconds. Actually it was even a tiny bit faster then the time
! from test 7, but this could
be attributed to varying runtime conditions.
! I have no idea what's wrong with Postgres in test 8. If you do, and you think
! that SQL being fed to it
makes that particular test unfair to Postgres, please speak up.
*A Speak UP:* Postgres is designed to run on a shoebox for the default install.
***************
*** 54,60 ****
stored in its stats database. That data is only updated by running the
"Analyze" command. Other databases may track those statistics differently. In
non transactional insert test, it is difficult to see how a 7200 RPM drive can
! do 1000 I/O operation in 0.7 secords. The sync should force each transaction to
disk, which is each INSERT operation.
*Reply*: conf file for Postgres has been tuned a bit since test have been run.
--- 58,65 ----
stored in its stats database. That data is only updated by running the
"Analyze" command. Other databases may track those statistics differently. In
non transactional insert test, it is difficult to see how a 7200 RPM drive can
! do 1000 I/O operation in 0.7 secords. The sync should force each transaction
! to
disk, which is each INSERT operation.
*Reply*: conf file for Postgres has been tuned a bit since test have been run.
***************
*** 63,85 ****
resources and function same in terms of ACID compliance for sync tests. Once
some input on all of the databases has been gathered, I'll rerun the
tests.{linebreak}
! Is it common for Postgres in real world scenario to run ANALYZE before each query? If so, I'll update the
test script to run ANALYZE before each test.{linebreak}
! Test 1 is very strange, I agree. Hypothetical perfect database would take more then 8 seconds to
complete that test if it used only one fsync per transaction.
! *Another speak UP :-) :* Analyze tables after bulk inserts or a few % or more rows being updated etc.
! The stats generated will allow the planner to choose the most effective query plan for SELECTs - a
! classic example is a bulk loaded table with a few million records; if never previously analyzed, the
! planner might think there are only 100 rows, and thus choose a sequential scan of the table over the
! index scan that might be far more appropriate given the actual table size. Also be aware that
! PostgreSQL shines under high levels of concurrent load. Consider expanding the tests so that you have
! tens of connections both reading and writing data in multiple transactions simultaneously. Expand that
to hundreds or thousands of connections on more hefty hardware.
*Reply*: As for ANALYZE, I'll see what I can do to add it to test script after
significant table changes. That should be OK I guess. I plan to include the
! time cost of ANALYZE as part of those tests for Postgress. I consider this to be
fair since no other db gets any special treatment like this and AFAICT ANALYZE
is very fast.{linebreak}
No doubt, Postgres would shine in high concurrency tests but you're unlikely to
--- 68,100 ----
resources and function same in terms of ACID compliance for sync tests. Once
some input on all of the databases has been gathered, I'll rerun the
tests.{linebreak}
! Is it common for Postgres in real world scenario to run ANALYZE before each
! query? If so, I'll update the
test script to run ANALYZE before each test.{linebreak}
! Test 1 is very strange, I agree. Hypothetical perfect database would take more
! then 8 seconds to
complete that test if it used only one fsync per transaction.
! *Another speak UP :-) :* Analyze tables after bulk inserts or a few % or more
! rows being updated etc.
! The stats generated will allow the planner to choose the most effective query
! plan for SELECTs - a
! classic example is a bulk loaded table with a few million records; if never
! previously analyzed, the
! planner might think there are only 100 rows, and thus choose a sequential scan
! of the table over the
! index scan that might be far more appropriate given the actual table size. Also
! be aware that
! PostgreSQL shines under high levels of concurrent load. Consider expanding the
! tests so that you have
! tens of connections both reading and writing data in multiple transactions
! simultaneously. Expand that
to hundreds or thousands of connections on more hefty hardware.
*Reply*: As for ANALYZE, I'll see what I can do to add it to test script after
significant table changes. That should be OK I guess. I plan to include the
! time cost of ANALYZE as part of those tests for Postgress. I consider this to
! be
fair since no other db gets any special treatment like this and AFAICT ANALYZE
is very fast.{linebreak}
No doubt, Postgres would shine in high concurrency tests but you're unlikely to
***************
*** 88,96 ****
Few notes about the results:
*: Time is mesured in seconds, it represents wallclock time.
! *: nosync in case of SQLite means that SQLite is running with PRAGMA synchronous=OFF;. In case of
MySQL it means that table type is MyISAM.
! *: sync in case of SQLite means that SQLite is running with PRAGMA synchronous=FULL;. In case of
MySQL it means that table type is InnoDB.
*Comment*: You can turn off syncing (ie: nosync) in PostgreSQL by setting
--- 103,113 ----
Few notes about the results:
*: Time is mesured in seconds, it represents wallclock time.
! *: nosync in case of SQLite means that SQLite is running with PRAGMA
! synchronous=OFF;. In case of
MySQL it means that table type is MyISAM.
! *: sync in case of SQLite means that SQLite is running with PRAGMA
! synchronous=FULL;. In case of
MySQL it means that table type is InnoDB.
*Comment*: You can turn off syncing (ie: nosync) in PostgreSQL by setting
***************
*** 101,127 ****
which are changing alot. PostgreSQL in general thinks about a table 'in
use' where rows are inserted, updated, and deleted during the lifetime of the
table while the general 'running size' and statistics are reasonably constant.
! What that's about is that you might not run analyze before *every* query because
! the overall statisitcs don't change that much, but you might run 'vacuum analyze' nightly to mark old
! rows as being replacable and update the statistics. For a test like this it does make sense to analyze
! after changes to the tables are made. If tables are reused (and rows are updated or deleted) then
! vacuum and/or vacuum full should be run. Lack of running analyze is probably the problem with Test
! 8, Postgres isn't using the index because it's going off the 'default' statistics, which indicates there's
! only 100 rows or some such and in a case like that a sequential scan is faster. An additional nicety
! might be to re-run the Postgres tests with 'explain analyze' for each query and provide that output as
well to verify the query plan being used.
*Reply*: nosync tests were run simply to give better insight to SQLite
developers and users. MySQL was thrown into the mix there because, AFAIK, its
very common setup for MySQL to be run like that. Other then that, I'm mostly
interested in sync performance since that's the most common mode of operation.
! Except maybe for MySQL, I'm not sure if this changed in last 3 years or so.{linebreak}
! *Comment*: I pulled down your postgresql.conf and noticed you turned row level statistics on. This will have a negative impact on all tests which have a lot of queries. Why did you do this?{linebreak}
! On another note I would suggest running prepared statement versions of some of the tests on databases that support them.
!
! *Reply*: I don't think I changed it. It probably installed like that by default.
! Only changes I made are the ones suggested on sqlite mailing list. Here they are
for a reference:
shared_buffers=10000
--- 118,158 ----
which are changing alot. PostgreSQL in general thinks about a table 'in
use' where rows are inserted, updated, and deleted during the lifetime of the
table while the general 'running size' and statistics are reasonably constant.
! What that's about is that you might not run analyze before *every* query
! because
! the overall statisitcs don't change that much, but you might run 'vacuum
! analyze' nightly to mark old
! rows as being replacable and update the statistics. For a test like this it
! does make sense to analyze
! after changes to the tables are made. If tables are reused (and rows are
! updated or deleted) then
! vacuum and/or vacuum full should be run. Lack of running analyze is probably
! the problem with Test
! 8, Postgres isn't using the index because it's going off the 'default'
! statistics, which indicates there's
! only 100 rows or some such and in a case like that a sequential scan is faster.
! An additional nicety
! might be to re-run the Postgres tests with 'explain analyze' for each query and
! provide that output as
well to verify the query plan being used.
*Reply*: nosync tests were run simply to give better insight to SQLite
developers and users. MySQL was thrown into the mix there because, AFAIK, its
very common setup for MySQL to be run like that. Other then that, I'm mostly
interested in sync performance since that's the most common mode of operation.
! Except maybe for MySQL, I'm not sure if this changed in last 3 years or so.
! {linebreak}
! *Comment*: I pulled down your postgresql.conf and noticed you turned row level
! statistics on. This will have a negative impact on all tests which have a lot
! of queries. Why did you do this?{linebreak}
! On another note I would suggest running prepared statement versions of some of
! the tests on databases that support them.
!
! *Reply*: I don't think I changed it. It probably installed like that by
! default.
! Only changes I made are the ones suggested on sqlite mailing list. Here they
! are
for a reference:
shared_buffers=10000
***************
*** 131,141 ****
autovacuum=on
autovacuum_vacuum_scale_factor=0.2
! *Comment on Reply*: The default installation of Postgres 8.x *definitely* (I just checked it) have
#stats_row_level = false
! in their default configs, so you should disable the row stats collector and rerun the tests.
On to the tests now.
--- 162,174 ----
autovacuum=on
autovacuum_vacuum_scale_factor=0.2
! *Comment on Reply*: The default installation of Postgres 8.x *definitely* (I
! just checked it) have
#stats_row_level = false
! in their default configs, so you should disable the row stats collector and
! rerun the tests.
On to the tests now.
***************
*** 146,186 ****
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');<br>
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
<i>... 995 lines omitted</i><br>
! INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');<br>
! INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');<br>
! INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');<br>
!
! </blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 3.823</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 1.668</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 4.245</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 1.743</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 4.922</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 2.647</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 0.329</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 0.320</td></tr>
</table>
<h2>Test 2: 25000 INSERTs in a transaction</h2>
<blockquote>
BEGIN;<br>
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
! INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
<i>... 24997 lines omitted</i><br>
! INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty
seven');<br>
! INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred
thirty');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.764</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.748</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 0.698</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.663</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 16.454</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 7.833</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 7.038</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 4.280</td></tr>
</table>
<h2>Test 3: 25000 INSERTs into an indexed table</h2>
<blockquote>
--- 179,241 ----
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');<br>
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
<i>... 995 lines omitted</i><br>
! INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty
! nine');<br>
! INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty
! two');<br>
! INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty
! two');<br>
!
! </blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 3.823</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 1.668</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 4.245</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 1.743</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 4.922</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 2.647</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 0.329</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 0.320</td></tr>
</table>
<h2>Test 2: 25000 INSERTs in a transaction</h2>
<blockquote>
BEGIN;<br>
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
! INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred
! sixty one');<br>
<i>... 24997 lines omitted</i><br>
! INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight
! hundred forty
seven');<br>
! INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three
! hundred
thirty');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.764</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.748</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 0.698</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.663</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 16.454</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 7.833</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 7.038</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 4.280</td></tr>
</table>
<h2>Test 3: 25000 INSERTs into an indexed table</h2>
<blockquote>
***************
*** 188,208 ****
CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));<br>
CREATE INDEX i3 ON t3(c);<br>
<i>... 24998 lines omitted</i><br>
! INSERT INTO t3 VALUES(24999,442549,'four hundred forty two thousand five hundred forty nine');<br>
! INSERT INTO t3 VALUES(25000,423958,'four hundred twenty three thousand nine hundred fifty
eight');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 1.778</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 1.832</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 1.526</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 1.364</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 19.236</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 11.524</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 12.427</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 6.351</td></tr>
! </table>
<h2>Test 4: 100 SELECTs without an index</h2>
<blockquote>
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;<br>
--- 243,280 ----
CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));<br>
CREATE INDEX i3 ON t3(c);<br>
<i>... 24998 lines omitted</i><br>
! INSERT INTO t3 VALUES(24999,442549,'four hundred forty two thousand five
! hundred forty nine');<br>
! INSERT INTO t3 VALUES(25000,423958,'four hundred twenty three thousand nine
! hundred fifty
eight');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 1.778</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 1.832</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 1.526</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 1.364</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 19.236</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 11.524</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 12.427</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 6.351</td></tr>
! </table>
! <p>
! <i>comment:</i> Unfortunately SQLite seems to have a scalability problem with
! indices, causing this speed advantage to be only present for small tables: On
! my system SQLite is about 2 times faster than MySQL on 25000 inserts in an
! indexed table. on the same test with 250000 inserts, SQLite is about 3 times
! slower than MySQL. With 500000 inserts, SQLite is about 6 times slower.
!
<h2>Test 4: 100 SELECTs without an index</h2>
<blockquote>
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;<br>
***************
*** 214,227 ****
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 3.153</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 3.088</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 3.993</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 3.983</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 5.740</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 2.718</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 1.641</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 2.976</td></tr>
</table>
<h2>Test 5: 100 SELECTs on a string comparison</h2>
<blockquote>
--- 286,307 ----
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 3.153</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 3.088</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 3.993</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 3.983</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 5.740</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 2.718</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 1.641</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 2.976</td></tr>
</table>
<h2>Test 5: 100 SELECTs on a string comparison</h2>
<blockquote>
***************
*** 234,273 ****
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 4.853</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 4.868</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 4.511</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 4.500</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 6.565</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 3.424</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 2.090</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 5.803</td></tr>
</table>
<h2>Test 6: INNER JOIN without an index</h2>
<blockquote>
SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 14.473</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 14.445</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 47.776</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 47.750</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 0.176</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 3.421</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 3.443</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 0.141</td></tr>
</table>
<h2>Test 7: Creating an index</h2>
<blockquote>
CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.552</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.526</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 0.650</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.605</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 0.276</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 1.159</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 0.275</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 0.264</td></tr>
</table>
<h2>Test 8: 5000 SELECTs with an index</h2>
<blockquote>
--- 314,377 ----
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 4.853</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 4.868</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 4.511</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 4.500</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 6.565</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 3.424</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 2.090</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 5.803</td></tr>
</table>
<h2>Test 6: INNER JOIN without an index</h2>
<blockquote>
SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 14.473</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 14.445</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 47.776</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 47.750</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 0.176</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 3.421</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 3.443</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 0.141</td></tr>
</table>
<h2>Test 7: Creating an index</h2>
<blockquote>
CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.552</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.526</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 0.650</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.605</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 0.276</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 1.159</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 0.275</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 0.264</td></tr>
</table>
<h2>Test 8: 5000 SELECTs with an index</h2>
<blockquote>
***************
*** 280,296 ****
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 1.872</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 1.853</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 2.444</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 2.478</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 199.823</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 3.763</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 3.725</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 5.187</td></tr>
</table>
* Performance of PostgreSQL in this test is most probably heavily impacted by
! <i>psql</i> command line utility. Same test when run form pgAdmin III GUI completed
in 5 seconds.
<h2>Test 9: 1000 UPDATEs without an index</h2>
--- 384,409 ----
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 1.872</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 1.853</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 2.444</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 2.478</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 199.823</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 3.763</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 3.725</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 5.187</td></tr>
</table>
* Performance of PostgreSQL in this test is most probably heavily impacted by
! <i>psql</i> command line utility. Same test when run form pgAdmin III GUI
! completed
in 5 seconds.
<h2>Test 9: 1000 UPDATEs without an index</h2>
***************
*** 304,317 ****
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.562</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.573</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 0.543</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.532</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 1.663</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 1.930</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 4.656</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 1.804</td></tr>
</table>
<h2>Test 10: 25000 UPDATEs with an index</h2>
<blockquote>
--- 417,438 ----
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.562</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.573</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 0.543</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.532</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 1.663</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 1.930</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 4.656</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 1.804</td></tr>
</table>
<h2>Test 10: 25000 UPDATEs with an index</h2>
<blockquote>
***************
*** 324,489 ****
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 1.883</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 1.894</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 1.994</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 1.973</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 23.933</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 16.348</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 17.383</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 15.542</td></tr>
</table>
<h2>Test 11: 25000 text UPDATEs with an index</h2>
<blockquote>
BEGIN;<br>
UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1;<br>
! UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
! UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE
a=25000;<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 1.386</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 1.365</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 1.168</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 1.121</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 24.672</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 16.469</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 15.491</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 21.583</td></tr>
</table>
<h2>Test 12: INSERTs from a SELECT</h2>
<blockquote>
! BEGIN;<br>INSERT INTO t1 SELECT * FROM t2;<br>INSERT INTO t2 SELECT * FROM t1;<br>COMMIT;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 1.179</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 1.116</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 1.864</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 1.526</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 1.091</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 0.986</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 0.933</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 4.782</td></tr>
</table>
<h2>Test 13: INNER JOIN with index on one side</h2>
<blockquote>
SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.371</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.369</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 0.273</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.275</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 5.981</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 0.408</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 0.603</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 1.099</td></tr>
</table>
<h2>Test 14: INNER JOIN on text field with index on one side</h2>
<blockquote>
SELECT t1.a FROM t1 INNER JOIN t3 ON t1.c=t3.c;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.383</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.376</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 0.309</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.291</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 1.324</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 0.404</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 0.558</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 0.454</td></tr>
</table>
<h2>Test 15: 100 SELECTs with subqueries. Subquery is using an index</h2>
<blockquote>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=0 AND t2.b<1000);<br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=100 AND t2.b<1100);<br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=200 AND t2.b<1200);<br>
<i>... 94 lines omitted</i><br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9700 AND t2.b<10700);<br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9800 AND t2.b<10800);<br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9900 AND t2.b<10900);<br>
!
! </blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 7.877</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 8.040</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 4.387</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 4.381</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 6.245</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 16.891</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 38.447</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 37.439</td></tr>
</table>
<h2>Test 16: DELETE without an index</h2>
<blockquote>
DELETE FROM t2 WHERE c LIKE '%fifty%';
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.528</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.429</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 1.228</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.984</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 0.336</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 0.394</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 0.532</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 0.404</td></tr>
</table>
<h2>Test 17: DELETE with an index</h2>
<blockquote>
DELETE FROM t2 WHERE a>10 AND a<20000;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.866</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.627</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 1.275</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.817</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 0.283</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 0.541</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 1.336</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 5.033</td></tr>
</table>
<h2>Test 18: A big INSERT after a big DELETE</h2>
<blockquote>
INSERT INTO t2 SELECT * FROM t1;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.973</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.865</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 1.680</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 1.336</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 0.727</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 0.762</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 1.088</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 4.171</td></tr>
</table>
<h2>Test 19: A big DELETE followed by many small INSERTs</h2>
<blockquote>
BEGIN;<br>
DELETE FROM t1;<br>
! INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');<br>
<i>... 2997 lines omitted</i><br>
! INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');<br>
! INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.155</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.133</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 0.160</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.255</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 2.635</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 1.402</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 1.133</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 0.667</td></tr>
</table>
<h2>Test 20: DROP TABLE</h2>
<blockquote>
DROP TABLE t1;<br>DROP TABLE t2;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td align="right"> 0.138</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td align="right"> 0.392</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td align="right"> 0.188</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td align="right"> 0.257</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td align="right"> 0.229</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td align="right"> 0.125</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td align="right"> 0.058</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td align="right"> 0.133</td></tr>
</table>
</html>
--- 445,710 ----
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 1.883</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 1.894</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 1.994</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 1.973</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 23.933</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 16.348</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 17.383</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 15.542</td></tr>
</table>
<h2>Test 11: 25000 text UPDATEs with an index</h2>
<blockquote>
BEGIN;<br>
UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1;<br>
! UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight'
! WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
! UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three'
! WHERE
a=25000;<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 1.386</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 1.365</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 1.168</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 1.121</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 24.672</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 16.469</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 15.491</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 21.583</td></tr>
</table>
<h2>Test 12: INSERTs from a SELECT</h2>
<blockquote>
! BEGIN;<br>INSERT INTO t1 SELECT * FROM t2;<br>INSERT INTO t2 SELECT * FROM
! t1;<br>COMMIT;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 1.179</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 1.116</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 1.864</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 1.526</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 1.091</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 0.986</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 0.933</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 4.782</td></tr>
</table>
<h2>Test 13: INNER JOIN with index on one side</h2>
<blockquote>
SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.371</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.369</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 0.273</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.275</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 5.981</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 0.408</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 0.603</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 1.099</td></tr>
</table>
<h2>Test 14: INNER JOIN on text field with index on one side</h2>
<blockquote>
SELECT t1.a FROM t1 INNER JOIN t3 ON t1.c=t3.c;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.383</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.376</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 0.309</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.291</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 1.324</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 0.404</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 0.558</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 0.454</td></tr>
</table>
<h2>Test 15: 100 SELECTs with subqueries. Subquery is using an index</h2>
<blockquote>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=0 AND
! t2.b<1000);<br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=100 AND
! t2.b<1100);<br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=200 AND
! t2.b<1200);<br>
<i>... 94 lines omitted</i><br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9700 AND
! t2.b<10700);<br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9800 AND
! t2.b<10800);<br>
! SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9900 AND
! t2.b<10900);<br>
!
! </blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 7.877</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 8.040</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 4.387</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 4.381</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 6.245</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 16.891</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 38.447</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 37.439</td></tr>
</table>
<h2>Test 16: DELETE without an index</h2>
<blockquote>
DELETE FROM t2 WHERE c LIKE '%fifty%';
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.528</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.429</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 1.228</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.984</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 0.336</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 0.394</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 0.532</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 0.404</td></tr>
</table>
<h2>Test 17: DELETE with an index</h2>
<blockquote>
DELETE FROM t2 WHERE a>10 AND a<20000;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.866</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.627</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 1.275</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.817</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 0.283</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 0.541</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 1.336</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 5.033</td></tr>
</table>
<h2>Test 18: A big INSERT after a big DELETE</h2>
<blockquote>
INSERT INTO t2 SELECT * FROM t1;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.973</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.865</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 1.680</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 1.336</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 0.727</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 0.762</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 1.088</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 4.171</td></tr>
</table>
<h2>Test 19: A big DELETE followed by many small INSERTs</h2>
<blockquote>
BEGIN;<br>
DELETE FROM t1;<br>
! INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy
! six');<br>
<i>... 2997 lines omitted</i><br>
! INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty
! five');<br>
! INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred
! seventeen');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.155</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.133</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 0.160</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.255</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 2.635</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 1.402</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 1.133</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 0.667</td></tr>
</table>
<h2>Test 20: DROP TABLE</h2>
<blockquote>
DROP TABLE t1;<br>DROP TABLE t2;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
! <tr><td>SQLite 3.3.3 (sync):</td><td
! align="right"> 0.138</td></tr>
! <tr><td>SQLite 3.3.3 (nosync):</td><td
! align="right"> 0.392</td></tr>
! <tr><td>SQLite 2.8.17 (sync):</td><td
! align="right"> 0.188</td></tr>
! <tr><td>SQLite 2.8.17 (nosync):</td><td
! align="right"> 0.257</td></tr>
! <tr><td>PostgreSQL 8.1.2:</td><td
! align="right"> 0.229</td></tr>
! <tr><td>MySQL 5.0.18 (sync):</td><td
! align="right"> 0.125</td></tr>
! <tr><td>MySQL 5.0.18 (nosync):</td><td
! align="right"> 0.058</td></tr>
! <tr><td>FirebirdSQL 1.5.2:</td><td
! align="right"> 0.133</td></tr>
</table>
</html>
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|