/ Check-in [ab53f508]
Login

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

Overview
Comment:Avoid unnecessary calls to pager_unwritelock() when in exclusive-access mode. Add the speed2.test script to the test suite. (CVS 3718)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ab53f5086334ea2f6c20c8f9d043baff39fae8d7
User & Date: drh 2007-03-26 13:48:13
Context
2007-03-26
15:46
Discard the pager-cache when the page-size is changed. (CVS 3719) check-in: e4209f71 user: danielk1977 tags: trunk
13:48
Avoid unnecessary calls to pager_unwritelock() when in exclusive-access mode. Add the speed2.test script to the test suite. (CVS 3718) check-in: ab53f508 user: drh tags: trunk
12:26
Run some malloc() tests with exclusive-access mode. (CVS 3717) check-in: 12745490 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pager.c.

    14     14   ** The pager is used to access a database disk file.  It implements
    15     15   ** atomic commit and rollback through the use of a journal file that
    16     16   ** is separate from the database file.  The pager also implements file
    17     17   ** locking to prevent two processes from writing the same database
    18     18   ** file simultaneously, or one process from reading the database while
    19     19   ** another is writing.
    20     20   **
    21         -** @(#) $Id: pager.c,v 1.297 2007/03/26 12:26:27 danielk1977 Exp $
           21  +** @(#) $Id: pager.c,v 1.298 2007/03/26 13:48:13 drh Exp $
    22     22   */
    23     23   #ifndef SQLITE_OMIT_DISKIO
    24     24   #include "sqliteInt.h"
    25     25   #include "os.h"
    26     26   #include "pager.h"
    27     27   #include <assert.h>
    28     28   #include <string.h>
................................................................................
  3010   3010       }
  3011   3011     
  3012   3012       /* When all pages reach the freelist, drop the read lock from
  3013   3013       ** the database file.
  3014   3014       */
  3015   3015       pPager->nRef--;
  3016   3016       assert( pPager->nRef>=0 );
  3017         -    if( pPager->nRef==0 ){
         3017  +    if( pPager->nRef==0 && !pPager->exclusiveMode ){
  3018   3018         pagerUnlockAndRollback(pPager);
  3019   3019       }
  3020   3020     }
  3021   3021     return SQLITE_OK;
  3022   3022   }
  3023   3023   
  3024   3024   /*

Changes to test/quick.test.

     2      2   #    May you do good and not evil.
     3      3   #    May you find forgiveness for yourself and forgive others.
     4      4   #    May you share freely, never taking more than you give.
     5      5   #
     6      6   #***********************************************************************
     7      7   # This file runs all tests.
     8      8   #
     9         -# $Id: quick.test,v 1.49 2007/03/26 12:26:27 danielk1977 Exp $
            9  +# $Id: quick.test,v 1.50 2007/03/26 13:48:14 drh Exp $
    10     10   
    11     11   proc lshift {lvar} {
    12     12     upvar $lvar l
    13     13     set ret [lindex $l 0]
    14     14     set l [lrange $l 1 end]
    15     15     return $ret
    16     16   }
................................................................................
    49     49     malloc.test
    50     50     malloc2.test
    51     51     malloc3.test
    52     52     memleak.test
    53     53     misuse.test
    54     54     quick.test
    55     55     speed1.test
           56  +  speed2.test
    56     57   
    57     58     autovacuum_crash.test
    58     59     btree8.test
    59     60     utf16.test
    60     61     shared_err.test
    61     62     vtab_err.test
    62     63   }

Changes to test/speed1.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 measuring executing speed.
    13     13   #
    14         -# $Id: speed1.test,v 1.2 2006/11/30 13:06:00 drh Exp $
           14  +# $Id: speed1.test,v 1.3 2007/03/26 13:48:14 drh Exp $
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   set sqlout [open speed1.txt w]
    21     21   proc tracesql {sql} {
    22     22     puts $::sqlout $sql\;
    23     23   }
    24         -db trace tracesql
           24  +#db trace tracesql
    25     25   
    26     26   # The number_name procedure below converts its argment (an integer)
    27     27   # into a string which is the English-language name for that number.
    28     28   #
    29     29   # Example:
    30     30   #
    31     31   #     puts [number_name 123]   ->  "one hundred twenty three"
................................................................................
    57     57     return $txt
    58     58   }
    59     59   
    60     60   # Create a database schema.
    61     61   #
    62     62   do_test speed1-1.0 {
    63     63     execsql {
    64         -pragma page_size=4096;
           64  +    PRAGMA page_size=1024;
           65  +    PRAGMA cache_size=8192;
           66  +    PRAGMA locking_mode=EXCLUSIVE;
    65     67       CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
    66     68       CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
    67     69       CREATE INDEX i2a ON t2(a);
    68     70       CREATE INDEX i2b ON t2(b);
           71  +  }
           72  +  execsql {
    69     73       SELECT name FROM sqlite_master ORDER BY 1;
    70     74     }
    71     75   } {i2a i2b t1 t2}
    72     76   
    73     77   
    74     78   # 50000 INSERTs on an unindexed table
    75     79   #

Added test/speed2.test.

            1  +# 2006 November 23
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this script is measuring executing speed.
           13  +#
           14  +# $Id: speed2.test,v 1.1 2007/03/26 13:48:14 drh Exp $
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +set sqlout [open speed1.txt w]
           21  +proc tracesql {sql} {
           22  +  puts $::sqlout $sql\;
           23  +}
           24  +#db trace tracesql
           25  +
           26  +# The number_name procedure below converts its argment (an integer)
           27  +# into a string which is the English-language name for that number.
           28  +#
           29  +# Example:
           30  +#
           31  +#     puts [number_name 123]   ->  "one hundred twenty three"
           32  +#
           33  +set ones {zero one two three four five six seven eight nine
           34  +          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
           35  +          eighteen nineteen}
           36  +set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
           37  +proc number_name {n} {
           38  +  if {$n>=1000} {
           39  +    set txt "[number_name [expr {$n/1000}]] thousand"
           40  +    set n [expr {$n%1000}]
           41  +  } else {
           42  +    set txt {}
           43  +  }
           44  +  if {$n>=100} {
           45  +    append txt " [lindex $::ones [expr {$n/100}]] hundred"
           46  +    set n [expr {$n%100}]
           47  +  }
           48  +  if {$n>=20} {
           49  +    append txt " [lindex $::tens [expr {$n/10}]]"
           50  +    set n [expr {$n%10}]
           51  +  }
           52  +  if {$n>0} {
           53  +    append txt " [lindex $::ones $n]"
           54  +  }
           55  +  set txt [string trim $txt]
           56  +  if {$txt==""} {set txt zero}
           57  +  return $txt
           58  +}
           59  +
           60  +# Create a database schema.
           61  +#
           62  +do_test speed1-1.0 {
           63  +  execsql {
           64  +    PRAGMA page_size=1024;
           65  +    PRAGMA cache_size=8192;
           66  +    PRAGMA locking_mode=EXCLUSIVE;
           67  +    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
           68  +    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
           69  +    CREATE INDEX i2a ON t2(a);
           70  +    CREATE INDEX i2b ON t2(b);
           71  +  }
           72  +  execsql {
           73  +    SELECT name FROM sqlite_master ORDER BY 1;
           74  +  }
           75  +} {i2a i2b t1 t2}
           76  +
           77  +
           78  +# 50000 INSERTs on an unindexed table
           79  +#
           80  +set sql {}
           81  +for {set i 1} {$i<=50000} {incr i} {
           82  +  set r [expr {int(rand()*500000)}]
           83  +  append sql "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');\n"
           84  +}
           85  +db eval BEGIN
           86  +speed_trial speed1-insert1 50000 row $sql
           87  +db eval COMMIT
           88  +
           89  +# 50000 INSERTs on an indexed table
           90  +#
           91  +set sql {}
           92  +for {set i 1} {$i<=50000} {incr i} {
           93  +  set r [expr {int(rand()*500000)}]
           94  +  append sql "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');\n"
           95  +}
           96  +db eval BEGIN
           97  +speed_trial speed1-insert2 50000 row $sql
           98  +db eval COMMIT
           99  +
          100  +
          101  +
          102  +# 50 SELECTs on an integer comparison.  There is no index so
          103  +# a full table scan is required.
          104  +#
          105  +set sql {}
          106  +for {set i 0} {$i<50} {incr i} {
          107  +  set lwr [expr {$i*100}]
          108  +  set upr [expr {($i+10)*100}]
          109  +  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
          110  +}
          111  +speed_trial speed1-select1 [expr {50*50000}] row $sql
          112  +
          113  +# 50 SELECTs on an LIKE comparison.  There is no index so a full
          114  +# table scan is required.
          115  +#
          116  +set sql {}
          117  +for {set i 0} {$i<50} {incr i} {
          118  +  append sql \
          119  +    "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"
          120  +}
          121  +speed_trial speed1-select2 [expr {50*50000}] row $sql
          122  +
          123  +# Create indices
          124  +#
          125  +db eval BEGIN
          126  +speed_trial speed1-createidx 150000 row {
          127  +  CREATE INDEX i1a ON t1(a);
          128  +  CREATE INDEX i1b ON t1(b);
          129  +  CREATE INDEX i1c ON t1(c);
          130  +}
          131  +db eval COMMIT
          132  +
          133  +# 5000 SELECTs on an integer comparison where the integer is
          134  +# indexed.
          135  +#
          136  +set sql {}
          137  +for {set i 0} {$i<5000} {incr i} {
          138  +  set lwr [expr {$i*100}]
          139  +  set upr [expr {($i+10)*100}]
          140  +  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
          141  +}
          142  +speed_trial speed1-select3 5000 stmt $sql
          143  +
          144  +# 100000 random SELECTs against rowid.
          145  +#
          146  +set sql {}
          147  +for {set i 1} {$i<=100000} {incr i} {
          148  +  set id [expr {int(rand()*50000)+1}]
          149  +  append sql "SELECT c=='hi' FROM t1 WHERE rowid=$id;\n"
          150  +}
          151  +speed_trial speed1-select4 100000 row $sql
          152  +
          153  +# 100000 random SELECTs against a unique indexed column.
          154  +#
          155  +set sql {}
          156  +for {set i 1} {$i<=100000} {incr i} {
          157  +  set id [expr {int(rand()*50000)+1}]
          158  +  append sql "SELECT c FROM t1 WHERE a=$id;"
          159  +}
          160  +speed_trial speed1-select5 100000 row $sql
          161  +
          162  +# 50000 random SELECTs against an indexed column text column
          163  +#
          164  +set sql {}
          165  +db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {
          166  +  append sql "SELECT c FROM t1 WHERE c='$c';"
          167  +}
          168  +speed_trial speed1-select6 50000 row $sql
          169  +
          170  +# Vacuum
          171  +speed_trial speed1-vacuum 100000 row VACUUM
          172  +
          173  +# 5000 updates of ranges where the field being compared is indexed.
          174  +#
          175  +set sql {}
          176  +for {set i 0} {$i<5000} {incr i} {
          177  +  set lwr [expr {$i*2}]
          178  +  set upr [expr {($i+1)*2}]
          179  +  append sql "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
          180  +}
          181  +db eval BEGIN
          182  +speed_trial speed1-update1 5000 stmt $sql
          183  +db eval COMMIT
          184  +
          185  +# 50000 single-row updates.  An index is used to find the row quickly.
          186  +#
          187  +set sql {}
          188  +for {set i 0} {$i<50000} {incr i} {
          189  +  set r [expr {int(rand()*500000)}]
          190  +  append sql "UPDATE t1 SET b=$r WHERE a=$i;"
          191  +}
          192  +db eval BEGIN
          193  +speed_trial speed1-update2 50000 row $sql
          194  +db eval COMMIT
          195  +
          196  +# 1 big text update that touches every row in the table.
          197  +#
          198  +speed_trial speed1-update3 50000 row {
          199  +  UPDATE t1 SET c=a;
          200  +}
          201  +
          202  +# Many individual text updates.  Each row in the table is
          203  +# touched through an index.
          204  +#
          205  +set sql {}
          206  +for {set i 1} {$i<=50000} {incr i} {
          207  +  set r [expr {int(rand()*500000)}]
          208  +  append sql "UPDATE t1 SET c='[number_name $r]' WHERE a=$i;"
          209  +}
          210  +db eval BEGIN
          211  +speed_trial speed1-update4 50000 row $sql
          212  +db eval COMMIT
          213  +
          214  +# Delete all content in a table.
          215  +#
          216  +speed_trial speed1-delete1 50000 row {DELETE FROM t1}
          217  +
          218  +# Copy one table into another
          219  +#
          220  +speed_trial speed1-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}
          221  +
          222  +# Delete all content in a table, one row at a time.
          223  +#
          224  +speed_trial speed1-delete2 50000 row {DELETE FROM t1 WHERE 1}
          225  +
          226  +# Refill the table yet again
          227  +#
          228  +speed_trial speed1-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}
          229  +
          230  +# Drop the table and recreate it without its indices.
          231  +#
          232  +db eval BEGIN
          233  +speed_trial speed1-drop1 50000 row {
          234  +   DROP TABLE t1;
          235  +   CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
          236  +}
          237  +db eval COMMIT
          238  +
          239  +# Refill the table yet again.  This copy should be faster because
          240  +# there are no indices to deal with.
          241  +#
          242  +speed_trial speed1-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}
          243  +
          244  +# Select 20000 rows from the table at random.
          245  +#
          246  +speed_trial speed1-random1 50000 row {
          247  +  SELECT rowid FROM t1 ORDER BY random() LIMIT 20000
          248  +}
          249  +
          250  +# Delete 20000 random rows from the table.
          251  +#
          252  +speed_trial speed1-random-del1 20000 row {
          253  +  DELETE FROM t1 WHERE rowid IN
          254  +    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)
          255  +}
          256  +do_test speed1-1.1 {
          257  +  db one {SELECT count(*) FROM t1}
          258  +} 30000
          259  +
          260  +    
          261  +# Delete 20000 more rows at random from the table.
          262  +#
          263  +speed_trial speed1-random-del2 20000 row {
          264  +  DELETE FROM t1 WHERE rowid IN
          265  +    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)
          266  +}
          267  +do_test speed1-1.2 {
          268  +  db one {SELECT count(*) FROM t1}
          269  +} 10000
          270  +
          271  +finish_test