/ Check-in [9f5b241c]
Login

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

Overview
Comment:Preparing for the 2.4.0 release. (CVS 426)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:9f5b241cb2fc89f66d3762b4b4978b8e114caf53
User & Date: drh 2002-03-11 02:06:13
Context
2002-03-11
02:15
Version 2.4.0 (CVS 443) check-in: d3f66b44 user: drh tags: trunk
02:06
Preparing for the 2.4.0 release. (CVS 426) check-in: 9f5b241c user: drh tags: trunk
2002-03-10
21:21
Bug fix: updates within a transaction would fail if there was existed a temporary table. (CVS 425) check-in: 02cc2d60 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -2.4.0-beta2
            1  +2.4.0

Changes to src/md5.c.

    26     26    * To compute the message digest of a chunk of bytes, declare an
    27     27    * MD5Context structure, pass it to MD5Init, call MD5Update as
    28     28    * needed on buffers full of bytes, and then call MD5Final, which
    29     29    * will fill a supplied 16-byte array with the digest.
    30     30    */
    31     31   #include <tcl.h>
    32     32   #include <string.h>
           33  +#include "sqlite.h"
    33     34   
    34     35   /*
    35     36    * If compiled on a machine that doesn't have a 32-bit integer,
    36     37    * you just set "uint32" to the appropriate datatype for an
    37     38    * unsigned 32-bit integer.  For example:
    38     39    *
    39     40    *       cc -Duint32='unsigned long' md5.c
................................................................................
   346    347   ** Register the two TCL commands above with the TCL interpreter.
   347    348   */
   348    349   int Md5_Init(Tcl_Interp *interp){
   349    350     Tcl_CreateCommand(interp, "md5", md5_cmd, 0, 0);
   350    351     Tcl_CreateCommand(interp, "md5file", md5file_cmd, 0, 0);
   351    352     return TCL_OK;
   352    353   }
          354  +
          355  +/*
          356  +** During testing, the special md5sum() aggregate function is available.
          357  +** inside SQLite.  The following routines implement that function.
          358  +*/
          359  +static void md5step(sqlite_func *context, int argc, const char **argv){
          360  +  MD5Context *p;
          361  +  int i;
          362  +  if( argc<1 ) return;
          363  +  p = sqlite_aggregate_context(context, sizeof(*p));
          364  +  if( p==0 ) return;
          365  +  if( sqlite_aggregate_count(context)==1 ){
          366  +    MD5Init(p);
          367  +  }
          368  +  for(i=0; i<argc; i++){
          369  +    if( argv[i] ){
          370  +      MD5Update(p, (unsigned char*)argv[i], strlen(argv[i]));
          371  +    }
          372  +  }
          373  +}
          374  +static void md5finalize(sqlite_func *context){
          375  +  MD5Context *p;
          376  +  unsigned char digest[16];
          377  +  char zBuf[33];
          378  +  p = sqlite_aggregate_context(context, sizeof(*p));
          379  +  MD5Final(digest,p);
          380  +  DigestToBase16(digest, zBuf);
          381  +  sqlite_set_result_string(context, zBuf, strlen(zBuf));
          382  +}
          383  +void Md5_Register(sqlite *db){
          384  +  sqlite_create_aggregate(db, "md5sum", -1, md5step, md5finalize, 0);
          385  +}

Changes to src/tclsqlite.c.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** A TCL Interface to SQLite
    13     13   **
    14         -** $Id: tclsqlite.c,v 1.29 2002/01/16 21:00:27 drh Exp $
           14  +** $Id: tclsqlite.c,v 1.30 2002/03/11 02:06:13 drh Exp $
    15     15   */
    16     16   #ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */
    17     17   
    18     18   #include "sqlite.h"
    19     19   #include "tcl.h"
    20     20   #include <stdlib.h>
    21     21   #include <string.h>
................................................................................
   527    527     if( p->db==0 ){
   528    528       Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE);
   529    529       Tcl_Free((char*)p);
   530    530       free(zErrMsg);
   531    531       return TCL_ERROR;
   532    532     }
   533    533     Tcl_CreateObjCommand(interp, argv[1], DbObjCmd, (char*)p, DbDeleteCmd);
          534  +#ifdef SQLITE_TEST
          535  +  {
          536  +     extern void Md5_Register(sqlite*);
          537  +     Md5_Register(p->db);
          538  +  }
          539  +#endif  
   534    540     return TCL_OK;
   535    541   }
   536    542   
   537    543   /*
   538    544   ** Provide a dummy Tcl_InitStubs if we are using this as a static
   539    545   ** library.
   540    546   */

Changes to src/test1.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Code for testing the printf() interface to SQLite.  This code
    13     13   ** is not included in the SQLite library.  It is used for automated
    14     14   ** testing of the SQLite library.
    15     15   **
    16         -** $Id: test1.c,v 1.6 2002/01/16 21:00:27 drh Exp $
           16  +** $Id: test1.c,v 1.7 2002/03/11 02:06:13 drh Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   #include "tcl.h"
    20     20   #include <stdlib.h>
    21     21   #include <string.h>
    22     22   
    23     23   /*
................................................................................
   319    319   ){
   320    320     char zBuf[200];
   321    321     sprintf(zBuf, "%d %d %d", sqlite_nMalloc, sqlite_nFree, sqlite_iMallocFail);
   322    322     Tcl_AppendResult(interp, zBuf, 0);
   323    323     return TCL_OK;
   324    324   }
   325    325   #endif
          326  +
          327  +/*
          328  +** Usage:  sqlite_abort
          329  +**
          330  +** Shutdown the process immediately.  This is not a clean shutdown.
          331  +** This command is used to test the recoverability of a database in
          332  +** the event of a program crash.
          333  +*/
          334  +static int sqlite_abort(
          335  +  void *NotUsed,
          336  +  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
          337  +  int argc,              /* Number of arguments */
          338  +  char **argv            /* Text of each argument */
          339  +){
          340  +  assert( interp==0 );   /* This will always fail */
          341  +  return TCL_OK;
          342  +}
   326    343   
   327    344   /*
   328    345   ** Register commands with the TCL interpreter.
   329    346   */
   330    347   int Sqlitetest1_Init(Tcl_Interp *interp){
   331    348     extern int sqlite_search_count;
   332    349     Tcl_CreateCommand(interp, "sqlite_mprintf_int", sqlite_mprintf_int, 0, 0);
................................................................................
   340    357     Tcl_CreateCommand(interp, "sqlite_close", sqlite_test_close, 0, 0);
   341    358     Tcl_LinkVar(interp, "sqlite_search_count", 
   342    359         (char*)&sqlite_search_count, TCL_LINK_INT);
   343    360   #ifdef MEMORY_DEBUG
   344    361     Tcl_CreateCommand(interp, "sqlite_malloc_fail", sqlite_malloc_fail, 0, 0);
   345    362     Tcl_CreateCommand(interp, "sqlite_malloc_stat", sqlite_malloc_stat, 0, 0);
   346    363   #endif
          364  +  Tcl_CreateCommand(interp, "sqlite_abort", sqlite_abort, 0, 0);
   347    365     return TCL_OK;
   348    366   }

Changes to test/trans.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this script is database locks.
    13     13   #
    14         -# $Id: trans.test,v 1.10 2002/01/10 14:31:49 drh Exp $
           14  +# $Id: trans.test,v 1.11 2002/03/11 02:06:14 drh Exp $
    15     15   
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   
    21     21   # Create several tables to work with.
................................................................................
   660    660   } {4 -5 -6 1 -2 -3}
   661    661   do_test trans-6.39 {
   662    662     execsql {
   663    663       SELECT * FROM t1 WHERE c<1;
   664    664     }
   665    665   } {1 -2 -3 4 -5 -6}
   666    666   
          667  +# Test to make sure rollback restores the database back to its original
          668  +# state.
          669  +#
          670  +do_test trans-7.1 {
          671  +  execsql {BEGIN}
          672  +  for {set i 0} {$i<1000} {incr i} {
          673  +    set r1 [expr {rand()}]
          674  +    set r2 [expr {rand()}]
          675  +    set r3 [expr {rand()}]
          676  +    execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)"
          677  +  }
          678  +  execsql {COMMIT}
          679  +  set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}]
          680  +  set ::checksum2 [
          681  +    execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
          682  +  ]
          683  +  execsql {SELECT count(*) FROM t2}
          684  +} {1001}
          685  +do_test trans-7.2 {
          686  +  execsql {SELECT md5sum(x,y,z) FROM t2}
          687  +} $checksum
          688  +do_test trans-7.2.1 {
          689  +  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
          690  +} $checksum2
          691  +do_test trans-7.3 {
          692  +  execsql {
          693  +    BEGIN;
          694  +    DELETE FROM t2;
          695  +    ROLLBACK;
          696  +    SELECT md5sum(x,y,z) FROM t2;
          697  +  }
          698  +} $checksum
          699  +do_test trans-7.4 {
          700  +  execsql {
          701  +    BEGIN;
          702  +    INSERT INTO t2 SELECT * FROM t2;
          703  +    ROLLBACK;
          704  +    SELECT md5sum(x,y,z) FROM t2;
          705  +  }
          706  +} $checksum
          707  +do_test trans-7.5 {
          708  +  execsql {
          709  +    BEGIN;
          710  +    DELETE FROM t2;
          711  +    ROLLBACK;
          712  +    SELECT md5sum(x,y,z) FROM t2;
          713  +  }
          714  +} $checksum
          715  +do_test trans-7.6 {
          716  +  execsql {
          717  +    BEGIN;
          718  +    INSERT INTO t2 SELECT * FROM t2;
          719  +    ROLLBACK;
          720  +    SELECT md5sum(x,y,z) FROM t2;
          721  +  }
          722  +} $checksum
          723  +do_test trans-7.7 {
          724  +  execsql {
          725  +    BEGIN;
          726  +    CREATE TABLE t3 AS SELECT * FROM t2;
          727  +    INSERT INTO t2 SELECT * FROM t3;
          728  +    ROLLBACK;
          729  +    SELECT md5sum(x,y,z) FROM t2;
          730  +  }
          731  +} $checksum
          732  +do_test trans-7.8 {
          733  +  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
          734  +} $checksum2
          735  +do_test trans-7.9 {
          736  +  execsql {
          737  +    BEGIN;
          738  +    CREATE TEMP TABLE t3 AS SELECT * FROM t2;
          739  +    INSERT INTO t2 SELECT * FROM t3;
          740  +    ROLLBACK;
          741  +    SELECT md5sum(x,y,z) FROM t2;
          742  +  }
          743  +} $checksum
          744  +do_test trans-7.10 {
          745  +  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
          746  +} $checksum2
          747  +do_test trans-7.11 {
          748  +  execsql {
          749  +    BEGIN;
          750  +    CREATE TEMP TABLE t3 AS SELECT * FROM t2;
          751  +    INSERT INTO t2 SELECT * FROM t3;
          752  +    DROP INDEX i2x;
          753  +    DROP INDEX i2y;
          754  +    CREATE INDEX i3a ON t3(x);
          755  +    ROLLBACK;
          756  +    SELECT md5sum(x,y,z) FROM t2;
          757  +  }
          758  +} $checksum
          759  +do_test trans-7.12 {
          760  +  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
          761  +} $checksum2
          762  +do_test trans-7.13 {
          763  +  execsql {
          764  +    BEGIN;
          765  +    DROP TABLE t2;
          766  +    ROLLBACK;
          767  +    SELECT md5sum(x,y,z) FROM t2;
          768  +  }
          769  +} $checksum
          770  +do_test trans-7.14 {
          771  +  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
          772  +} $checksum2
          773  +
          774  +# Arrange for another process to begin modifying the database but abort
          775  +# and die in the middle of the modification.  Then have this process read
          776  +# the database.  This process should detect the journal file and roll it
          777  +# back.  Verify that this happens correctly.
          778  +#
          779  +set fd [open test.tcl w]
          780  +puts $fd {
          781  +  sqlite db test.db
          782  +  db eval {
          783  +    BEGIN;
          784  +    CREATE TABLE t3 AS SELECT * FROM t2;
          785  +    DELETE FROM t2;
          786  +  }
          787  +  sqlite_abort
          788  +}
          789  +close $fd
          790  +do_test trans-8.1 {
          791  +  catch {exec [info nameofexec] test.tcl}
          792  +  execsql {SELECT md5sum(x,y,z) FROM t2}
          793  +} $checksum
          794  +do_test trans-8.2 {
          795  +  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
          796  +} $checksum2
          797  +
          798  +   
   667    799   finish_test

Changes to www/changes.tcl.

    13     13   
    14     14   
    15     15   proc chng {date desc} {
    16     16     puts "<DT><B>$date</B></DT>"
    17     17     puts "<DD><P><UL>$desc</UL></P></DD>"
    18     18   }
    19     19   
    20         -chng {2002 Mar * (2.4.0)} {
           20  +chng {2002 Mar 10 (2.4.0)} {
    21     21   <li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b>
    22     22       and make it available in all compiles.</li>
    23     23   <li>SELECT min() or max() of an indexed column with no WHERE or GROUP BY
    24     24       clause is handled as a special case which avoids a complete table scan.</li>
    25     25   <li>Automatically generated ROWIDs are now sequential.</li>
    26     26   <li>Do not allow dot-commands of the command-line shell to occur in the
    27     27       middle of a real SQL command.</li>
................................................................................
    36     36       contain real data (free pages) are not journalled and are not
    37     37       written from memory back to the disk when they change.  This does not 
    38     38       impact database integrity, since the
    39     39       pages contain no real data, but it does make large INSERT operations
    40     40       about 2.5 times faster and large DELETEs about 5 times faster.</li>
    41     41   <li>Made the CACHE_SIZE pragma persistent</li>
    42     42   <li>Added the SYNCHRONOUS pragma</li>
           43  +<li>Fixed a bug that was causing updates to fail inside of transactions when
           44  +    the database contained a temporary table.</li>
    43     45   }
    44     46   
    45     47   chng {2002 Feb 18 (2.3.3)} {
    46     48   <li>Allow identifiers to be quoted in square brackets, for compatibility
    47     49       with MS-Access.</li>
    48     50   <li>Added support for sub-queries in the FROM clause of a SELECT.</li>
    49     51   <li>More efficient implementation of sqliteFileExists() under Windows.

Changes to www/formatchng.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the formatchng.html file.
     3      3   #
     4         -set rcsid {$Id: formatchng.tcl,v 1.3 2002/03/04 02:26:17 drh Exp $ }
            4  +set rcsid {$Id: formatchng.tcl,v 1.4 2002/03/11 02:06:14 drh Exp $ }
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>File Format Changes in SQLite</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
    89     89     that are stored in the SQLITE_MASTER table.  If you create a database that
    90     90     contains this new syntax, then try to read that database using version 2.2.5
    91     91     or earlier, the parser will not understand the new syntax and you will get
    92     92     an error.  Otherwise, databases for 2.2.x and 2.3.x are interchangeable.</td>
    93     93   </tr>
    94     94   <tr>
    95     95     <td valign="top">2.3.3 to 2.4.0</td>
    96         -  <td valign="top">2002-Mar-?</td>
           96  +  <td valign="top">2002-Mar-10</td>
    97     97     <td>Beginning with version 2.4.0, SQLite added support for views. 
    98     98     Information about views is stored in the SQLITE_MASTER table.  If an older
    99     99     version of SQLite attempts to read a database that contains VIEW information
   100    100     in the SQLITE_MASTER table, the parser will not understand the new syntax
   101    101     and you will get an error.  Also, the
   102    102     way SQLite keeps track of unused disk blocks in the database file
   103    103     changed slightly.

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.27 2002/03/04 02:26:17 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.28 2002/03/11 02:06:14 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Query Language Understood By SQLite</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
   813    813   The pragma command is experimental and specific pragma statements may
   814    814   removed or added in future releases of SQLite.  Use this command
   815    815   with caution.</p>
   816    816   
   817    817   <p>The current implementation supports the following pragmas:</p>
   818    818   
   819    819   <ul>
   820         -<li><p><b>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
   821         -    <p>Change the maximum number of database disk pages that SQLite
   822         -    will hold in memory at once.  Each page uses about 1.5K of RAM.
   823         -    The default cache size is 100.  If you are doing UPDATEs or DELETEs
          820  +<li><p><b>PRAGMA cache_size;
          821  +       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
          822  +    <p>Query or change the maximum number of database disk pages that SQLite
          823  +    will hold in memory at once.  Each page uses about 1.5K of memory.
          824  +    The default cache size is 2000.  If you are doing UPDATEs or DELETEs
   824    825       that change many rows of a database and you do not mind if SQLite
   825    826       uses more memory, you can increase the cache size for a possible speed
   826         -    improvement.</p></li>
          827  +    improvement.</p>
          828  +    <p>When you change the cache size using the cache_size pragma, the
          829  +    change only endures for the current session.  The cache size reverts
          830  +    to the default value when the database is closed and reopened.  Use
          831  +    the <b>default_cache_size</b> pragma to check the cache size permanently
          832  +    </p></li>
   827    833   
   828    834   <li><p><b>PRAGMA count_changes = ON;
   829    835          <br>PRAGMA count_changes = OFF;</b></p>
   830    836       <p>When on, the COUNT_CHANGES pragma causes the callback function to
   831    837       be invoked once for each DELETE, INSERT, or UPDATE operation.  The
   832    838       argument is the number of rows that were changed.</p>
          839  +
          840  +<li><p><b>PRAGMA default_cache_size;
          841  +       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
          842  +    <p>Query or change the maximum number of database disk pages that SQLite
          843  +    will hold in memory at once.  Each page uses about 1.5K of memory.
          844  +    This pragma works like the <b>cache_size</b> pragma with the addition
          845  +    feature that it changes the cache size persistently.  With this pragma,
          846  +    you can set the cache size once and that setting is retained and reused
          847  +    everytime you reopen the database.</p></li>
          848  +
          849  +<li><p><b>PRAGMA default_synchronous;
          850  +       <br>PRAGMA default_synchronous = ON;
          851  +       <br>PRAGMA default_synchronous = OFF;</b></p>
          852  +    <p>Query or change the setting of the "synchronous" flag in
          853  +    the database.  When synchronous is on (the default), the SQLite database
          854  +    engine will pause at critical moments to make sure that data has actually
          855  +    be written to the disk surface.  (In other words, it invokes the
          856  +    equivalent of the <b>fsync()</b> system call.)  In synchronous mode,
          857  +    an SQLite database should be fully recoverable even if the operating
          858  +    system crashes or power is interrupted unexpectedly.  The penalty for
          859  +    this assurance is that some database operations take longer because the
          860  +    engine has to wait on the (relatively slow) disk drive.  The alternative
          861  +    is to turn synchronous off.  With synchronous off, SQLite continues
          862  +    processing as soon as it has handed data off to the operating system.
          863  +    If the application running SQLite crashes, the data will be safe, but
          864  +    the database could (in theory) become corrupted if the operating system
          865  +    crashes or the computer suddenly loses power.  On the other hand, some
          866  +    operations are as much as 50 or more times faster with synchronous off.
          867  +    </p>
          868  +    <p>This pragma changes the synchronous mode persistently.  Once changed,
          869  +    the mode stays as set even if the database is closed and reopened.  The
          870  +    <b>synchronous</b> pragma does the same thing but only applies the setting
          871  +    to the current session.</p>
   833    872   
   834    873   <li><p><b>PRAGMA empty_result_callbacks = ON;
   835    874          <br>PRAGMA empty_result_callbacks = OFF;</b></p>
   836    875       <p>When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback
   837    876       function to be invoked once for each query that has an empty result
   838    877       set.  The third "<b>argv</b>" parameter to the callback is set to NULL
   839    878       because there is no data to report.  But the second "<b>argc</b>" and
................................................................................
   868    907   
   869    908   <li><p><b>PRAGMA integrity_check;</b></p>
   870    909       <p>The command does an integrity check of the entire database.  It
   871    910       looks for out-of-order records, missing pages, and malformed records.
   872    911       If any problems are found, then a single string is returned which is
   873    912       a description of all problems.  If everything is in order, "ok" is
   874    913       returned.</p>
          914  +
          915  +<li><p><b>PRAGMA synchronous;
          916  +       <br>PRAGMA synchronous = ON;
          917  +       <br>PRAGMA synchronous = OFF;</b></p>
          918  +    <p>Query or change the setting of the "synchronous" flag in
          919  +    the database for the duration of the current database connect.
          920  +    The synchronous flag reverts to its default value when the database
          921  +    is closed and reopened.  For additional information on the synchronous
          922  +    flag, see the description of the <b>default_synchronous</b> pragma.</p>
          923  +    </li>
   875    924   
   876    925   <li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
   877    926       <p>For each column in the named table, invoke the callback function
   878    927       once with information about that column, including the column name,
   879    928       data type, whether or not the column can be NULL, and the default
   880    929       value for the column.</p>
   881    930   

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.5 2001/11/24 13:23:05 drh Exp $ }
            4  +set rcsid {$Id: speed.tcl,v 1.6 2002/03/11 02:06:14 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>
................................................................................
    14     14   puts "<p align=center>
    15     15   (This page was last modified on [lrange $rcsid 3 4] UTC)
    16     16   </p>"
    17     17   
    18     18   puts {
    19     19   <h2>Executive Summary</h2>
    20     20   
    21         -<p>A series of tests are run to measure the relative performance of
    22         -SQLite version 1.0 and 2.0 and PostgreSQL version 6.4.
           21  +<p>A series of tests were run to measure the relative performance of
           22  +SQLite 2.4.0, PostgreSQL, and MySQL
    23     23   The following are general
    24     24   conclusions drawn from these experiments:
    25     25   </p>
    26     26   
    27     27   <ul>
    28     28   <li><p>
    29         -  SQLite 2.0 is significantly faster than both SQLite 1.0 and PostgreSQL
           29  +  SQLite 2.4.0 is significantly faster than PostgreSQL
    30     30     for most common operations.
    31         -  SQLite 2.0 is over 4 times faster than PostgreSQL for simple
    32         -  query operations and about 7 times faster for <b>INSERT</b> statements 
    33         -  within a transaction.
    34     31   </p></li>
    35     32   <li><p>
    36         -  PostgreSQL performs better on complex queries, possibly due to having
    37         -  a more sophisticated query optimizer.
    38         -</p></li>
    39         -<li><p>
    40         -  SQLite 2.0 is significantly slower than both SQLite 1.0 and PostgreSQL
    41         -  on <b>DROP TABLE</b> statements and on doing lots of small <b>INSERT</b>
    42         -  statements that are not grouped into a single transaction.
           33  +  The speed of SQLite 2.4.0 is similar to MySQL.
           34  +  This is true in spite of the
           35  +  fact that SQLite contains full transaction support whereas the
           36  +  version of MySQL tested did not.
    43     37   </p></li>
    44     38   </ul>
    45     39   
    46     40   <h2>Test Environment</h2>
    47     41   
    48     42   <p>
    49         -The platform used for these tests is a 550MHz Athlon with 256MB or memory
    50         -and 33MHz IDE disk drives.  The operating system is RedHat Linux 6.0 with
    51         -various upgrades, including an upgrade to kernel version 2.2.18.
    52         -</p>
    53         -
    54         -<p>
    55         -PostgreSQL version 6.4.2 was used for these tests because that is what
    56         -came pre-installed with RedHat 6.0.  Newer version of PostgreSQL may give
    57         -better performance.
    58         -</p>
    59         -
    60         -<p>
    61         -SQLite version 1.0.32 was compiled with -O2 optimization and without
    62         -the -DNDEBUG=1 switch.  Setting the NDEBUG macro disables all "assert()"
    63         -statements within the code, but SQLite version 1.0 does not have any
    64         -expensive assert() statements so the difference in performance is
    65         -negligible.
    66         -</p>
    67         -
    68         -<p>
    69         -SQLite version 2.0-alpha-2 was compiled with -O2 optimization and
    70         -with the -DNDEBUG=1 compiler switch.  Setting the NDEBUG macro is very
    71         -important in SQLite version 2.0.  SQLite 2.0 contains some expensive
    72         -"assert()" statements in the inner loop of its processing.  Setting
    73         -the NDEBUG macro makes SQLite 2.0 run nearly twice as fast.
           43  +The platform used for these tests is a 1.6GHz Athlon with 1GB or memory
           44  +and an IDE disk drive.  The operating system is RedHat Linux 7.2 with
           45  +a stock kernel.
           46  +</p>
           47  +
           48  +<p>
           49  +The PostgreSQL and MySQL servers used were as delivered by default on
           50  +RedHat 7.2.  No effort was made to tune these engines.  Note in particular
           51  +the the default MySQL configuration on RedHat 7.2 does not support
           52  +transactions.  Not having to support transactions gives MySQL a
           53  +big advantage, but SQLite is still able to hold its own on most
           54  +tests.
           55  +</p>
           56  +
           57  +<p>
           58  +SQLite was compiled with -O6 optimization and with
           59  +the -DNDEBUG=1 switch which disables the many "assert()" statements
           60  +in the SQLite code.  The -DNDEBUG=1 compiler option roughly doubles
           61  +the speed of SQLite.
    74     62   </p>
    75     63   
    76     64   <p>
    77     65   All tests are conducted on an otherwise quiescent machine.
    78         -A simple shell script was used to generate and run all the tests.
    79         -Each test reports three different times:
           66  +A simple Tcl script was used to generate and run all the tests.
           67  +A copy of this Tcl script can be found in the SQLite source tree
           68  +in the file <b>tools/speedtest.tcl</b>.
           69  +</p>
           70  +
           71  +<p>
           72  +The times reported on all tests represent wall-clock time 
           73  +in seconds.  Two separate time values are reported for SQLite.
           74  +The first value is for SQLite in its default configuration with
           75  +full disk synchronization turned on.  With synchronization turned
           76  +on, SQLite executes
           77  +an <b>fsync()</b> system call (or the equivalent) at key points
           78  +to make certain that critical data has 
           79  +actually been written to the disk drive surface.  Synchronization
           80  +is necessary to guarantee the integrity of the database if the
           81  +operating system crashes or the computer powers down unexpectedly
           82  +in the middle of a database update.  The second time reported for SQLite is
           83  +when synchronization is turned off.  With synchronization off,
           84  +SQLite is sometimes much faster, but there is a risk that an
           85  +operating system crash or an unexpected power failure could
           86  +damage the database.  Generally speaking, the synchronous SQLite
           87  +times are for comparison against PostgreSQL (which is also
           88  +synchronous) and the asynchronous SQLite times are for 
           89  +comparison against the asynchronous MySQL engine.
           90  +</p>
           91  +
           92  +<h2>Test 1: 1000 INSERTs</h2>
           93  +<blockquote>
           94  +CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));<br>
           95  +INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');<br>
           96  +INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
           97  +<i>... 995 lines omitted</i><br>
           98  +INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');<br>
           99  +INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');<br>
          100  +INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');<br>
          101  +
          102  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          103  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.027</td></tr>
          104  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.113</td></tr>
          105  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;8.409</td></tr>
          106  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.188</td></tr>
          107  +</table>
          108  +
          109  +<p>SQLite must close and reopen the database file, and thus invalidate
          110  +its cache, for each SQL statement.  In spite of this, the asynchronous
          111  +version of SQLite is still nearly as fast as MySQL.  Notice how much slower
          112  +the synchronous version is, however.  This is due to the necessity of
          113  +calling <b>fsync()</b> after each SQL statement.</p>
          114  +
          115  +<h2>Test 2: 25000 INSERTs in a transaction</h2>
          116  +<blockquote>
          117  +BEGIN;<br>
          118  +CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
          119  +INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
          120  +<i>... 24997 lines omitted</i><br>
          121  +INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');<br>
          122  +INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');<br>
          123  +COMMIT;<br>
          124  +
          125  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          126  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.175</td></tr>
          127  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.444</td></tr>
          128  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.858</td></tr>
          129  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.739</td></tr>
          130  +</table>
          131  +
          132  +<p>
          133  +When all the INSERTs are put in a transaction, SQLite no longer has to
          134  +close and reopen the database between each statement.  It also does not
          135  +have to do any fsync()s until the very end.  When unshackled in
          136  +this way, SQLite is much faster than either PostgreSQL and MySQL.
          137  +</p>
          138  +
          139  +<h2>Test 3: 100 SELECTs without an index</h2>
          140  +<blockquote>
          141  +SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;<br>
          142  +SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;<br>
          143  +SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;<br>
          144  +<i>... 94 lines omitted</i><br>
          145  +SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;<br>
          146  +SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
          147  +SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>
          148  +
          149  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          150  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.773</td></tr>
          151  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.023</td></tr>
          152  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;6.281</td></tr>
          153  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.247</td></tr>
          154  +</table>
          155  +
          156  +<p>
          157  +This test does 100 queries on a 25000 entry table without an index,
          158  +thus requiring a full table scan.  SQLite is about half the speed of
          159  +PostgreSQL and MySQL.  This is because SQLite stores all data as strings
          160  +and must therefore call <b>strtod()</b> 5 million times in the
          161  +course of evaluating the WHERE clauses.  Both PostgreSQL and MySQL
          162  +store data as binary values where appropriate and can forego
          163  +this conversion effort.
          164  +</p>
          165  +
          166  +
          167  +<h2>Test 4: 100 SELECTs on a string comparison</h2>
          168  +<blockquote>
          169  +SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';<br>
          170  +SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';<br>
          171  +SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%three%';<br>
          172  +<i>... 94 lines omitted</i><br>
          173  +SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety eight%';<br>
          174  +SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';<br>
          175  +SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>
          176  +
          177  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          178  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;16.726</td></tr>
          179  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.237</td></tr>
          180  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;6.137</td></tr>
          181  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.112</td></tr>
          182  +</table>
          183  +
          184  +<p>
          185  +This set of 100 queries uses string comparisons instead of
          186  +numerical comparisions.  As a result, the speed of SQLite is
          187  +compariable to are better then PostgreSQL and MySQL.
          188  +</p>
          189  +
          190  +<h2>Test 5: Creating an index</h2>
          191  +<blockquote>
          192  +CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
          193  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          194  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.510</td></tr>
          195  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.352</td></tr>
          196  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.809</td></tr>
          197  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.720</td></tr>
          198  +</table>
          199  +
          200  +<p>
          201  +SQLite is slower at creating new indices.  But since creating
          202  +new indices is an uncommon operation, this is not seen as a
          203  +problem.
          204  +</p>
          205  +
          206  +<h2>Test 6: 5000 SELECTs with an index</h2>
          207  +<blockquote>
          208  +SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;<br>
          209  +SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;<br>
          210  +SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
          211  +<i>... 4994 lines omitted</i><br>
          212  +SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
          213  +SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>
          214  +SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>
          215  +
          216  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          217  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.318</td></tr>
          218  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.555</td></tr>
          219  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;1.289</td></tr>
          220  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.273</td></tr>
          221  +</table>
          222  +
          223  +<p>
          224  +This test runs a set of 5000 queries that are similar in form to
          225  +those in test 3.  But now instead of being half as fast, SQLite
          226  +is faster than both PostgreSQL and MySQL.
    80    227   </p>
    81    228   
          229  +<h2>Test 7: 1000 UPDATEs without an index</h2>
          230  +<blockquote>
          231  +BEGIN;<br>
          232  +UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;<br>
          233  +UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
          234  +<i>... 996 lines omitted</i><br>
          235  +UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
          236  +UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>
          237  +COMMIT;<br>
          238  +
          239  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          240  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.828</td></tr>
          241  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;9.272</td></tr>
          242  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.915</td></tr>
          243  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.889</td></tr>
          244  +</table>
          245  +
    82    246   <p>
    83         -<ol>
    84         -<li> "<b>Real</b>" or wall-clock time. </li>
    85         -<li> "<b>User</b>" time, the time spent executing user-level code. </li>
    86         -<li> "<b>Sys</b>" or system time, the time spent in the operating system. </li>
    87         -</ol>
          247  +Here is a case where MySQL is over 10 times slower than SQLite.
          248  +The reason for this is unclear.
    88    249   </p>
    89    250   
          251  +<h2>Test 8: 25000 UPDATEs with an index</h2>
          252  +<blockquote>
          253  +BEGIN;<br>
          254  +UPDATE t2 SET b=271822 WHERE a=1;<br>
          255  +UPDATE t2 SET b=28304 WHERE a=2;<br>
          256  +<i>... 24996 lines omitted</i><br>
          257  +UPDATE t2 SET b=442549 WHERE a=24999;<br>
          258  +UPDATE t2 SET b=423958 WHERE a=25000;<br>
          259  +COMMIT;<br>
          260  +
          261  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          262  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;28.021</td></tr>
          263  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.565</td></tr>
          264  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;10.939</td></tr>
          265  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;11.199</td></tr>
          266  +</table>
          267  +
    90    268   <p>
    91         -PostgreSQL uses a client-server model.  The experiment is unable to measure
    92         -CPU used by the server, only the client, so the "user" and "sys" numbers
    93         -from PostgreSQL are meaningless.
          269  +In this case MySQL is slightly faster than SQLite, though not by much.
          270  +The difference is believed to have to do with the fact SQLite 
          271  +handles the integers as strings instead of binary numbers.
    94    272   </p>
    95    273   
    96         -<h2>Test 1: CREATE TABLE</h2>
          274  +<h2>Test 9: 25000 text UPDATEs with an index</h2>
          275  +<blockquote>
          276  +BEGIN;<br>
          277  +UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1;<br>
          278  +UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
          279  +<i>... 24996 lines omitted</i><br>
          280  +UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
          281  +UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;<br>
          282  +COMMIT;<br>
    97    283   
    98         -<blockquote><pre>
    99         -CREATE TABLE t1(f1 int, f2 int, f3 int);
   100         -COPY t1 FROM '/home/drh/sqlite/bld/speeddata3.txt';
   101         -
   102         -PostgreSQL:   real   1.84
   103         -SQLite 1.0:   real   3.29   user   0.64   sys   1.60
   104         -SQLite 2.0:   real   0.77   user   0.51   sys   0.05
   105         -</pre></blockquote>
          284  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          285  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;48.739</td></tr>
          286  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;7.059</td></tr>
          287  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;7.868</td></tr>
          288  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.720</td></tr>
          289  +</table>
   106    290   
   107    291   <p>
   108         -The speeddata3.txt data file contains 30000 rows of data.
          292  +When updating a text field instead of an integer field,
          293  +SQLite is slightly faster than MySQL.
   109    294   </p>
   110    295   
   111         -<h2>Test 2: SELECT</h2>
   112         -
   113         -<blockquote><pre>
   114         -SELECT max(f2), min(f3), count(*) FROM t1
   115         -WHERE f3<10000 OR f1>=20000;
   116         -
   117         -PostgreSQL:   real   1.22
   118         -SQLite 1.0:   real   0.80   user   0.67   sys   0.12
   119         -SQLite 2.0:   real   0.65   user   0.60   sys   0.05
   120         -</pre></blockquote>
          296  +<h2>Test 10: INSERTs from a SELECT</h2>
          297  +<blockquote>
          298  +BEGIN;<br>INSERT INTO t1 SELECT * FROM t2;<br>INSERT INTO t2 SELECT * FROM t1;<br>COMMIT;
          299  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          300  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;54.822</td></tr>
          301  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.512</td></tr>
          302  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;4.423</td></tr>
          303  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;2.386</td></tr>
          304  +</table>
   121    305   
   122    306   <p>
   123         -With no indices, a complete scan of the table must be performed
   124         -(all 30000 rows) in order to complete this query.
   125         -</p>
   126         -
   127         -<h2>Test 3: CREATE INDEX</h2>
   128         -
   129         -<blockquote><pre>
   130         -CREATE INDEX idx1 ON t1(f1);
   131         -CREATE INDEX idx2 ON t1(f2,f3);
   132         -
   133         -PostgreSQL:   real   2.24
   134         -SQLite 1.0:   real   5.37   user   1.22   sys   3.10
   135         -SQLite 2.0:   real   3.71   user   2.31   sys   1.06
   136         -</pre></blockquote>
   137         -
   138         -<p>
   139         -PostgreSQL is fastest at creating new indices.
   140         -Note that SQLite 2.0 is faster than SQLite 1.0 but still
   141         -spends longer in user-space code.
          307  +The poor performance of PostgreSQL in this case appears to be due to its
          308  +synchronous behavior.  The CPU was mostly idle during the 55 second run.
   142    309   </p>
   143    310   
   144         -<h2>Test 4: SELECT using an index</h2>
          311  +<h2>Test 11: DELETE without an index</h2>
          312  +<blockquote>
          313  +DELETE FROM t2 WHERE c LIKE '%fifty%';
          314  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          315  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.734</td></tr>
          316  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.888</td></tr>
          317  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;5.405</td></tr>
          318  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.731</td></tr>
          319  +</table>
   145    320   
   146         -<blockquote><pre>
   147         -SELECT max(f2), min(f3), count(*) FROM t1
   148         -WHERE f3<10000 OR f1>=20000;
   149         -
   150         -PostgreSQL:   real   0.19
   151         -SQLite 1.0:   real   0.77   user   0.66   sys   0.12
   152         -SQLite 2.0:   real   0.62   user   0.62   sys   0.01
   153         -</pre></blockquote>
   154    321   
   155         -<p>
   156         -This is the same query as in Test 2, but now there are indices.
   157         -Unfortunately, SQLite is reasonably simple-minded about its querying
   158         -and not able to take advantage of the indices.  It still does a
   159         -linear scan of the entire table.  PostgreSQL, on the other hand,
   160         -is able to use the indices to make its query over six times faster.
   161         -</p>
          322  +<h2>Test 12: DELETE with an index</h2>
          323  +<blockquote>
          324  +DELETE FROM t2 WHERE a>10 AND a<20000;
          325  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          326  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.318</td></tr>
          327  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.600</td></tr>
          328  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;1.436</td></tr>
          329  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.775</td></tr>
          330  +</table>
          331  +
   162    332   
   163         -<h2>Test 5: SELECT a single record</h2>
   164         -
   165         -<blockquote><pre>
   166         -SELECT f2, f3 FROM t1 WHERE f1==1;
   167         -SELECT f2, f3 FROM t1 WHERE f1==2;
   168         -SELECT f2, f3 FROM t1 WHERE f1==3;
   169         -...
   170         -SELECT f2, f3 FROM t1 WHERE f1==998;
   171         -SELECT f2, f3 FROM t1 WHERE f1==999;
   172         -SELECT f2, f3 FROM t1 WHERE f1==1000;
   173         -
   174         -PostgreSQL:   real   0.95
   175         -SQLite 1.0:   real  15.70   user   0.70   sys  14.41
   176         -SQLite 2.0:   real   0.20   user   0.15   sys   0.05
   177         -</pre></blockquote>
          333  +<h2>Test 13: A big INSERT after a big DELETE</h2>
          334  +<blockquote>
          335  +INSERT INTO t2 SELECT * FROM t1;
          336  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          337  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;63.867</td></tr>
          338  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.839</td></tr>
          339  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;3.971</td></tr>
          340  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.993</td></tr>
          341  +</table>
   178    342   
   179    343   <p>
   180         -This test involves 1000 separate SELECT statements, only the first
   181         -and last three of which are show above.  SQLite 2.0 is the clear
   182         -winner.  The miserable showing by SQLite 1.0 is due (it is thought)
   183         -to the high overhead of executing <b>gdbm_open</b> 2000 times in
   184         -quick succession.
   185         -</p>
   186         -
   187         -<h2>Test 6: UPDATE</h2>
   188         -
   189         -<blockquote><pre>
   190         -UPDATE t1 SET f2=f3, f3=f2
   191         -WHERE f1 BETWEEN 15000 AND 20000;
   192         -
   193         -PostgreSQL:   real   6.56
   194         -SQLite 1.0:   real   3.54   user   0.74   sys   1.16
   195         -SQLite 2.0:   real   2.70   user   0.70   sys   1.25
   196         -</pre></blockquote>
   197         -
   198         -<p>
   199         -We have no explanation for why PostgreSQL does poorly here.
          344  +Earlier versions of SQLite would show decreasing performance after a
          345  +sequence DELETEs followed by new INSERTs.  As this test shows, the
          346  +problem has now been resolved.
   200    347   </p>
   201    348   
   202         -<h2>Test 7: INSERT from a SELECT</h2>
   203         -
   204         -<blockquote><pre>
   205         -CREATE TABLE t2(f1 int, f2 int);
   206         -INSERT INTO t2 SELECT f1, f2 FROM t1 WHERE f3<10000;
          349  +<h2>Test 14: A big DELETE followed by many small INSERTs</h2>
          350  +<blockquote>
          351  +BEGIN;<br>
          352  +DELETE FROM t1;<br>
          353  +INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');<br>
          354  +<i>... 2997 lines omitted</i><br>
          355  +INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');<br>
          356  +INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');<br>
          357  +COMMIT;<br>
   207    358   
   208         -PostgreSQL:   real   2.05
   209         -SQLite 1.0:   real   1.80   user   0.81   sys   0.73
   210         -SQLite 2.0:   real   0.69   user   0.58   sys   0.07
   211         -</pre></blockquote>
   212         -
   213         -
   214         -<h2>Test 8: Many small INSERTs</h2>
          359  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          360  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.209</td></tr>
          361  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.031</td></tr>
          362  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.298</td></tr>
          363  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.282</td></tr>
          364  +</table>
   215    365   
   216         -<blockquote><pre>
   217         -CREATE TABLE t3(f1 int, f2 int, f3 int);
   218         -INSERT INTO t3 VALUES(1,1641,1019);
   219         -INSERT INTO t3 VALUES(2,984,477);
   220         -...
   221         -INSERT INTO t3 VALUES(998,1411,1392);
   222         -INSERT INTO t3 VALUES(999,1715,526);
   223         -INSERT INTO t3 VALUES(1000,1906,1037);
   224         -
   225         -PostgreSQL:   real   5.28
   226         -SQLite 1.0:   real   2.20   user   0.21   sys   0.67
   227         -SQLite 2.0:   real  10.99   user   0.21   sys   7.02
   228         -</pre></blockquote>
          366  +<h2>Test 15: DROP TABLE</h2>
          367  +<blockquote>
          368  +DROP TABLE t1;<br>DROP TABLE t2;
          369  +</blockquote><table border=0 cellpadding=0 cellspacing=0>
          370  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.105</td></tr>
          371  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.015</td></tr>
          372  +<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.472</td></tr>
          373  +<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.232</td></tr>
          374  +</table>
   229    375   
   230    376   <p>
   231         -This test involves 1000 separate INSERT statements, only 5 of which
   232         -are shown above.  SQLite 2.0 does poorly because of its atomic commit
   233         -logic.  A minimum of two calls to <b>fsync()</b> are required for each
   234         -INSERT statement, and that really slows things down.  On the other hand,
   235         -PostgreSQL also has to support atomic commits and it seems to do so
   236         -efficiently.
   237         -</p>
   238         -
   239         -<h2>Test 9: Many small INSERTs within a TRANSACTION</h2>
   240         -
   241         -<blockquote><pre>
   242         -CREATE TABLE t4(f1 int, f2 int, f3 int);
   243         -BEGIN TRANSACTION;
   244         -INSERT INTO t4 VALUES(1,440,1084);
   245         -...
   246         -INSERT INTO t4 VALUES(999,1527,423);
   247         -INSERT INTO t4 VALUES(1000,74,1865);
   248         -COMMIT;
   249         -
   250         -PostgreSQL:   real   0.68
   251         -SQLite 1.0:   real   1.72   user   0.09   sys   0.55
   252         -SQLite 2.0:   real   0.10   user   0.08   sys   0.02
   253         -</pre></blockquote>
   254         -
   255         -<p>
   256         -By putting all the inserts inside a single transaction, there
   257         -only needs to be a single atomic commit at the very end.  This
   258         -allows SQLite 2.0 to go (literally) 100 times faster!  PostgreSQL
   259         -only gets a eight-fold speedup.  Perhaps PostgreSQL is limited here by
   260         -the IPC overhead.
   261         -</p>
   262         -
   263         -<h2>Test 10: DELETE</h2>
   264         -
   265         -<blockquote><pre>
   266         -DELETE FROM t1 WHERE f2 NOT BETWEEN 10000 AND 20000;
   267         -
   268         -PostgreSQL:   real   7.25
   269         -SQLite 1.0:   real   6.98   user   1.66   sys   4.11
   270         -SQLite 2.0:   real   5.89   user   1.35   sys   3.11
   271         -</pre></blockquote>
   272         -
   273         -<p>
   274         -All three database run at about the same speed here.
   275         -</p>
   276         -
   277         -<h2>Test 11: DROP TABLE</h2>
   278         -
   279         -<blockquote><pre>
   280         -BEGIN TRANSACTION;
   281         -DROP TABLE t1; DROP TABLE t2;
   282         -DROP TABLE t3; DROP TABLE t4;
   283         -COMMIT;
   284         -
   285         -PostgreSQL:   real   0.06
   286         -SQLite 1.0:   real   0.03   user   0.00   sys   0.02
   287         -SQLite 2.0:   real   3.12   user   0.02   sys   0.31
   288         -</pre></blockquote>
   289         -
   290         -<p>
   291         -SQLite 2.0 is much slower at dropping tables.  This may be because
   292         -both SQLite 1.0 and PostgreSQL can drop a table simply by unlinking
   293         -or renaming a file, since both store database tables in separate files.
   294         -SQLite 2.0, on the other hand, uses a single file for the entire
   295         -database, so dropping a table involves moving lots of page of that
   296         -file to the free-list, which takes time.
          377  +SQLite is slower than the other databases when it comes to dropping tables.
          378  +This is not seen as a big problem, however, since DROP TABLE is seldom
          379  +used in speed-critical situations.
   297    380   </p>
   298    381   
   299    382   }
   300    383   puts {
   301    384   <p><hr /></p>
   302    385   <p><a href="index.html"><img src="/goback.jpg" border=0 />
   303    386   Back to the SQLite Home Page</a>
   304    387   </p>
   305    388   
   306    389   </body></html>}