New speed testing tools. (CVS 5786)

FossilOrigin-Name: 2d427746d53104ca032c404f7f65c51b41b7a20e

4 files changed, 245 insertions, 98 deletions

diff --git a/manifest b/manifest
index 01ebbf8e2..c356bd0bb 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Add\sa\stest\sto\sverify\sthe\ssqlite3_column_name()\sinterface\sreturns\sthe\ncorrect\svalue\swhen\sthe\squery\sis\sof\sthe\sform:\s"SELECT\srowid,*\sFROM...".\nTicket\s#3429.\s(CVS\s5785)
-D 2008-10-09T15:56:46
+C New\sspeed\stesting\stools.\s(CVS\s5786)
+D 2008-10-09T17:57:34
 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0
 F Makefile.in 7fc26e087207e7a4a7723583dbd7997477af3b13
 F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654
@@ -627,6 +627,7 @@ F tool/memleak2.awk 9cc20c8e8f3c675efac71ea0721ee6874a1566e8
 F tool/memleak3.tcl 7707006ee908cffff210c98158788d85bb3fcdbf
 F tool/mkkeywordhash.c c219ee2b8b5b8e7011cccfa1caec62d9812e82e7
 F tool/mkopts.tcl 66ac10d240cc6e86abd37dc908d50382f84ff46e x
+F tool/mkspeedsql.tcl a1a334d288f7adfe6e996f2e712becf076745c97
 F tool/mksqlite3c.tcl f74c5aacea1668dcf4ac0b75f3e1830942b733f6
 F tool/mksqlite3internalh.tcl 7b43894e21bcb1bb39e11547ce7e38a063357e87
 F tool/omittest.tcl 4be55add3ec87f45fe041b7bfa8f16dab8105431
@@ -640,9 +641,9 @@ F tool/spaceanal.tcl b87db46ae29e3116411b1686e136b9b994d7de39
 F tool/speedtest.tcl 06c76698485ccf597b9e7dbb1ac70706eb873355
 F tool/speedtest16.c c8a9c793df96db7e4933f0852abb7a03d48f2e81
 F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff
-F tool/speedtest8.c 1dbced29de5f59ba2ebf877edcadf171540374d1
+F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224
 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e
-P 4b973ac98d027cc1097fa9845eb4a8489b633ea4
-R 45542fbff8d14207e2f09fb766bf04f6
+P e90d5a5515f7096e247e6059cf77f8089ed90b2c
+R acf63e31bfe8fb60e83299dbf2d48b42
 U drh
-Z 351753422fadf7662b5a45fc736c1469
+Z bbf583f0e0427f12e35f13f0fad6c899
diff --git a/manifest.uuid b/manifest.uuid
index 3a3b78e74..33a610f9b 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-e90d5a5515f7096e247e6059cf77f8089ed90b2c
\ No newline at end of file
+2d427746d53104ca032c404f7f65c51b41b7a20e
\ No newline at end of file
diff --git a/tool/mkspeedsql.tcl b/tool/mkspeedsql.tcl
new file mode 100644
index 000000000..04bafc04c
--- /dev/null
+++ b/tool/mkspeedsql.tcl
@@ -0,0 +1,237 @@
+# 2008 October 9
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#*************************************************************************
+# This file generates SQL text used for performance testing.
+#
+# $Id: mkspeedsql.tcl,v 1.1 2008/10/09 17:57:34 drh Exp $
+#
+
+# Set a uniform random seed
+expr srand(0)
+
+# The number_name procedure below converts its argment (an integer)
+# into a string which is the English-language name for that number.
+#
+# Example:
+#
+#     puts [number_name 123]   ->  "one hundred twenty three"
+#
+set ones {zero one two three four five six seven eight nine
+          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
+          eighteen nineteen}
+set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
+proc number_name {n} {
+  if {$n>=1000} {
+    set txt "[number_name [expr {$n/1000}]] thousand"
+    set n [expr {$n%1000}]
+  } else {
+    set txt {}
+  }
+  if {$n>=100} {
+    append txt " [lindex $::ones [expr {$n/100}]] hundred"
+    set n [expr {$n%100}]
+  }
+  if {$n>=20} {
+    append txt " [lindex $::tens [expr {$n/10}]]"
+    set n [expr {$n%10}]
+  }
+  if {$n>0} {
+    append txt " [lindex $::ones $n]"
+  }
+  set txt [string trim $txt]
+  if {$txt==""} {set txt zero}
+  return $txt
+}
+
+# Create a database schema.
+#
+puts {
+  PRAGMA page_size=1024;
+  PRAGMA cache_size=8192;
+  PRAGMA locking_mode=EXCLUSIVE;
+  CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
+  CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
+  CREATE INDEX i2a ON t2(a);
+  CREATE INDEX i2b ON t2(b);
+  SELECT name FROM sqlite_master ORDER BY 1;
+}
+
+
+# 50000 INSERTs on an unindexed table
+#
+set t1c_list {}
+puts {BEGIN;}
+for {set i 1} {$i<=50000} {incr i} {
+  set r [expr {int(rand()*500000)}]
+  set x [number_name $r]
+  lappend t1c_list $x
+  puts "INSERT INTO t1 VALUES($i,$r,'$x');"
+}
+puts {COMMIT;}
+
+# 50000 INSERTs on an indexed table
+#
+puts {BEGIN;}
+for {set i 1} {$i<=50000} {incr i} {
+  set r [expr {int(rand()*500000)}]
+  puts "INSERT INTO t2 VALUES($i,$r,'[number_name $r]');"
+}
+puts {COMMIT;}
+
+
+# 50 SELECTs on an integer comparison.  There is no index so
+# a full table scan is required.
+#
+for {set i 0} {$i<50} {incr i} {
+  set lwr [expr {$i*100}]
+  set upr [expr {($i+10)*100}]
+  puts "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
+}
+
+# 50 SELECTs on an LIKE comparison.  There is no index so a full
+# table scan is required.
+#
+for {set i 0} {$i<50} {incr i} {
+  puts "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"
+}
+
+# Create indices
+#
+puts {BEGIN;}
+puts {
+  CREATE INDEX i1a ON t1(a);
+  CREATE INDEX i1b ON t1(b);
+  CREATE INDEX i1c ON t1(c);
+}
+puts {COMMIT;}
+
+# 5000 SELECTs on an integer comparison where the integer is
+# indexed.
+#
+set sql {}
+for {set i 0} {$i<5000} {incr i} {
+  set lwr [expr {$i*100}]
+  set upr [expr {($i+10)*100}]
+  puts "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
+}
+
+# 100000 random SELECTs against rowid.
+#
+for {set i 1} {$i<=100000} {incr i} {
+  set id [expr {int(rand()*50000)+1}]
+  puts "SELECT c FROM t1 WHERE rowid=$id;"
+}
+
+# 100000 random SELECTs against a unique indexed column.
+#
+for {set i 1} {$i<=100000} {incr i} {
+  set id [expr {int(rand()*50000)+1}]
+  puts "SELECT c FROM t1 WHERE a=$id;"
+}
+
+# 50000 random SELECTs against an indexed column text column
+#
+set nt1c [llength $t1c_list]
+for {set i 0} {$i<50000} {incr i} {
+  set r [expr {int(rand()*$nt1c)}]
+  set c [lindex $t1c_list $i]
+  puts "SELECT c FROM t1 WHERE c='$c';"
+}
+
+
+# Vacuum
+puts {VACUUM;}
+
+# 5000 updates of ranges where the field being compared is indexed.
+#
+puts {BEGIN;}
+for {set i 0} {$i<5000} {incr i} {
+  set lwr [expr {$i*2}]
+  set upr [expr {($i+1)*2}]
+  puts "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
+}
+puts {COMMIT;}
+
+# 50000 single-row updates.  An index is used to find the row quickly.
+#
+puts {BEGIN;}
+for {set i 0} {$i<50000} {incr i} {
+  set r [expr {int(rand()*500000)}]
+  puts "UPDATE t1 SET b=$r WHERE a=$i;"
+}
+puts {COMMIT;}
+
+# 1 big text update that touches every row in the table.
+#
+puts {
+  UPDATE t1 SET c=a;
+}
+
+# Many individual text updates.  Each row in the table is
+# touched through an index.
+#
+puts {BEGIN;}
+for {set i 1} {$i<=50000} {incr i} {
+  set r [expr {int(rand()*500000)}]
+  puts "UPDATE t1 SET c='[number_name $r]' WHERE a=$i;"
+}
+puts {COMMIT;}
+
+# Delete all content in a table.
+#
+puts {DELETE FROM t1;}
+
+# Copy one table into another
+#
+puts {INSERT INTO t1 SELECT * FROM t2;}
+
+# Delete all content in a table, one row at a time.
+#
+puts {DELETE FROM t1 WHERE 1;}
+
+# Refill the table yet again
+#
+puts {INSERT INTO t1 SELECT * FROM t2;}
+
+# Drop the table and recreate it without its indices.
+#
+puts {BEGIN;}
+puts {
+   DROP TABLE t1;
+   CREATE TABLE t1(a INTEGER, b INTEGER, c TEXT);
+}
+puts {COMMIT;}
+
+# Refill the table yet again.  This copy should be faster because
+# there are no indices to deal with.
+#
+puts {INSERT INTO t1 SELECT * FROM t2;}
+
+# Select 20000 rows from the table at random.
+#
+puts {
+  SELECT rowid FROM t1 ORDER BY random() LIMIT 20000;
+}
+
+# Delete 20000 random rows from the table.
+#
+puts {
+  DELETE FROM t1 WHERE rowid IN
+    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000);
+}
+puts {SELECT count(*) FROM t1;}
+    
+# Delete 20000 more rows at random from the table.
+#
+puts {
+  DELETE FROM t1 WHERE rowid IN
+    (SELECT rowid FROM t1 ORDER BY random() LIMIT 20000);
+}
+puts {SELECT count(*) FROM t1;}
diff --git a/tool/speedtest8.c b/tool/speedtest8.c
index 5972947f5..051fc8981 100644
--- a/tool/speedtest8.c
+++ b/tool/speedtest8.c
@@ -90,89 +90,6 @@ static void prepareAndRun(sqlite3 *db, const char *zSql, int bQuiet){
   }
 }
 
-/***************************************************************************
-** The "overwrite" VFS is an overlay over the default VFS.  It modifies
-** the xTruncate operation on journal files so that xTruncate merely
-** writes zeros into the first 50 bytes of the file rather than truely
-** truncating the file.
-**
-** The following variables are initialized to be the virtual function
-** tables for the overwrite VFS.
-*/
-static sqlite3_vfs overwrite_vfs;
-static sqlite3_io_methods overwrite_methods;
-
-/*
-** The truncate method for journal files in the overwrite VFS.
-*/
-static int overwriteTruncate(sqlite3_file *pFile, sqlite_int64 size){
-  int rc;
-  static const char buf[50];
-  if( size ){
-    return SQLITE_IOERR;
-  }
-  rc = pFile->pMethods->xWrite(pFile, buf, sizeof(buf), 0);
-  if( rc==SQLITE_OK ){
-    rc = pFile->pMethods->xSync(pFile, SQLITE_SYNC_NORMAL);
-  }
-  return rc;
-}
-
-/*
-** The delete method for journal files in the overwrite VFS.
-*/
-static int overwriteDelete(sqlite3_file *pFile){
-  return overwriteTruncate(pFile, 0);
-}
-
-/*
-** The open method for overwrite VFS.  If the file being opened is
-** a journal file then substitute the alternative xTruncate method.
-*/
-static int overwriteOpen(
-  sqlite3_vfs *pVfs,
-  const char *zName,
-  sqlite3_file *pFile,
-  int flags,
-  int *pOutFlags
-){
-  int rc;
-  sqlite3_vfs *pRealVfs;
-  int isJournal;
-
-  isJournal = (flags & (SQLITE_OPEN_MAIN_JOURNAL|SQLITE_OPEN_TEMP_JOURNAL))!=0;
-  pRealVfs = (sqlite3_vfs*)pVfs->pAppData;
-  rc = pRealVfs->xOpen(pRealVfs, zName, pFile, flags, pOutFlags);
-  if( rc==SQLITE_OK && isJournal ){
-    if( overwrite_methods.xTruncate==0 ){
-      sqlite3_io_methods temp;
-      memcpy(&temp, pFile->pMethods, sizeof(temp));
-      temp.xTruncate = overwriteTruncate;
-      memcpy(&overwrite_methods, &temp, sizeof(temp));
-    }
-    pFile->pMethods = &overwrite_methods;
-  }
-  return rc;
-}
-
-/*
-** Overlay the overwrite VFS over top of the current default VFS
-** and make the overlay VFS the new default.
-**
-** This routine can only be evaluated once.  On second and subsequent
-** executions it becomes a no-op.
-*/
-static void registerOverwriteVfs(void){
-  sqlite3_vfs *pBase;
-  if( overwrite_vfs.iVersion ) return;
-  pBase = sqlite3_vfs_find(0);
-  memcpy(&overwrite_vfs, pBase, sizeof(overwrite_vfs));
-  overwrite_vfs.pAppData = pBase;
-  overwrite_vfs.xOpen = overwriteOpen;
-  overwrite_vfs.zName = "overwriteVfs";
-  sqlite3_vfs_register(&overwrite_vfs, 1);
-}
-
 int main(int argc, char **argv){
   sqlite3 *db;
   int rc;
@@ -199,13 +116,6 @@ int main(int argc, char **argv){
 
   while (argc>3)
   {
-    if( argc>3 && strcmp(argv[1], "-overwrite")==0 ){
-     registerOverwriteVfs();
-     argv++;
-     argc--;
-     continue;
-    }
-
 #ifdef HAVE_OSINST
     if( argc>4 && (strcmp(argv[1], "-log")==0) ){
      pVfs = sqlite3_instvfs_binarylog("oslog", 0, argv[2]);
@@ -264,7 +174,6 @@ int main(int argc, char **argv){
    fprintf(stderr, "Usage: %s [options] FILENAME SQL-SCRIPT\n"
               "Runs SQL-SCRIPT against a UTF8 database\n"
               "\toptions:\n"
-              "\t-overwrite\n"
 #ifdef HAVE_OSINST
               "\t-log <log>\n"
 #endif