AryaWu/sqlite

1# 2008 March 212#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.  The12# focus of this script is measuring executing speed.  13#14# This is a copy of speed1.test modified to user prepared statements.15#16# $Id: speed1p.test,v 1.7 2009/04/09 01:23:49 drh Exp $17#18 19catch { db close }20sqlite3_shutdown21#sqlite3_config_scratch 29000 122set old_lookaside [sqlite3_config_lookaside 2048 300]23#sqlite3_config_pagecache 1024 1100024set testdir [file dirname $argv0]25source $testdir/tester.tcl26reset_db27speed_trial_init speed128 29sqlite3_memdebug_vfs_oom_test 030 31# Set a uniform random seed32expr srand(0)33 34# The number_name procedure below converts its argment (an integer)35# into a string which is the English-language name for that number.36#37# Example:38#39#     puts [number_name 123]   ->  "one hundred twenty three"40#41set ones {zero one two three four five six seven eight nine42          ten eleven twelve thirteen fourteen fifteen sixteen seventeen43          eighteen nineteen}44set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}45proc number_name {n} {46  if {$n>=1000} {47    set txt "[number_name [expr {$n/1000}]] thousand"48    set n [expr {$n%1000}]49  } else {50    set txt {}51  }52  if {$n>=100} {53    append txt " [lindex $::ones [expr {$n/100}]] hundred"54    set n [expr {$n%100}]55  }56  if {$n>=20} {57    append txt " [lindex $::tens [expr {$n/10}]]"58    set n [expr {$n%10}]59  }60  if {$n>0} {61    append txt " [lindex $::ones $n]"62  }63  set txt [string trim $txt]64  if {$txt==""} {set txt zero}65  return $txt66}67 68# Create a database schema.69#70do_test speed1p-1.0 {71  execsql {72    PRAGMA page_size=1024;73    PRAGMA cache_size=500;74    PRAGMA locking_mode=EXCLUSIVE;75    CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);76    CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);77    CREATE INDEX i2a ON t2(a);78    CREATE INDEX i2b ON t2(b);79  }80  execsql {81    SELECT name FROM sqlite_master ORDER BY 1;82  }83} {i2a i2b t1 t2}84 85# 50000 INSERTs on an unindexed table86#87set list {}88for {set i 1} {$i<=50000} {incr i} {89  set r [expr {int(rand()*500000)}]90  set x [number_name $r]91  lappend list $i $r $x92}93set script {94  foreach {i r x} $::list {95    db eval {INSERT INTO t1 VALUES($i,$r,$x)}96  }97}98db eval BEGIN99speed_trial_tcl speed1p-insert1 50000 row $script100db eval COMMIT101 102# 50000 INSERTs on an indexed table103#104set list {}105for {set i 1} {$i<=50000} {incr i} {106  set r [expr {int(rand()*500000)}]107  set x [number_name $r]108  lappend list $i $r $x109}110set script {111  foreach {i r x} $::list {112    db eval {INSERT INTO t2 VALUES($i,$r,$x)}113  }114}115db eval BEGIN116speed_trial_tcl speed1p-insert2 50000 row $script117db eval COMMIT118 119 120 121# 50 SELECTs on an integer comparison.  There is no index so122# a full table scan is required.123#124set list {}125for {set i 0} {$i<50} {incr i} {126  set lwr [expr {$i*100}]127  set upr [expr {($i+10)*100}]128  lappend list $lwr $upr129}130set script {131  foreach {lwr upr} $::list {132    db eval  {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}133  }134}135db eval BEGIN136speed_trial_tcl speed1p-select1 [expr {50*50000}] row $script137db eval COMMIT138 139# 50 SELECTs on an LIKE comparison.  There is no index so a full140# table scan is required.141#142set list {}143for {set i 0} {$i<50} {incr i} {144  lappend list "%[number_name $i]%"145}146set script {147  foreach pattern $::list {148    db eval {SELECT count(*), avg(b) FROM t1 WHERE c LIKE $pattern}149  }150}151db eval BEGIN152speed_trial_tcl speed1p-select2 [expr {50*50000}] row $script153db eval COMMIT154 155# Create indices156#157db eval BEGIN158speed_trial speed1p-createidx 150000 row {159  CREATE INDEX i1a ON t1(a);160  CREATE INDEX i1b ON t1(b);161  CREATE INDEX i1c ON t1(c);162}163db eval COMMIT164 165# 5000 SELECTs on an integer comparison where the integer is166# indexed.167#168set list {}169for {set i 0} {$i<5000} {incr i} {170  set lwr [expr {$i*100}]171  set upr [expr {($i+10)*100}]172  lappend list $lwr $upr173}174set script {175  foreach {lwr upr} $::list {176    db eval {SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr}177  }178}179db eval BEGIN180speed_trial_tcl speed1p-select3 5000 stmt $script181db eval COMMIT182 183# 100000 random SELECTs against rowid.184#185set list {}186for {set i 1} {$i<=100000} {incr i} {187  set id [expr {int(rand()*50000)+1}]188  lappend list $id189}190set script {191  foreach id $::list {192    db eval {SELECT c FROM t1 WHERE rowid=$id}193  }194}195db eval BEGIN196speed_trial_tcl speed1p-select4 100000 row $script197db eval COMMIT198 199# 100000 random SELECTs against a unique indexed column.200#201set list {}202for {set i 1} {$i<=100000} {incr i} {203  set id [expr {int(rand()*50000)+1}]204  lappend list $id205}206set script {207  foreach id $::list {208    db eval {SELECT c FROM t1 WHERE a=$id}209  }210}211db eval BEGIN212speed_trial_tcl speed1p-select5 100000 row $script213db eval COMMIT214 215# 50000 random SELECTs against an indexed column text column216#217set list [db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000}]218set script {219  foreach c $::list {220    db eval {SELECT c FROM t1 WHERE c=$c}221  }222}223db eval BEGIN224speed_trial_tcl speed1p-select6 50000 row $script225db eval COMMIT226 227 228# Vacuum229speed_trial speed1p-vacuum 100000 row VACUUM230 231# 5000 updates of ranges where the field being compared is indexed.232#233set list {}234for {set i 0} {$i<5000} {incr i} {235  set lwr [expr {$i*2}]236  set upr [expr {($i+1)*2}]237  lappend list $lwr $upr238}239set script {240  foreach {lwr upr} $::list {241    db eval {UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr}242  }243}244db eval BEGIN245speed_trial_tcl speed1p-update1 5000 stmt $script246db eval COMMIT247 248# 50000 single-row updates.  An index is used to find the row quickly.249#250set list {}251for {set i 0} {$i<50000} {incr i} {252  set r [expr {int(rand()*500000)}]253  lappend list $i $r254}255set script {256  foreach {i r} $::list {257    db eval {UPDATE t1 SET b=$r WHERE a=$i}258  }259}260db eval BEGIN261speed_trial_tcl speed1p-update2 50000 row $script262db eval COMMIT263 264# 1 big text update that touches every row in the table.265#266speed_trial speed1p-update3 50000 row {267  UPDATE t1 SET c=a;268}269 270# Many individual text updates.  Each row in the table is271# touched through an index.272#273set list {}274for {set i 1} {$i<=50000} {incr i} {275  set r [expr {int(rand()*500000)}]276  lappend list $i [number_name $r]277}278set script {279  foreach {i x} $::list {280    db eval {UPDATE t1 SET c=$x WHERE a=$i}281  }282}283db eval BEGIN284speed_trial_tcl speed1p-update4 50000 row $script285db eval COMMIT286 287# Delete all content in a table.288#289speed_trial speed1p-delete1 50000 row {DELETE FROM t1}290 291# Copy one table into another292#293speed_trial speed1p-copy1 50000 row {INSERT INTO t1 SELECT * FROM t2}294 295# Delete all content in a table, one row at a time.296#297speed_trial speed1p-delete2 50000 row {DELETE FROM t1 WHERE 1}298 299# Refill the table yet again300#301speed_trial speed1p-copy2 50000 row {INSERT INTO t1 SELECT * FROM t2}302 303# Drop the table and recreate it without its indices.304#305db eval BEGIN306speed_trial speed1p-drop1 50000 row {307   DROP TABLE t1;308   CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);309}310db eval COMMIT311 312# Refill the table yet again.  This copy should be faster because313# there are no indices to deal with.314#315speed_trial speed1p-copy3 50000 row {INSERT INTO t1 SELECT * FROM t2}316 317# Select 20000 rows from the table at random.318#319speed_trial speed1p-random1 50000 row {320  SELECT rowid FROM t1 ORDER BY random() LIMIT 20000321}322 323# Delete 20000 random rows from the table.324#325speed_trial speed1p-random-del1 20000 row {326  DELETE FROM t1 WHERE rowid IN327    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)328}329do_test speed1p-1.1 {330  db one {SELECT count(*) FROM t1}331} 30000332    333# Delete 20000 more rows at random from the table.334#335speed_trial speed1p-random-del2 20000 row {336  DELETE FROM t1 WHERE rowid IN337    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000)338}339do_test speed1p-1.2 {340  db one {SELECT count(*) FROM t1}341} 10000342speed_trial_summary speed1343 344db close345sqlite3_shutdown346eval sqlite3_config_lookaside $old_lookaside347sqlite3_initialize348autoinstall_test_functions349finish_test350