/ Check-in [ed47d162]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the speed.html documentation. Recent optimizations have made the library much faster. (CVS 846)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ed47d162a072a2f98b633cc14f2be1474288d90b
User & Date: drh 2003-01-25 14:25:42
Context
2003-01-25
14:32
Preparations for the release of version 2.7.6. (CVS 847) check-in: 5143d481 user: drh tags: trunk
14:25
Update the speed.html documentation. Recent optimizations have made the library much faster. (CVS 846) check-in: ed47d162 user: drh tags: trunk
2003-01-24
12:14
Add asserts to btree.c that check for the correct size of various typedefs and structures. Ticket #233. (CVS 845) check-in: c7e647d0 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to www/speed.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the speed.html file.
     3      3   #
     4         -set rcsid {$Id: speed.tcl,v 1.9 2003/01/18 22:01:07 drh Exp $ }
            4  +set rcsid {$Id: speed.tcl,v 1.10 2003/01/25 14:25:42 drh Exp $ }
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Database Speed Comparison: SQLite versus PostgreSQL</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
    27     27   <ul>
    28     28   <li><p>
    29     29     SQLite 2.7.6 is significantly faster (sometimes as much as 10 or
    30     30     20 times faster) than PostgreSQL 7.1.3
    31     31     for most common operations.  
    32     32   </p></li>
    33     33   <li><p>
    34         -  SQLite 2.7.6 is usually faster than MySQL 3.23.41 (sometimes
    35         -  more than twice as fast) though for some operations such as
    36         -  full table scans, it can be as much as 30% slower.
           34  +  SQLite 2.7.6 is often faster (sometimes
           35  +  more than twice as fast) than MySQL 3.23.41
           36  +  for most common operations.
    37     37   </p></li>
    38     38   <li><p>
    39     39     SQLite does not execute CREATE INDEX or DROP TABLE as fast as
    40     40     the other databases.  But this as not seen is a problem because
    41     41     those are infrequent operations.
    42     42   </p></li>
           43  +<li><p>
           44  +  SQLite works best if you group multiple operations together into
           45  +  a single transaction.
           46  +</p></li>
    43     47   </ul>
    44     48   
    45     49   <p>
    46     50   The results presented here come with the following caveats:
    47     51   </p>
    48     52   
    49     53   <ul>
    50     54   <li><p>
    51     55     These tests did not attempt to measure multi-user performance or
    52     56     optimization of complex queries involving multiple joins and subqueries.
    53     57   </p></li>
    54     58   <li><p>
    55         -  These tests are on a relatively small (approximately 10 megabyte) database.
           59  +  These tests are on a relatively small (approximately 14 megabyte) database.
    56     60     They do not measure how well the database engines scale to larger problems.
    57     61   </p></li>
    58     62   </ul>
    59     63   
    60     64   <h2>Test Environment</h2>
    61     65   
    62     66   <p>
................................................................................
   123    127   INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
   124    128   <i>... 995 lines omitted</i><br>
   125    129   INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');<br>
   126    130   INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');<br>
   127    131   INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');<br>
   128    132   
   129    133   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   130         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.658</td></tr>
   131         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.109</td></tr>
   132         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;7.177</td></tr>
   133         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.266</td></tr>
          134  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.373</td></tr>
          135  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.114</td></tr>
          136  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;13.061</td></tr>
          137  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.223</td></tr>
   134    138   </table>
   135    139   
   136         -<p>SQLite must close and reopen the database file, and thus invalidate
   137         -its cache, for each SQL statement.  In spite of this, the asynchronous
          140  +<p>
          141  +Because it does not have a central server to coordinate access,
          142  +SQLite must close and reopen the database file, and thus invalidate
          143  +its cache, for each transaction.  In this test, each SQL statement
          144  +is a separate transaction so the database file must be opened and closed
          145  +and the cache must be flushed 1000 times.  In spite of this, the asynchronous
   138    146   version of SQLite is still nearly as fast as MySQL.  Notice how much slower
   139         -the synchronous version is, however.  This is due to the necessity of
   140         -calling <b>fsync()</b> after each SQL statement.</p>
          147  +the synchronous version is, however.  SQLite calls <b>fsync()</b> after 
          148  +each synchronous transaction to make sure that all data is safely on
          149  +the disk surface before continuing.  For most of the 13 seconds in the
          150  +synchronous test, SQLite was sitting idle waiting on disk I/O to complete.</p>
   141    151   
   142    152   
   143    153   <h2>Test 2: 25000 INSERTs in a transaction</h2>
   144    154   <blockquote>
   145    155   BEGIN;<br>
   146    156   CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
   147         -INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
          157  +INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');<br>
   148    158   <i>... 24997 lines omitted</i><br>
   149         -INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');<br>
   150         -INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');<br>
          159  +INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');<br>
          160  +INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');<br>
   151    161   COMMIT;<br>
   152    162   
   153    163   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   154         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.058</td></tr>
   155         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.271</td></tr>
   156         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.912</td></tr>
   157         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.798</td></tr>
          164  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.900</td></tr>
          165  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.184</td></tr>
          166  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.914</td></tr>
          167  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.757</td></tr>
   158    168   </table>
   159    169   
   160    170   <p>
   161    171   When all the INSERTs are put in a transaction, SQLite no longer has to
   162         -close and reopen the database between each statement.  It also does not
          172  +close and reopen the database or invalidate its cache between each statement.
          173  +It also does not
   163    174   have to do any fsync()s until the very end.  When unshackled in
   164    175   this way, SQLite is much faster than either PostgreSQL and MySQL.
   165    176   </p>
   166    177   
   167         -<h2>Test 3: 100 SELECTs without an index</h2>
          178  +<h2>Test 3: 25000 INSERTs into an indexed tablel</h2>
          179  +<blockquote>
          180  +BEGIN;<br>
          181  +CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));<br>
          182  +CREATE INDEX i3 ON t3(c);<br>
          183  +<i>... 24998 lines omitted</i><br>
          184  +INSERT INTO t3 VALUES(24999,88509,'eighty eight thousand five hundred nine');<br>
          185  +INSERT INTO t3 VALUES(25000,84791,'eighty four thousand seven hundred ninety one');<br>
          186  +COMMIT;<br>
          187  +
          188  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          189  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.175</td></tr>
          190  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.197</td></tr>
          191  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;1.555</td></tr>
          192  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.402</td></tr>
          193  +</table>
          194  +
          195  +<p>
          196  +There were reports that SQLite did not perform as well on an indexed table.
          197  +This test was recently added to disprove those rumors.  It is true that
          198  +SQLite is not as fast at creating new index entries as the other engines
          199  +(see Test 6 below) but its overall speed is still better.
          200  +</p>
          201  +
          202  +<h2>Test 4: 100 SELECTs without an index</h2>
   168    203   <blockquote>
   169    204   BEGIN;<br>
   170    205   SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;<br>
   171    206   SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;<br>
   172    207   <i>... 96 lines omitted</i><br>
   173    208   SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
   174    209   SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>
   175    210   COMMIT;<br>
   176    211   
   177    212   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   178         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.657</td></tr>
   179         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.368</td></tr>
   180         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;4.386</td></tr>
   181         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;4.314</td></tr>
          213  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.629</td></tr>
          214  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.760</td></tr>
          215  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.494</td></tr>
          216  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;2.526</td></tr>
   182    217   </table>
          218  +
   183    219   
   184    220   <p>
   185    221   This test does 100 queries on a 25000 entry table without an index,
   186         -thus requiring a full table scan.  SQLite is about 20% or 30% slower
   187         -than PostgreSQL and MySQL.  The reason for this is believed to be
   188         -because SQLite stores all data as strings
   189         -and must therefore do 5 million string-to-number conversions in the
   190         -course of evaluating the WHERE clauses.  Both PostgreSQL and MySQL
   191         -store data as binary values where appropriate and can forego
   192         -this conversion effort.
          222  +thus requiring a full table scan.   Prior versions of SQLite used to
          223  +be slower than PostgreSQL and MySQL on this test, but recent performance
          224  +enhancements have increased its speed so that it is now the fastest
          225  +of the group.
   193    226   </p>
   194    227   
   195         -
   196         -<h2>Test 4: 100 SELECTs on a string comparison</h2>
          228  +<h2>Test 5: 100 SELECTs on a string comparison</h2>
   197    229   <blockquote>
   198    230   BEGIN;<br>
   199    231   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';<br>
   200    232   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';<br>
   201    233   <i>... 96 lines omitted</i><br>
   202    234   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';<br>
   203    235   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>
   204    236   COMMIT;<br>
   205    237   
   206    238   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   207         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;15.967</td></tr>
   208         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.088</td></tr>
   209         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;5.419</td></tr>
   210         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;5.367</td></tr>
          239  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;13.409</td></tr>
          240  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.640</td></tr>
          241  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.362</td></tr>
          242  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;3.372</td></tr>
   211    243   </table>
   212    244   
   213    245   <p>
   214    246   This test still does 100 full table scans but it uses
   215    247   uses string comparisons instead of numerical comparisions.
   216         -SQLite is almost three times faster than PostgreSQL here.  But it is
   217         -still 15% slower than MySQL.  MySQL appears to be very good
   218         -at doing full table scans.
          248  +SQLite is over three times faster than PostgreSQL here and about 30%
          249  +faster than MySQL.
   219    250   </p>
   220    251   
   221         -<h2>Test 5: Creating an index</h2>
          252  +<h2>Test 6: Creating an index</h2>
   222    253   <blockquote>
   223    254   CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
   224    255   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   225         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.431</td></tr>
   226         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.340</td></tr>
   227         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.814</td></tr>
   228         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.675</td></tr>
          256  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.381</td></tr>
          257  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.318</td></tr>
          258  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.777</td></tr>
          259  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.659</td></tr>
   229    260   </table>
   230    261   
   231    262   <p>
   232         -SQLite is slower at creating new indices.  But since creating
   233         -new indices is an uncommon operation, this is not seen as a
   234         -problem.
          263  +SQLite is slower at creating new indices.  This is not a huge problem
          264  +(since new indices are not created very often) but it is something that
          265  +is being worked on.  Hopefully, future versions of SQLite will do better
          266  +here.
   235    267   </p>
   236    268   
   237         -<h2>Test 6: 5000 SELECTs with an index</h2>
          269  +<h2>Test 7: 5000 SELECTs with an index</h2>
   238    270   <blockquote>
   239    271   SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;<br>
   240    272   SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;<br>
   241    273   SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
   242    274   <i>... 4994 lines omitted</i><br>
   243    275   SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
   244    276   SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>
   245    277   SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>
   246    278   
   247    279   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   248         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.369</td></tr>
   249         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.489</td></tr>
   250         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;1.423</td></tr>
   251         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.358</td></tr>
          280  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.614</td></tr>
          281  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.270</td></tr>
          282  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;1.121</td></tr>
          283  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.162</td></tr>
   252    284   </table>
   253    285   
   254    286   <p>
   255         -This test runs a set of 5000 queries that are similar in form to
   256         -those in test 3.  But now instead of being slower, SQLite
   257         -is faster than both PostgreSQL and MySQL.
          287  +All three database engines run faster when they have indices to work with.
          288  +But SQLite is still the fastest.
   258    289   </p>
   259    290   
   260         -<h2>Test 7: 1000 UPDATEs without an index</h2>
          291  +<h2>Test 8: 1000 UPDATEs without an index</h2>
   261    292   <blockquote>
   262    293   BEGIN;<br>
   263    294   UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;<br>
   264    295   UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
   265    296   <i>... 996 lines omitted</i><br>
   266    297   UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
   267    298   UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>
   268    299   COMMIT;<br>
   269    300   
   270    301   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   271         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.740</td></tr>
   272         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.162</td></tr>
   273         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.635</td></tr>
   274         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.608</td></tr>
          302  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.739</td></tr>
          303  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.410</td></tr>
          304  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.637</td></tr>
          305  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.638</td></tr>
   275    306   </table>
   276    307   
   277    308   <p>
   278    309   For this particular UPDATE test, MySQL is consistently
   279    310   five or ten times
   280    311   slower than PostgreSQL and SQLite.  I do not know why.  MySQL is
   281    312   normally a very fast engine.  Perhaps this problem has been addressed
   282    313   in later versions of MySQL.
   283    314   </p>
   284    315   
   285         -<h2>Test 8: 25000 UPDATEs with an index</h2>
          316  +<h2>Test 9: 25000 UPDATEs with an index</h2>
   286    317   <blockquote>
   287    318   BEGIN;<br>
   288         -UPDATE t2 SET b=271822 WHERE a=1;<br>
   289         -UPDATE t2 SET b=28304 WHERE a=2;<br>
          319  +UPDATE t2 SET b=468026 WHERE a=1;<br>
          320  +UPDATE t2 SET b=121928 WHERE a=2;<br>
   290    321   <i>... 24996 lines omitted</i><br>
   291         -UPDATE t2 SET b=442549 WHERE a=24999;<br>
   292         -UPDATE t2 SET b=423958 WHERE a=25000;<br>
          322  +UPDATE t2 SET b=35065 WHERE a=24999;<br>
          323  +UPDATE t2 SET b=347393 WHERE a=25000;<br>
   293    324   COMMIT;<br>
   294    325   
   295    326   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   296         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;32.118</td></tr>
   297         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.132</td></tr>
   298         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;4.109</td></tr>
   299         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;3.712</td></tr>
          327  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;18.797</td></tr>
          328  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.134</td></tr>
          329  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.520</td></tr>
          330  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;3.104</td></tr>
   300    331   </table>
   301    332   
   302    333   <p>
   303    334   As recently as version 2.7.0, SQLite ran at about the same speed as
   304         -MySQL on this test.  But recent optimizations to SQLite have doubled
   305         -speed of UPDATEs.
          335  +MySQL on this test.  But recent optimizations to SQLite have more
          336  +than doubled speed of UPDATEs.
   306    337   </p>
   307    338   
   308         -<h2>Test 9: 25000 text UPDATEs with an index</h2>
          339  +<h2>Test 10: 25000 text UPDATEs with an index</h2>
   309    340   <blockquote>
   310    341   BEGIN;<br>
   311         -UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1;<br>
   312         -UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
          342  +UPDATE t2 SET c='one hundred forty eight thousand three hundred eighty two' WHERE a=1;<br>
          343  +UPDATE t2 SET c='three hundred sixty six thousand five hundred two' WHERE a=2;<br>
   313    344   <i>... 24996 lines omitted</i><br>
   314         -UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
   315         -UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;<br>
          345  +UPDATE t2 SET c='three hundred eighty three thousand ninety nine' WHERE a=24999;<br>
          346  +UPDATE t2 SET c='two hundred fifty six thousand eight hundred thirty' WHERE a=25000;<br>
   316    347   COMMIT;<br>
   317    348   
   318    349   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   319         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;55.309</td></tr>
   320         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;6.585</td></tr>
   321         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.474</td></tr>
   322         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.800</td></tr>
          350  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;48.133</td></tr>
          351  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;6.982</td></tr>
          352  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.408</td></tr>
          353  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.725</td></tr>
   323    354   </table>
   324    355   
   325    356   <p>
   326    357   Here again, version 2.7.0 of SQLite used to run at about the same speed
   327    358   as MySQL.  But now version 2.7.6 is over two times faster than MySQL and
   328    359   over twenty times faster than PostgreSQL.
   329    360   </p>
   330    361   
   331         -<h2>Test 10: INSERTs from a SELECT</h2>
          362  +<p>
          363  +In fairness to PostgreSQL, it started thrashing on this test.  A
          364  +knowledgeable administrator might be able to get PostgreSQL to run a lot
          365  +faster here by tweaking and tuning the server a little.
          366  +</p>
          367  +
          368  +<h2>Test 11: INSERTs from a SELECT</h2>
   332    369   <blockquote>
   333    370   BEGIN;<br>INSERT INTO t1 SELECT b,a,c FROM t2;<br>INSERT INTO t2 SELECT b,a,c FROM t1;<br>COMMIT;
   334    371   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   335         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;58.956</td></tr>
   336         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.465</td></tr>
   337         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.926</td></tr>
   338         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.664</td></tr>
          372  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;61.364</td></tr>
          373  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.537</td></tr>
          374  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.787</td></tr>
          375  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.599</td></tr>
   339    376   </table>
   340    377   
   341    378   <p>
   342         -The poor performance of PostgreSQL in this case appears to be due to its
   343         -synchronous behavior.  The CPU was mostly idle the test run.  Presumably,
   344         -PostgreSQL was spending most of its time waiting on disk I/O to complete.
   345         -I'm not sure why SQLite performs poorly here.  It use to be quicker at this
   346         -test, but the same enhancements that sped up the UPDATE logic seem to have
   347         -slowed down this test.
          379  +The asynchronous SQLite is just a shade slower than MySQL on this test.
          380  +(MySQL seems to be especially adept at INSERT...SELECT statements.)
          381  +The PostgreSQL engine is still thrashing - most of the 61 seconds it used
          382  +were spent waiting on disk I/O.
   348    383   </p>
   349    384   
   350         -<h2>Test 11: DELETE without an index</h2>
          385  +<h2>Test 12: DELETE without an index</h2>
   351    386   <blockquote>
   352    387   DELETE FROM t2 WHERE c LIKE '%fifty%';
   353    388   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   354         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.365</td></tr>
   355         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.849</td></tr>
   356         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;4.005</td></tr>
   357         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.631</td></tr>
          389  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.509</td></tr>
          390  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.975</td></tr>
          391  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;4.004</td></tr>
          392  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.560</td></tr>
   358    393   </table>
   359    394   
   360    395   <p>
   361    396   The synchronous version of SQLite is the slowest of the group in this test,
   362         -but the asynchronous version is the fastest.  SQLite used about the same
   363         -amount of CPU time in both versions; the difference is the extra time needed
   364         -to write information to the disk surface.
          397  +but the asynchronous version is the fastest.  
          398  +The difference is the extra time needed to execute fsync().
   365    399   </p>
   366    400   
   367         -<h2>Test 12: DELETE with an index</h2>
          401  +<h2>Test 13: DELETE with an index</h2>
   368    402   <blockquote>
   369    403   DELETE FROM t2 WHERE a>10 AND a<20000;
   370    404   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   371         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.340</td></tr>
   372         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.167</td></tr>
   373         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.344</td></tr>
   374         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.858</td></tr>
          405  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.316</td></tr>
          406  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.262</td></tr>
          407  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.068</td></tr>
          408  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.752</td></tr>
   375    409   </table>
   376    410   
   377    411   <p>
   378    412   This test is significant because it is one of the few where
   379    413   PostgreSQL is faster than MySQL.  The asynchronous SQLite is,
   380    414   however, faster then both the other two.
   381    415   </p>
   382    416   
   383         -</table>
   384         -<h2>Test 13: A big INSERT after a big DELETE</h2>
          417  +<h2>Test 14: A big INSERT after a big DELETE</h2>
   385    418   <blockquote>
   386    419   INSERT INTO t2 SELECT * FROM t1;
   387    420   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   388         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;12.672</td></tr>
   389         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.837</td></tr>
   390         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.076</td></tr>
   391         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.570</td></tr>
          421  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;13.168</td></tr>
          422  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.815</td></tr>
          423  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.210</td></tr>
          424  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.485</td></tr>
   392    425   </table>
   393    426   
   394    427   <p>
   395         -Some older versions of SQLite would show decreasing performance after a
   396         -sequence DELETEs followed by new INSERTs.  As this test shows, the
          428  +Some older versions of SQLite (prior to version 2.4.0)
          429  +would show decreasing performance after a
          430  +sequence of DELETEs followed by new INSERTs.  As this test shows, the
   397    431   problem has now been resolved.
   398    432   </p>
   399    433   
   400         -<h2>Test 14: A big DELETE followed by many small INSERTs</h2>
          434  +<h2>Test 15: A big DELETE followed by many small INSERTs</h2>
   401    435   <blockquote>
   402    436   BEGIN;<br>
   403    437   DELETE FROM t1;<br>
   404         -INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');<br>
          438  +INSERT INTO t1 VALUES(1,10719,'ten thousand seven hundred nineteen');<br>
   405    439   <i>... 11997 lines omitted</i><br>
   406         -INSERT INTO t1 VALUES(11999,71818,'seventy one thousand eight hundred eighteen');<br>
   407         -INSERT INTO t1 VALUES(12000,58579,'fifty eight thousand five hundred seventy nine');<br>
          440  +INSERT INTO t1 VALUES(11999,72836,'seventy two thousand eight hundred thirty six');<br>
          441  +INSERT INTO t1 VALUES(12000,64231,'sixty four thousand two hundred thirty one');<br>
   408    442   COMMIT;<br>
   409    443   
   410    444   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   411         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.165</td></tr>
   412         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.733</td></tr>
   413         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.652</td></tr>
   414         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.465</td></tr>
          445  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.556</td></tr>
          446  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.704</td></tr>
          447  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.618</td></tr>
          448  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.406</td></tr>
   415    449   </table>
   416    450   
   417    451   <p>
   418    452   SQLite is very good at doing INSERTs within a transaction, which probably
   419    453   explains why it is so much faster than the other databases at this test.
   420    454   </p>
   421    455   
   422         -<h2>Test 15: DROP TABLE</h2>
          456  +<h2>Test 16: DROP TABLE</h2>
   423    457   <blockquote>
   424         -DROP TABLE t1;<br>DROP TABLE t2;
          458  +DROP TABLE t1;<br>DROP TABLE t2;<br>DROP TABLE t3;
   425    459   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   426         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.133</td></tr>
   427         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.014</td></tr>
   428         -<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.873</td></tr>
   429         -<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.224</td></tr>
          460  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.135</td></tr>
          461  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.015</td></tr>
          462  +<tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.939</td></tr>
          463  +<tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.254</td></tr>
   430    464   </table>
   431    465   
   432    466   <p>
   433    467   SQLite is slower than the other databases when it comes to dropping tables.
   434    468   This probably is because when SQLite drops a table, it has to go through and
   435    469   erase the records in the database file that deal with that table.  MySQL and
   436    470   PostgreSQL, on the other hand, use separate files to represent each table