AryaWu/sqlite

1# 2007 May 172#3# The author disclaims copyright to this source code.  In place of4# 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 related13# 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 cases22# should show performance improvement with respect to 3.3.17.23#24#   + When deleting rows that span overflow pages. This is faster25#     because the overflow pages no longer need to be read before26#     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 that29#     is not the first overflow page for the row. The improvement30#     in this case is because the overflow pages between the tree31#     page and the overflow page containing the value do not have32#     to be read (test cases speed3-2.X).33#34 35set testdir [file dirname $argv0]36source $testdir/tester.tcl37 38ifcapable !tclvar||!attach {39  finish_test40  return41}42 43speed_trial_init speed144 45# Set a uniform random seed46expr srand(0)47 48set ::NROW 100049 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#57set ones {zero one two three four five six seven eight nine58          ten eleven twelve thirteen fourteen fifteen sixteen seventeen59          eighteen nineteen}60set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}61proc 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 $txt82}83 84proc 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 97proc io_log {db} {98  db_enter db99  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 db102# 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 108proc speed3_reset_db {} {109  db close110  sqlite3 db test.db111  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 119forcedelete test2.db test2.db-journal120speed3_reset_db121 122# Set up a database in auto-vacuum mode and create a database schema.123#124do_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}132do_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}140do_test speed3-0.3 {141  populate_t1 db142  execsql {143    SELECT count(*) FROM main.t1;144    SELECT count(*) FROM aux.t1;145  }146} "$::NROW $::NROW"147do_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 db157speed3_reset_db158speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}159speed_trial speed3-1.normal     $::NROW row {DELETE FROM aux.t1 WHERE 1}160io_log db161 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)}166populate_t1 db167speed3_reset_db168speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}169speed_trial speed3-2.normal     $::NROW row {SELECT c FROM aux.t1}170io_log db171 172finish_test173