Home | History | Annotate | Download | only in test 
      1 # 2007 May 17
      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 testing that the overflow-page related
     13 # enhancements added after version 3.3.17 speed things up.
     14 #
     15 # $Id: speed3.test,v 1.6 2009/07/09 02:48:24 shane Exp $
     16 #
     17 
     18 #---------------------------------------------------------------------
     19 # Test plan:
     20 #
     21 # If auto-vacuum is enabled for the database, the following cases
     22 # should show performance improvement with respect to 3.3.17.
     23 #
     24 #   + When deleting rows that span overflow pages. This is faster
     25 #     because the overflow pages no longer need to be read before
     26 #     they can be moved to the free list (test cases speed3-1.X). 
     27 #
     28 #   + When reading a column value stored on an overflow page that
     29 #     is not the first overflow page for the row. The improvement
     30 #     in this case is because the overflow pages between the tree
     31 #     page and the overflow page containing the value do not have
     32 #     to be read (test cases speed3-2.X).
     33 #
     34 
     35 set testdir [file dirname $argv0]
     36 source $testdir/tester.tcl
     37 
     38 ifcapable !tclvar||!attach {
     39   finish_test
     40   return
     41 }
     42 
     43 speed_trial_init speed1
     44 
     45 # Set a uniform random seed
     46 expr srand(0)
     47 
     48 set ::NROW 1000
     49 
     50 # The number_name procedure below converts its argment (an integer)
     51 # into a string which is the English-language name for that number.
     52 #
     53 # Example:
     54 #
     55 #     puts [number_name 123]   ->  "one hundred twenty three"
     56 #
     57 set ones {zero one two three four five six seven eight nine
     58           ten eleven twelve thirteen fourteen fifteen sixteen seventeen
     59           eighteen nineteen}
     60 set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
     61 proc number_name {n} {
     62   if {$n>=1000} {
     63     set txt "[number_name [expr {$n/1000}]] thousand"
     64     set n [expr {$n%1000}]
     65   } else {
     66     set txt {}
     67   }
     68   if {$n>=100} {
     69     append txt " [lindex $::ones [expr {$n/100}]] hundred"
     70     set n [expr {$n%100}]
     71   }
     72   if {$n>=20} {
     73     append txt " [lindex $::tens [expr {$n/10}]]"
     74     set n [expr {$n%10}]
     75   }
     76   if {$n>0} {
     77     append txt " [lindex $::ones $n]"
     78   }
     79   set txt [string trim $txt]
     80   if {$txt==""} {set txt zero}
     81   return $txt
     82 }
     83 
     84 proc populate_t1 {db} {
     85   $db transaction {
     86     for {set ii 0} {$ii < $::NROW} {incr ii} {
     87       set N [number_name $ii]
     88       set repeats [expr {(10000/[string length $N])+1}]
     89       set text [string range [string repeat $N $repeats] 0 10000]
     90       $db eval {INSERT INTO main.t1 VALUES($ii, $text, $ii)}
     91     }
     92     $db eval {INSERT INTO aux.t1 SELECT * FROM main.t1}
     93   }
     94 }
     95 
     96 
     97 proc io_log {db} {
     98   db_enter db
     99   array set stats1 [btree_pager_stats [btree_from_db db]]
    100   array set stats2 [btree_pager_stats [btree_from_db db 2]]
    101   db_leave db
    102 # puts "1: [array get stats1]"
    103 # puts "2: [array get stats2]"
    104   puts "Incrvacuum: Read $stats1(read), wrote $stats1(write)"
    105   puts "Normal    : Read $stats2(read), wrote $stats2(write)"
    106 }
    107 
    108 proc reset_db {} {
    109   db close
    110   sqlite3 db test.db
    111   db eval { 
    112     PRAGMA main.cache_size = 200000;
    113     PRAGMA main.auto_vacuum = 'incremental';
    114     ATTACH 'test2.db' AS 'aux'; 
    115     PRAGMA aux.auto_vacuum = 'none';
    116   }
    117 }
    118 
    119 file delete -force test2.db test2.db-journal
    120 reset_db
    121 
    122 # Set up a database in auto-vacuum mode and create a database schema.
    123 #
    124 do_test speed3-0.1 {
    125   execsql {
    126     CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);
    127   }
    128   execsql {
    129     SELECT name FROM sqlite_master ORDER BY 1;
    130   }
    131 } {t1}
    132 do_test speed3-0.2 {
    133   execsql {
    134     CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);
    135   }
    136   execsql {
    137     SELECT name FROM aux.sqlite_master ORDER BY 1;
    138   }
    139 } {t1}
    140 do_test speed3-0.3 {
    141   populate_t1 db
    142   execsql {
    143     SELECT count(*) FROM main.t1;
    144     SELECT count(*) FROM aux.t1;
    145   }
    146 } "$::NROW $::NROW"
    147 do_test speed3-0.4 {
    148   execsql {
    149     PRAGMA main.auto_vacuum;
    150     PRAGMA aux.auto_vacuum;
    151   }
    152 } {2 0}
    153 
    154 # Delete all content in a table, one row at a time.
    155 #
    156 #io_log db
    157 reset_db
    158 speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}
    159 speed_trial speed3-1.normal     $::NROW row {DELETE FROM aux.t1 WHERE 1}
    160 io_log db
    161 
    162 # Select the "C" column (located at the far end of the overflow 
    163 # chain) from each table row.
    164 #
    165 #db eval {PRAGMA incremental_vacuum(500000)}
    166 populate_t1 db
    167 reset_db
    168 speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}
    169 speed_trial speed3-2.normal     $::NROW row {SELECT c FROM aux.t1}
    170 io_log db
    171 
    172 finish_test
    173