test/speedtest1.c

   1 /*
   2 ** A program for performance testing.
   3 **
   4 ** The available command-line options are described below:
   5 */
   6 static const char zHelp[] =
   7   "Usage: %s [--options] DATABASE\n"
   8   "Options:\n"
   9   "  --autovacuum        Enable AUTOVACUUM mode\n"
  10   "  --cachesize N       Set the cache size to N\n"
  11   "  --exclusive         Enable locking_mode=EXCLUSIVE\n"
  12   "  --explain           Like --sqlonly but with added EXPLAIN keywords\n"
  13   "  --heap SZ MIN       Memory allocator uses SZ bytes & min allocation MIN\n"
  14   "  --incrvacuum        Enable incremenatal vacuum mode\n"
  15   "  --journal M         Set the journal_mode to M\n"
  16   "  --key KEY           Set the encryption key to KEY\n"
  17   "  --lookaside N SZ    Configure lookaside for N slots of SZ bytes each\n"
  18   "  --mmap SZ           MMAP the first SZ bytes of the database file\n"
  19   "  --multithread       Set multithreaded mode\n"
  20   "  --nomemstat         Disable memory statistics\n"
  21   "  --nosync            Set PRAGMA synchronous=OFF\n"
  22   "  --notnull           Add NOT NULL constraints to table columns\n"
  23   "  --pagesize N        Set the page size to N\n"
  24   "  --pcache N SZ       Configure N pages of pagecache each of size SZ bytes\n"
  25   "  --primarykey        Use PRIMARY KEY instead of UNIQUE where appropriate\n"
  26   "  --repeat N          Repeat each SELECT N times (default: 1)\n"
  27   "  --reprepare         Reprepare each statement upon every invocation\n"
  28   "  --serialized        Set serialized threading mode\n"
  29   "  --singlethread      Set single-threaded mode - disables all mutexing\n"
  30   "  --sqlonly           No-op.  Only show the SQL that would have been run.\n"
  31   "  --shrink-memory     Invoke sqlite3_db_release_memory() frequently.\n"
  32   "  --size N            Relative test size.  Default=100\n"
  33   "  --stats             Show statistics at the end\n"
  34   "  --temp N            N from 0 to 9.  0: no temp table. 9: all temp tables\n"
  35   "  --testset T         Run test-set T (main, cte, rtree, orm, fp, debug)\n"
  36   "  --trace             Turn on SQL tracing\n"
  37   "  --threads N         Use up to N threads for sorting\n"
  38   "  --utf16be           Set text encoding to UTF-16BE\n"
  39   "  --utf16le           Set text encoding to UTF-16LE\n"
  40   "  --verify            Run additional verification steps.\n"
  41   "  --without-rowid     Use WITHOUT ROWID where appropriate\n"
  42 ;
  43
  44
  45 #include "sqlite3.h"
  46 #include <assert.h>
  47 #include <stdio.h>
  48 #include <stdlib.h>
  49 #include <stdarg.h>
  50 #include <string.h>
  51 #include <ctype.h>
  52 #ifndef _WIN32
  53 # include <unistd.h>
  54 #else
  55 # include <io.h>
  56 #endif
  57 #define ISSPACE(X) isspace((unsigned char)(X))
  58 #define ISDIGIT(X) isdigit((unsigned char)(X))
  59
  60 #if SQLITE_VERSION_NUMBER<3005000
  61 # define sqlite3_int64 sqlite_int64
  62 #endif
  63
  64 /* All global state is held in this structure */
  65 static struct Global {
  66   sqlite3 *db;               /* The open database connection */
  67   sqlite3_stmt *pStmt;       /* Current SQL statement */
  68   sqlite3_int64 iStart;      /* Start-time for the current test */
  69   sqlite3_int64 iTotal;      /* Total time */
  70   int bWithoutRowid;         /* True for --without-rowid */
  71   int bReprepare;            /* True to reprepare the SQL on each rerun */
  72   int bSqlOnly;              /* True to print the SQL once only */
  73   int bExplain;              /* Print SQL with EXPLAIN prefix */
  74   int bVerify;               /* Try to verify that results are correct */
  75   int bMemShrink;            /* Call sqlite3_db_release_memory() often */
  76   int eTemp;                 /* 0: no TEMP.  9: always TEMP. */
  77   int szTest;                /* Scale factor for test iterations */
  78   int nRepeat;               /* Repeat selects this many times */
  79   const char *zWR;           /* Might be WITHOUT ROWID */
  80   const char *zNN;           /* Might be NOT NULL */
  81   const char *zPK;           /* Might be UNIQUE or PRIMARY KEY */
  82   unsigned int x, y;         /* Pseudo-random number generator state */
  83   int nResult;               /* Size of the current result */
  84   char zResult[3000];        /* Text of the current result */
  85 } g;
  86
  87 /* Return " TEMP" or "", as appropriate for creating a table.
  88 */
  89 static const char *isTemp(int N){
  90   return g.eTemp>=N ? " TEMP" : "";
  91 }
  92
  93
  94 /* Print an error message and exit */
  95 static void fatal_error(const char *zMsg, ...){
  96   va_list ap;
  97   va_start(ap, zMsg);
  98   vfprintf(stderr, zMsg, ap);
  99   va_end(ap);
 100   exit(1);
 101 }
 102
 103 /*
 104 ** Return the value of a hexadecimal digit.  Return -1 if the input
 105 ** is not a hex digit.
 106 */
 107 static int hexDigitValue(char c){
 108   if( c>='0' && c<='9' ) return c - '0';
 109   if( c>='a' && c<='f' ) return c - 'a' + 10;
 110   if( c>='A' && c<='F' ) return c - 'A' + 10;
 111   return -1;
 112 }
 113
 114 /* Provide an alternative to sqlite3_stricmp() in older versions of
 115 ** SQLite */
 116 #if SQLITE_VERSION_NUMBER<3007011
 117 # define sqlite3_stricmp strcmp
 118 #endif
 119
 120 /*
 121 ** Interpret zArg as an integer value, possibly with suffixes.
 122 */
 123 static int integerValue(const char *zArg){
 124   sqlite3_int64 v = 0;
 125   static const struct { char *zSuffix; int iMult; } aMult[] = {
 126     { "KiB", 1024 },
 127     { "MiB", 1024*1024 },
 128     { "GiB", 1024*1024*1024 },
 129     { "KB",  1000 },
 130     { "MB",  1000000 },
 131     { "GB",  1000000000 },
 132     { "K",   1000 },
 133     { "M",   1000000 },
 134     { "G",   1000000000 },
 135   };
 136   int i;
 137   int isNeg = 0;
 138   if( zArg[0]=='-' ){
 139     isNeg = 1;
 140     zArg++;
 141   }else if( zArg[0]=='+' ){
 142     zArg++;
 143   }
 144   if( zArg[0]=='0' && zArg[1]=='x' ){
 145     int x;
 146     zArg += 2;
 147     while( (x = hexDigitValue(zArg[0]))>=0 ){
 148       v = (v<<4) + x;
 149       zArg++;
 150     }
 151   }else{
 152     while( isdigit(zArg[0]) ){
 153       v = v*10 + zArg[0] - '0';
 154       zArg++;
 155     }
 156   }
 157   for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){
 158     if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){
 159       v *= aMult[i].iMult;
 160       break;
 161     }
 162   }
 163   if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");
 164   return (int)(isNeg? -v : v);
 165 }
 166
 167 /* Return the current wall-clock time, in milliseconds */
 168 sqlite3_int64 speedtest1_timestamp(void){
 169 #if SQLITE_VERSION_NUMBER<3005000
 170   return 0;
 171 #else
 172   static sqlite3_vfs *clockVfs = 0;
 173   sqlite3_int64 t;
 174   if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
 175 #if SQLITE_VERSION_NUMBER>=3007000
 176   if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){
 177     clockVfs->xCurrentTimeInt64(clockVfs, &t);
 178   }else
 179 #endif
 180   {
 181     double r;
 182     clockVfs->xCurrentTime(clockVfs, &r);
 183     t = (sqlite3_int64)(r*86400000.0);
 184   }
 185   return t;
 186 #endif
 187 }
 188
 189 /* Return a pseudo-random unsigned integer */
 190 unsigned int speedtest1_random(void){
 191   g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001);
 192   g.y = g.y*1103515245 + 12345;
 193   return g.x ^ g.y;
 194 }
 195
 196 /* Map the value in within the range of 1...limit into another
 197 ** number in a way that is chatic and invertable.
 198 */
 199 unsigned swizzle(unsigned in, unsigned limit){
 200   unsigned out = 0;
 201   while( limit ){
 202     out = (out<<1) | (in&1);
 203     in >>= 1;
 204     limit >>= 1;
 205   }
 206   return out;
 207 }
 208
 209 /* Round up a number so that it is a power of two minus one
 210 */
 211 unsigned roundup_allones(unsigned limit){
 212   unsigned m = 1;
 213   while( m<limit ) m = (m<<1)+1;
 214   return m;
 215 }
 216
 217 /* The speedtest1_numbername procedure below converts its argment (an integer)
 218 ** into a string which is the English-language name for that number.
 219 ** The returned string should be freed with sqlite3_free().
 220 **
 221 ** Example:
 222 **
 223 **     speedtest1_numbername(123)   ->  "one hundred twenty three"
 224 */
 225 int speedtest1_numbername(unsigned int n, char *zOut, int nOut){
 226   static const char *ones[] = {  "zero", "one", "two", "three", "four", "five",
 227                   "six", "seven", "eight", "nine", "ten", "eleven", "twelve",
 228                   "thirteen", "fourteen", "fifteen", "sixteen", "seventeen",
 229                   "eighteen", "nineteen" };
 230   static const char *tens[] = { "", "ten", "twenty", "thirty", "forty",
 231                  "fifty", "sixty", "seventy", "eighty", "ninety" };
 232   int i = 0;
 233
 234   if( n>=1000000000 ){
 235     i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i);
 236     sqlite3_snprintf(nOut-i, zOut+i, " billion");
 237     i += (int)strlen(zOut+i);
 238     n = n % 1000000000;
 239   }
 240   if( n>=1000000 ){
 241     if( i && i<nOut-1 ) zOut[i++] = ' ';
 242     i += speedtest1_numbername(n/1000000, zOut+i, nOut-i);
 243     sqlite3_snprintf(nOut-i, zOut+i, " million");
 244     i += (int)strlen(zOut+i);
 245     n = n % 1000000;
 246   }
 247   if( n>=1000 ){
 248     if( i && i<nOut-1 ) zOut[i++] = ' ';
 249     i += speedtest1_numbername(n/1000, zOut+i, nOut-i);
 250     sqlite3_snprintf(nOut-i, zOut+i, " thousand");
 251     i += (int)strlen(zOut+i);
 252     n = n % 1000;
 253   }
 254   if( n>=100 ){
 255     if( i && i<nOut-1 ) zOut[i++] = ' ';
 256     sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]);
 257     i += (int)strlen(zOut+i);
 258     n = n % 100;
 259   }
 260   if( n>=20 ){
 261     if( i && i<nOut-1 ) zOut[i++] = ' ';
 262     sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]);
 263     i += (int)strlen(zOut+i);
 264     n = n % 10;
 265   }
 266   if( n>0 ){
 267     if( i && i<nOut-1 ) zOut[i++] = ' ';
 268     sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]);
 269     i += (int)strlen(zOut+i);
 270   }
 271   if( i==0 ){
 272     sqlite3_snprintf(nOut-i, zOut+i, "zero");
 273     i += (int)strlen(zOut+i);
 274   }
 275   return i;
 276 }
 277
 278
 279 /* Start a new test case */
 280 #define NAMEWIDTH 60
 281 static const char zDots[] =
 282   ".......................................................................";
 283 void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){
 284   int n = (int)strlen(zTestName);
 285   char *zName;
 286   va_list ap;
 287   va_start(ap, zTestName);
 288   zName = sqlite3_vmprintf(zTestName, ap);
 289   va_end(ap);
 290   n = (int)strlen(zName);
 291   if( n>NAMEWIDTH ){
 292     zName[NAMEWIDTH] = 0;
 293     n = NAMEWIDTH;
 294   }
 295   if( g.bSqlOnly ){
 296     printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots);
 297   }else{
 298     printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);
 299     fflush(stdout);
 300   }
 301   sqlite3_free(zName);
 302   g.nResult = 0;
 303   g.iStart = speedtest1_timestamp();
 304   g.x = 0xad131d0b;
 305   g.y = 0x44f9eac8;
 306 }
 307
 308 /* Complete a test case */
 309 void speedtest1_end_test(void){
 310   sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;
 311   if( !g.bSqlOnly ){
 312     g.iTotal += iElapseTime;
 313     printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000));
 314   }
 315   if( g.pStmt ){
 316     sqlite3_finalize(g.pStmt);
 317     g.pStmt = 0;
 318   }
 319 }
 320
 321 /* Report end of testing */
 322 void speedtest1_final(void){
 323   if( !g.bSqlOnly ){
 324     printf("       TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
 325            (int)(g.iTotal/1000), (int)(g.iTotal%1000));
 326   }
 327 }
 328
 329 /* Print an SQL statement to standard output */
 330 static void printSql(const char *zSql){
 331   int n = (int)strlen(zSql);
 332   while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ){ n--; }
 333   if( g.bExplain ) printf("EXPLAIN ");
 334   printf("%.*s;\n", n, zSql);
 335   if( g.bExplain
 336 #if SQLITE_VERSION_NUMBER>=3007017
 337    && ( sqlite3_strglob("CREATE *", zSql)==0
 338      || sqlite3_strglob("DROP *", zSql)==0
 339      || sqlite3_strglob("ALTER *", zSql)==0
 340       )
 341 #endif
 342   ){
 343     printf("%.*s;\n", n, zSql);
 344   }
 345 }
 346
 347 /* Shrink memory used, if appropriate and if the SQLite version is capable
 348 ** of doing so.
 349 */
 350 void speedtest1_shrink_memory(void){
 351 #if SQLITE_VERSION_NUMBER>=3007010
 352   if( g.bMemShrink ) sqlite3_db_release_memory(g.db);
 353 #endif
 354 }
 355
 356 /* Run SQL */
 357 void speedtest1_exec(const char *zFormat, ...){
 358   va_list ap;
 359   char *zSql;
 360   va_start(ap, zFormat);
 361   zSql = sqlite3_vmprintf(zFormat, ap);
 362   va_end(ap);
 363   if( g.bSqlOnly ){
 364     printSql(zSql);
 365   }else{
 366     char *zErrMsg = 0;
 367     int rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
 368     if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
 369     if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
 370   }
 371   sqlite3_free(zSql);
 372   speedtest1_shrink_memory();
 373 }
 374
 375 /* Prepare an SQL statement */
 376 void speedtest1_prepare(const char *zFormat, ...){
 377   va_list ap;
 378   char *zSql;
 379   va_start(ap, zFormat);
 380   zSql = sqlite3_vmprintf(zFormat, ap);
 381   va_end(ap);
 382   if( g.bSqlOnly ){
 383     printSql(zSql);
 384   }else{
 385     int rc;
 386     if( g.pStmt ) sqlite3_finalize(g.pStmt);
 387     rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
 388     if( rc ){
 389       fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
 390     }
 391   }
 392   sqlite3_free(zSql);
 393 }
 394
 395 /* Run an SQL statement previously prepared */
 396 void speedtest1_run(void){
 397   int i, n, len;
 398   if( g.bSqlOnly ) return;
 399   assert( g.pStmt );
 400   g.nResult = 0;
 401   while( sqlite3_step(g.pStmt)==SQLITE_ROW ){
 402     n = sqlite3_column_count(g.pStmt);
 403     for(i=0; i<n; i++){
 404       const char *z = (const char*)sqlite3_column_text(g.pStmt, i);
 405       if( z==0 ) z = "nil";
 406       len = (int)strlen(z);
 407       if( g.nResult+len<sizeof(g.zResult)-2 ){
 408         if( g.nResult>0 ) g.zResult[g.nResult++] = ' ';
 409         memcpy(g.zResult + g.nResult, z, len+1);
 410         g.nResult += len;
 411       }
 412     }
 413   }
 414 #if SQLITE_VERSION_NUMBER>=3006001
 415   if( g.bReprepare ){
 416     sqlite3_stmt *pNew;
 417     sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0);
 418     sqlite3_finalize(g.pStmt);
 419     g.pStmt = pNew;
 420   }else
 421 #endif
 422   {
 423     sqlite3_reset(g.pStmt);
 424   }
 425   speedtest1_shrink_memory();
 426 }
 427
 428 #ifndef SQLITE_OMIT_DEPRECATED
 429 /* The sqlite3_trace() callback function */
 430 static void traceCallback(void *NotUsed, const char *zSql){
 431   int n = (int)strlen(zSql);
 432   while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ) n--;
 433   fprintf(stderr,"%.*s;\n", n, zSql);
 434 }
 435 #endif /* SQLITE_OMIT_DEPRECATED */
 436
 437 /* Substitute random() function that gives the same random
 438 ** sequence on each run, for repeatability. */
 439 static void randomFunc(
 440   sqlite3_context *context,
 441   int NotUsed,
 442   sqlite3_value **NotUsed2
 443 ){
 444   sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random());
 445 }
 446
 447 /* Estimate the square root of an integer */
 448 static int est_square_root(int x){
 449   int y0 = x/2;
 450   int y1;
 451   int n;
 452   for(n=0; y0>0 && n<10; n++){
 453     y1 = (y0 + x/y0)/2;
 454     if( y1==y0 ) break;
 455     y0 = y1;
 456   }
 457   return y0;
 458 }
 459
 460
 461 #if SQLITE_VERSION_NUMBER<3005004
 462 /*
 463 ** An implementation of group_concat().  Used only when testing older
 464 ** versions of SQLite that lack the built-in group_concat().
 465 */
 466 struct groupConcat {
 467   char *z;
 468   int nAlloc;
 469   int nUsed;
 470 };
 471 static void groupAppend(struct groupConcat *p, const char *z, int n){
 472   if( p->nUsed+n >= p->nAlloc ){
 473     int n2 = (p->nAlloc+n+1)*2;
 474     char *z2 = sqlite3_realloc(p->z, n2);
 475     if( z2==0 ) return;
 476     p->z = z2;
 477     p->nAlloc = n2;
 478   }
 479   memcpy(p->z+p->nUsed, z, n);
 480   p->nUsed += n;
 481 }
 482 static void groupStep(
 483   sqlite3_context *context,
 484   int argc,
 485   sqlite3_value **argv
 486 ){
 487   const char *zVal;
 488   struct groupConcat *p;
 489   const char *zSep;
 490   int nVal, nSep;
 491   assert( argc==1 || argc==2 );
 492   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
 493   p= (struct groupConcat*)sqlite3_aggregate_context(context, sizeof(*p));
 494
 495   if( p ){
 496     int firstTerm = p->nUsed==0;
 497     if( !firstTerm ){
 498       if( argc==2 ){
 499         zSep = (char*)sqlite3_value_text(argv[1]);
 500         nSep = sqlite3_value_bytes(argv[1]);
 501       }else{
 502         zSep = ",";
 503         nSep = 1;
 504       }
 505       if( nSep ) groupAppend(p, zSep, nSep);
 506     }
 507     zVal = (char*)sqlite3_value_text(argv[0]);
 508     nVal = sqlite3_value_bytes(argv[0]);
 509     if( zVal ) groupAppend(p, zVal, nVal);
 510   }
 511 }
 512 static void groupFinal(sqlite3_context *context){
 513   struct groupConcat *p;
 514   p = sqlite3_aggregate_context(context, 0);
 515   if( p && p->z ){
 516     p->z[p->nUsed] = 0;
 517     sqlite3_result_text(context, p->z, p->nUsed, sqlite3_free);
 518   }
 519 }
 520 #endif
 521
 522 /*
 523 ** The main and default testset
 524 */
 525 void testset_main(void){
 526   int i;                        /* Loop counter */
 527   int n;                        /* iteration count */
 528   int sz;                       /* Size of the tables */
 529   int maxb;                     /* Maximum swizzled value */
 530   unsigned x1 = 0, x2 = 0;      /* Parameters */
 531   int len = 0;                  /* Length of the zNum[] string */
 532   char zNum[2000];              /* A number name */
 533
 534   sz = n = g.szTest*500;
 535   zNum[0] = 0;
 536   maxb = roundup_allones(sz);
 537   speedtest1_begin_test(100, "%d INSERTs into table with no index", n);
 538   speedtest1_exec("BEGIN");
 539   speedtest1_exec("CREATE%s TABLE t1(a INTEGER %s, b INTEGER %s, c TEXT %s);",
 540                   isTemp(9), g.zNN, g.zNN, g.zNN);
 541   speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); --  %d times", n);
 542   for(i=1; i<=n; i++){
 543     x1 = swizzle(i,maxb);
 544     speedtest1_numbername(x1, zNum, sizeof(zNum));
 545     sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
 546     sqlite3_bind_int(g.pStmt, 2, i);
 547     sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
 548     speedtest1_run();
 549   }
 550   speedtest1_exec("COMMIT");
 551   speedtest1_end_test();
 552
 553
 554   n = sz;
 555   speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n);
 556   speedtest1_exec("BEGIN");
 557   speedtest1_exec(
 558      "CREATE%s TABLE t2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
 559      isTemp(5), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
 560   speedtest1_prepare("INSERT INTO t2 VALUES(?1,?2,?3); -- %d times", n);
 561   for(i=1; i<=n; i++){
 562     x1 = swizzle(i,maxb);
 563     speedtest1_numbername(x1, zNum, sizeof(zNum));
 564     sqlite3_bind_int(g.pStmt, 1, i);
 565     sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1);
 566     sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
 567     speedtest1_run();
 568   }
 569   speedtest1_exec("COMMIT");
 570   speedtest1_end_test();
 571
 572
 573   n = sz;
 574   speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n);
 575   speedtest1_exec("BEGIN");
 576   speedtest1_exec(
 577       "CREATE%s TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
 578       isTemp(3), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
 579   speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n);
 580   for(i=1; i<=n; i++){
 581     x1 = swizzle(i,maxb);
 582     speedtest1_numbername(x1, zNum, sizeof(zNum));
 583     sqlite3_bind_int(g.pStmt, 2, i);
 584     sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
 585     sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
 586     speedtest1_run();
 587   }
 588   speedtest1_exec("COMMIT");
 589   speedtest1_end_test();
 590
 591 #if SQLITE_VERSION_NUMBER<3005004
 592   sqlite3_create_function(g.db, "group_concat", 1, SQLITE_UTF8, 0,
 593                           0, groupStep, groupFinal);
 594 #endif
 595
 596   n = 25;
 597   speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
 598   speedtest1_exec("BEGIN");
 599   speedtest1_prepare(
 600     "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM t1\n"
 601     " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
 602   );
 603   for(i=1; i<=n; i++){
 604     if( (i-1)%g.nRepeat==0 ){
 605       x1 = speedtest1_random()%maxb;
 606       x2 = speedtest1_random()%10 + sz/5000 + x1;
 607     }
 608     sqlite3_bind_int(g.pStmt, 1, x1);
 609     sqlite3_bind_int(g.pStmt, 2, x2);
 610     speedtest1_run();
 611   }
 612   speedtest1_exec("COMMIT");
 613   speedtest1_end_test();
 614
 615
 616   n = 10;
 617   speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
 618   speedtest1_exec("BEGIN");
 619   speedtest1_prepare(
 620     "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM t1\n"
 621     " WHERE c LIKE ?1; -- %d times", n
 622   );
 623   for(i=1; i<=n; i++){
 624     if( (i-1)%g.nRepeat==0 ){
 625       x1 = speedtest1_random()%maxb;
 626       zNum[0] = '%';
 627       len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
 628       zNum[len] = '%';
 629       zNum[len+1] = 0;
 630     }
 631     sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
 632     speedtest1_run();
 633   }
 634   speedtest1_exec("COMMIT");
 635   speedtest1_end_test();
 636
 637
 638   n = 10;
 639   speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);
 640   speedtest1_exec("BEGIN");
 641   speedtest1_prepare(
 642     "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n"
 643     " ORDER BY a; -- %d times", n
 644   );
 645   for(i=1; i<=n; i++){
 646     if( (i-1)%g.nRepeat==0 ){
 647       x1 = speedtest1_random()%maxb;
 648       zNum[0] = '%';
 649       len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
 650       zNum[len] = '%';
 651       zNum[len+1] = 0;
 652     }
 653     sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
 654     speedtest1_run();
 655   }
 656   speedtest1_exec("COMMIT");
 657   speedtest1_end_test();
 658
 659   n = 10; /* g.szTest/5; */
 660   speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);
 661   speedtest1_exec("BEGIN");
 662   speedtest1_prepare(
 663     "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n"
 664     " ORDER BY a LIMIT 10; -- %d times", n
 665   );
 666   for(i=1; i<=n; i++){
 667     if( (i-1)%g.nRepeat==0 ){
 668       x1 = speedtest1_random()%maxb;
 669       zNum[0] = '%';
 670       len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
 671       zNum[len] = '%';
 672       zNum[len+1] = 0;
 673     }
 674     sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
 675     speedtest1_run();
 676   }
 677   speedtest1_exec("COMMIT");
 678   speedtest1_end_test();
 679
 680
 681   speedtest1_begin_test(150, "CREATE INDEX five times");
 682   speedtest1_exec("BEGIN;");
 683   speedtest1_exec("CREATE UNIQUE INDEX t1b ON t1(b);");
 684   speedtest1_exec("CREATE INDEX t1c ON t1(c);");
 685   speedtest1_exec("CREATE UNIQUE INDEX t2b ON t2(b);");
 686   speedtest1_exec("CREATE INDEX t2c ON t2(c DESC);");
 687   speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
 688   speedtest1_exec("COMMIT;");
 689   speedtest1_end_test();
 690
 691
 692   n = sz/5;
 693   speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
 694   speedtest1_exec("BEGIN");
 695   speedtest1_prepare(
 696     "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM t1\n"
 697     " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
 698   );
 699   for(i=1; i<=n; i++){
 700     if( (i-1)%g.nRepeat==0 ){
 701       x1 = speedtest1_random()%maxb;
 702       x2 = speedtest1_random()%10 + sz/5000 + x1;
 703     }
 704     sqlite3_bind_int(g.pStmt, 1, x1);
 705     sqlite3_bind_int(g.pStmt, 2, x2);
 706     speedtest1_run();
 707   }
 708   speedtest1_exec("COMMIT");
 709   speedtest1_end_test();
 710
 711
 712   n = sz/5;
 713   speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
 714   speedtest1_exec("BEGIN");
 715   speedtest1_prepare(
 716     "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM t2\n"
 717     " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
 718   );
 719   for(i=1; i<=n; i++){
 720     if( (i-1)%g.nRepeat==0 ){
 721       x1 = speedtest1_random()%maxb;
 722       x2 = speedtest1_random()%10 + sz/5000 + x1;
 723     }
 724     sqlite3_bind_int(g.pStmt, 1, x1);
 725     sqlite3_bind_int(g.pStmt, 2, x2);
 726     speedtest1_run();
 727   }
 728   speedtest1_exec("COMMIT");
 729   speedtest1_end_test();
 730
 731
 732   n = sz/5;
 733   speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
 734   speedtest1_exec("BEGIN");
 735   speedtest1_prepare(
 736     "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM t1\n"
 737     " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
 738   );
 739   for(i=1; i<=n; i++){
 740     if( (i-1)%g.nRepeat==0 ){
 741       x1 = swizzle(i, maxb);
 742       len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
 743     }
 744     sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
 745     speedtest1_run();
 746   }
 747   speedtest1_exec("COMMIT");
 748   speedtest1_end_test();
 749
 750   n = sz;
 751   speedtest1_begin_test(180, "%d INSERTS with three indexes", n);
 752   speedtest1_exec("BEGIN");
 753   speedtest1_exec(
 754     "CREATE%s TABLE t4(\n"
 755     "  a INTEGER %s %s,\n"
 756     "  b INTEGER %s,\n"
 757     "  c TEXT %s\n"
 758     ") %s",
 759     isTemp(1), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
 760   speedtest1_exec("CREATE INDEX t4b ON t4(b)");
 761   speedtest1_exec("CREATE INDEX t4c ON t4(c)");
 762   speedtest1_exec("INSERT INTO t4 SELECT * FROM t1");
 763   speedtest1_exec("COMMIT");
 764   speedtest1_end_test();
 765
 766   n = sz;
 767   speedtest1_begin_test(190, "DELETE and REFILL one table", n);
 768   speedtest1_exec("DELETE FROM t2;");
 769   speedtest1_exec("INSERT INTO t2 SELECT * FROM t1;");
 770   speedtest1_end_test();
 771
 772
 773   speedtest1_begin_test(200, "VACUUM");
 774   speedtest1_exec("VACUUM");
 775   speedtest1_end_test();
 776
 777
 778   speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query");
 779   speedtest1_exec("ALTER TABLE t2 ADD COLUMN d DEFAULT 123");
 780   speedtest1_exec("SELECT sum(d) FROM t2");
 781   speedtest1_end_test();
 782
 783
 784   n = sz/5;
 785   speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n);
 786   speedtest1_exec("BEGIN");
 787   speedtest1_prepare(
 788     "UPDATE t2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
 789   );
 790   for(i=1; i<=n; i++){
 791     x1 = speedtest1_random()%maxb;
 792     x2 = speedtest1_random()%10 + sz/5000 + x1;
 793     sqlite3_bind_int(g.pStmt, 1, x1);
 794     sqlite3_bind_int(g.pStmt, 2, x2);
 795     speedtest1_run();
 796   }
 797   speedtest1_exec("COMMIT");
 798   speedtest1_end_test();
 799
 800
 801   n = sz;
 802   speedtest1_begin_test(240, "%d UPDATES of individual rows", n);
 803   speedtest1_exec("BEGIN");
 804   speedtest1_prepare(
 805     "UPDATE t2 SET d=b*3 WHERE a=?1; -- %d times", n
 806   );
 807   for(i=1; i<=n; i++){
 808     x1 = speedtest1_random()%sz + 1;
 809     sqlite3_bind_int(g.pStmt, 1, x1);
 810     speedtest1_run();
 811   }
 812   speedtest1_exec("COMMIT");
 813   speedtest1_end_test();
 814
 815   speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz);
 816   speedtest1_exec("UPDATE t2 SET d=b*4");
 817   speedtest1_end_test();
 818
 819
 820   speedtest1_begin_test(260, "Query added column after filling");
 821   speedtest1_exec("SELECT sum(d) FROM t2");
 822   speedtest1_end_test();
 823
 824
 825
 826   n = sz/5;
 827   speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n);
 828   speedtest1_exec("BEGIN");
 829   speedtest1_prepare(
 830     "DELETE FROM t2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
 831   );
 832   for(i=1; i<=n; i++){
 833     x1 = speedtest1_random()%maxb + 1;
 834     x2 = speedtest1_random()%10 + sz/5000 + x1;
 835     sqlite3_bind_int(g.pStmt, 1, x1);
 836     sqlite3_bind_int(g.pStmt, 2, x2);
 837     speedtest1_run();
 838   }
 839   speedtest1_exec("COMMIT");
 840   speedtest1_end_test();
 841
 842
 843   n = sz;
 844   speedtest1_begin_test(280, "%d DELETEs of individual rows", n);
 845   speedtest1_exec("BEGIN");
 846   speedtest1_prepare(
 847     "DELETE FROM t3 WHERE a=?1; -- %d times", n
 848   );
 849   for(i=1; i<=n; i++){
 850     x1 = speedtest1_random()%sz + 1;
 851     sqlite3_bind_int(g.pStmt, 1, x1);
 852     speedtest1_run();
 853   }
 854   speedtest1_exec("COMMIT");
 855   speedtest1_end_test();
 856
 857
 858   speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
 859   speedtest1_exec("REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1");
 860   speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1");
 861   speedtest1_end_test();
 862
 863   speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
 864   speedtest1_exec("DELETE FROM t2;");
 865   speedtest1_exec("INSERT INTO t2(a,b,c)\n"
 866                   " SELECT a,b,c FROM t1  WHERE (b&1)==(a&1);");
 867   speedtest1_exec("INSERT INTO t2(a,b,c)\n"
 868                   " SELECT a,b,c FROM t1  WHERE (b&1)<>(a&1);");
 869   speedtest1_end_test();
 870
 871
 872   n = sz/5;
 873   speedtest1_begin_test(310, "%d four-ways joins", n);
 874   speedtest1_exec("BEGIN");
 875   speedtest1_prepare(
 876     "SELECT t1.c FROM t1, t2, t3, t4\n"
 877     " WHERE t4.a BETWEEN ?1 AND ?2\n"
 878     "   AND t3.a=t4.b\n"
 879     "   AND t2.a=t3.b\n"
 880     "   AND t1.c=t2.c"
 881   );
 882   for(i=1; i<=n; i++){
 883     x1 = speedtest1_random()%sz + 1;
 884     x2 = speedtest1_random()%10 + x1 + 4;
 885     sqlite3_bind_int(g.pStmt, 1, x1);
 886     sqlite3_bind_int(g.pStmt, 2, x2);
 887     speedtest1_run();
 888   }
 889   speedtest1_exec("COMMIT");
 890   speedtest1_end_test();
 891
 892   speedtest1_begin_test(320, "subquery in result set", n);
 893   speedtest1_prepare(
 894     "SELECT sum(a), max(c),\n"
 895     "       avg((SELECT a FROM t2 WHERE 5+t2.b=t1.b) AND rowid<?1), max(c)\n"
 896     " FROM t1 WHERE rowid<?1;"
 897   );
 898   sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
 899   speedtest1_run();
 900   speedtest1_end_test();
 901
 902   sz = n = g.szTest*700;
 903   zNum[0] = 0;
 904   maxb = roundup_allones(sz/3);
 905   speedtest1_begin_test(400, "%d REPLACE ops on an IPK", n);
 906   speedtest1_exec("BEGIN");
 907   speedtest1_exec("CREATE%s TABLE t5(a INTEGER PRIMARY KEY, b %s);",
 908                   isTemp(9), g.zNN);
 909   speedtest1_prepare("REPLACE INTO t5 VALUES(?1,?2); --  %d times",n);
 910   for(i=1; i<=n; i++){
 911     x1 = swizzle(i,maxb);
 912     speedtest1_numbername(i, zNum, sizeof(zNum));
 913     sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
 914     sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
 915     speedtest1_run();
 916   }
 917   speedtest1_exec("COMMIT");
 918   speedtest1_end_test();
 919   speedtest1_begin_test(410, "%d SELECTS on an IPK", n);
 920   speedtest1_prepare("SELECT b FROM t5 WHERE a=?1; --  %d times",n);
 921   for(i=1; i<=n; i++){
 922     x1 = swizzle(i,maxb);
 923     sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
 924     speedtest1_run();
 925   }
 926   speedtest1_end_test();
 927
 928   sz = n = g.szTest*700;
 929   zNum[0] = 0;
 930   maxb = roundup_allones(sz/3);
 931   speedtest1_begin_test(500, "%d REPLACE on TEXT PK", n);
 932   speedtest1_exec("BEGIN");
 933   speedtest1_exec("CREATE%s TABLE t6(a TEXT PRIMARY KEY, b %s)%s;",
 934                   isTemp(9), g.zNN,
 935                   sqlite3_libversion_number()>=3008002 ? "WITHOUT ROWID" : "");
 936   speedtest1_prepare("REPLACE INTO t6 VALUES(?1,?2); --  %d times",n);
 937   for(i=1; i<=n; i++){
 938     x1 = swizzle(i,maxb);
 939     speedtest1_numbername(x1, zNum, sizeof(zNum));
 940     sqlite3_bind_int(g.pStmt, 2, i);
 941     sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
 942     speedtest1_run();
 943   }
 944   speedtest1_exec("COMMIT");
 945   speedtest1_end_test();
 946   speedtest1_begin_test(510, "%d SELECTS on a TEXT PK", n);
 947   speedtest1_prepare("SELECT b FROM t6 WHERE a=?1; --  %d times",n);
 948   for(i=1; i<=n; i++){
 949     x1 = swizzle(i,maxb);
 950     speedtest1_numbername(x1, zNum, sizeof(zNum));
 951     sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
 952     speedtest1_run();
 953   }
 954   speedtest1_end_test();
 955   speedtest1_begin_test(520, "%d SELECT DISTINCT", n);
 956   speedtest1_exec("SELECT DISTINCT b FROM t5;");
 957   speedtest1_exec("SELECT DISTINCT b FROM t6;");
 958   speedtest1_end_test();
 959
 960
 961   speedtest1_begin_test(980, "PRAGMA integrity_check");
 962   speedtest1_exec("PRAGMA integrity_check");
 963   speedtest1_end_test();
 964
 965
 966   speedtest1_begin_test(990, "ANALYZE");
 967   speedtest1_exec("ANALYZE");
 968   speedtest1_end_test();
 969 }
 970
 971 /*
 972 ** A testset for common table expressions.  This exercises code
 973 ** for views, subqueries, co-routines, etc.
 974 */
 975 void testset_cte(void){
 976   static const char *azPuzzle[] = {
 977     /* Easy */
 978     "534...9.."
 979     "67.195..."
 980     ".98....6."
 981     "8...6...3"
 982     "4..8.3..1"
 983     "....2...6"
 984     ".6....28."
 985     "...419..5"
 986     "...28..79",
 987
 988     /* Medium */
 989     "53....9.."
 990     "6..195..."
 991     ".98....6."
 992     "8...6...3"
 993     "4..8.3..1"
 994     "....2...6"
 995     ".6....28."
 996     "...419..5"
 997     "....8..79",
 998
 999     /* Hard */
1000     "53......."
1001     "6..195..."
1002     ".98....6."
1003     "8...6...3"
1004     "4..8.3..1"
1005     "....2...6"
1006     ".6....28."
1007     "...419..5"
1008     "....8..79",
1009   };
1010   const char *zPuz;
1011   double rSpacing;
1012   int nElem;
1013
1014   if( g.szTest<25 ){
1015     zPuz = azPuzzle[0];
1016   }else if( g.szTest<70 ){
1017     zPuz = azPuzzle[1];
1018   }else{
1019     zPuz = azPuzzle[2];
1020   }
1021   speedtest1_begin_test(100, "Sudoku with recursive 'digits'");
1022   speedtest1_prepare(
1023     "WITH RECURSIVE\n"
1024     "  input(sud) AS (VALUES(?1)),\n"
1025     "  digits(z,lp) AS (\n"
1026     "    VALUES('1', 1)\n"
1027     "    UNION ALL\n"
1028     "    SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9\n"
1029     "  ),\n"
1030     "  x(s, ind) AS (\n"
1031     "    SELECT sud, instr(sud, '.') FROM input\n"
1032     "    UNION ALL\n"
1033     "    SELECT\n"
1034     "      substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1035     "      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1036     "     FROM x, digits AS z\n"
1037     "    WHERE ind>0\n"
1038     "      AND NOT EXISTS (\n"
1039     "            SELECT 1\n"
1040     "              FROM digits AS lp\n"
1041     "             WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1042     "                OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1043     "                OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1044     "                        + ((ind-1)/27) * 27 + lp\n"
1045     "                        + ((lp-1) / 3) * 6, 1)\n"
1046     "         )\n"
1047     "  )\n"
1048     "SELECT s FROM x WHERE ind=0;"
1049   );
1050   sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1051   speedtest1_run();
1052   speedtest1_end_test();
1053
1054   speedtest1_begin_test(200, "Sudoku with VALUES 'digits'");
1055   speedtest1_prepare(
1056     "WITH RECURSIVE\n"
1057     "  input(sud) AS (VALUES(?1)),\n"
1058     "  digits(z,lp) AS (VALUES('1',1),('2',2),('3',3),('4',4),('5',5),\n"
1059     "                         ('6',6),('7',7),('8',8),('9',9)),\n"
1060     "  x(s, ind) AS (\n"
1061     "    SELECT sud, instr(sud, '.') FROM input\n"
1062     "    UNION ALL\n"
1063     "    SELECT\n"
1064     "      substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1065     "      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1066     "     FROM x, digits AS z\n"
1067     "    WHERE ind>0\n"
1068     "      AND NOT EXISTS (\n"
1069     "            SELECT 1\n"
1070     "              FROM digits AS lp\n"
1071     "             WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1072     "                OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1073     "                OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1074     "                        + ((ind-1)/27) * 27 + lp\n"
1075     "                        + ((lp-1) / 3) * 6, 1)\n"
1076     "         )\n"
1077     "  )\n"
1078     "SELECT s FROM x WHERE ind=0;"
1079   );
1080   sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1081   speedtest1_run();
1082   speedtest1_end_test();
1083
1084   rSpacing = 5.0/g.szTest;
1085   speedtest1_begin_test(300, "Mandelbrot Set with spacing=%f", rSpacing);
1086   speedtest1_prepare(
1087    "WITH RECURSIVE \n"
1088    "  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+?1 FROM xaxis WHERE x<1.2),\n"
1089    "  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+?2 FROM yaxis WHERE y<1.0),\n"
1090    "  m(iter, cx, cy, x, y) AS (\n"
1091    "    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis\n"
1092    "    UNION ALL\n"
1093    "    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \n"
1094    "     WHERE (x*x + y*y) < 4.0 AND iter<28\n"
1095    "  ),\n"
1096    "  m2(iter, cx, cy) AS (\n"
1097    "    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n"
1098    "  ),\n"
1099    "  a(t) AS (\n"
1100    "    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n"
1101    "    FROM m2 GROUP BY cy\n"
1102    "  )\n"
1103    "SELECT group_concat(rtrim(t),x'0a') FROM a;"
1104   );
1105   sqlite3_bind_double(g.pStmt, 1, rSpacing*.05);
1106   sqlite3_bind_double(g.pStmt, 2, rSpacing);
1107   speedtest1_run();
1108   speedtest1_end_test();
1109
1110   nElem = 10000*g.szTest;
1111   speedtest1_begin_test(400, "EXCEPT operator on %d-element tables", nElem);
1112   speedtest1_prepare(
1113     "WITH RECURSIVE \n"
1114     "  t1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM t1 WHERE x<%d),\n"
1115     "  t2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM t2 WHERE y<%d)\n"
1116     "SELECT count(x), avg(x) FROM (\n"
1117     "  SELECT x FROM t1 EXCEPT SELECT y FROM t2 ORDER BY 1\n"
1118     ");",
1119     nElem, nElem
1120   );
1121   speedtest1_run();
1122   speedtest1_end_test();
1123 }
1124
1125 /*
1126 ** Compute a pseudo-random floating point ascii number.
1127 */
1128 void speedtest1_random_ascii_fp(char *zFP){
1129   int x = speedtest1_random();
1130   int y = speedtest1_random();
1131   int z;
1132   z = y%10;
1133   if( z<0 ) z = -z;
1134   y /= 10;
1135   sqlite3_snprintf(100,zFP,"%d.%de%d",y,z,x%200);
1136 }
1137
1138 /*
1139 ** A testset for floating-point numbers.
1140 */
1141 void testset_fp(void){
1142   int n;
1143   int i;
1144   char zFP1[100];
1145   char zFP2[100];
1146
1147   n = g.szTest*5000;
1148   speedtest1_begin_test(100, "Fill a table with %d FP values", n*2);
1149   speedtest1_exec("BEGIN");
1150   speedtest1_exec("CREATE%s TABLE t1(a REAL %s, b REAL %s);",
1151                   isTemp(1), g.zNN, g.zNN);
1152   speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2); -- %d times", n);
1153   for(i=1; i<=n; i++){
1154     speedtest1_random_ascii_fp(zFP1);
1155     speedtest1_random_ascii_fp(zFP2);
1156     sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1157     sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1158     speedtest1_run();
1159   }
1160   speedtest1_exec("COMMIT");
1161   speedtest1_end_test();
1162
1163   n = g.szTest/25 + 2;
1164   speedtest1_begin_test(110, "%d range queries", n);
1165   speedtest1_prepare("SELECT sum(b) FROM t1 WHERE a BETWEEN ?1 AND ?2");
1166   for(i=1; i<=n; i++){
1167     speedtest1_random_ascii_fp(zFP1);
1168     speedtest1_random_ascii_fp(zFP2);
1169     sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1170     sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1171     speedtest1_run();
1172   }
1173   speedtest1_end_test();
1174
1175   speedtest1_begin_test(120, "CREATE INDEX three times");
1176   speedtest1_exec("BEGIN;");
1177   speedtest1_exec("CREATE INDEX t1a ON t1(a);");
1178   speedtest1_exec("CREATE INDEX t1b ON t1(b);");
1179   speedtest1_exec("CREATE INDEX t1ab ON t1(a,b);");
1180   speedtest1_exec("COMMIT;");
1181   speedtest1_end_test();
1182
1183   n = g.szTest/3 + 2;
1184   speedtest1_begin_test(130, "%d indexed range queries", n);
1185   speedtest1_prepare("SELECT sum(b) FROM t1 WHERE a BETWEEN ?1 AND ?2");
1186   for(i=1; i<=n; i++){
1187     speedtest1_random_ascii_fp(zFP1);
1188     speedtest1_random_ascii_fp(zFP2);
1189     sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1190     sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1191     speedtest1_run();
1192   }
1193   speedtest1_end_test();
1194 }
1195
1196 #ifdef SQLITE_ENABLE_RTREE
1197 /* Generate two numbers between 1 and mx.  The first number is less than
1198 ** the second.  Usually the numbers are near each other but can sometimes
1199 ** be far apart.
1200 */
1201 static void twoCoords(
1202   int p1, int p2,                   /* Parameters adjusting sizes */
1203   unsigned mx,                      /* Range of 1..mx */
1204   unsigned *pX0, unsigned *pX1      /* OUT: write results here */
1205 ){
1206   unsigned d, x0, x1, span;
1207
1208   span = mx/100 + 1;
1209   if( speedtest1_random()%3==0 ) span *= p1;
1210   if( speedtest1_random()%p2==0 ) span = mx/2;
1211   d = speedtest1_random()%span + 1;
1212   x0 = speedtest1_random()%(mx-d) + 1;
1213   x1 = x0 + d;
1214   *pX0 = x0;
1215   *pX1 = x1;
1216 }
1217 #endif
1218
1219 #ifdef SQLITE_ENABLE_RTREE
1220 /* The following routine is an R-Tree geometry callback.  It returns
1221 ** true if the object overlaps a slice on the Y coordinate between the
1222 ** two values given as arguments.  In other words
1223 **
1224 **     SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
1225 **
1226 ** Is the same as saying:
1227 **
1228 **     SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
1229 */
1230 static int xsliceGeometryCallback(
1231   sqlite3_rtree_geometry *p,
1232   int nCoord,
1233   double *aCoord,
1234   int *pRes
1235 ){
1236   *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
1237   return SQLITE_OK;
1238 }
1239 #endif /* SQLITE_ENABLE_RTREE */
1240
1241 #ifdef SQLITE_ENABLE_RTREE
1242 /*
1243 ** A testset for the R-Tree virtual table
1244 */
1245 void testset_rtree(int p1, int p2){
1246   unsigned i, n;
1247   unsigned mxCoord;
1248   unsigned x0, x1, y0, y1, z0, z1;
1249   unsigned iStep;
1250   int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*500 );
1251
1252   mxCoord = 15000;
1253   n = g.szTest*500;
1254   speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
1255   speedtest1_exec("BEGIN");
1256   speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
1257   speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
1258                      "VALUES(?1,?2,?3,?4,?5,?6,?7)");
1259   for(i=1; i<=n; i++){
1260     twoCoords(p1, p2, mxCoord, &x0, &x1);
1261     twoCoords(p1, p2, mxCoord, &y0, &y1);
1262     twoCoords(p1, p2, mxCoord, &z0, &z1);
1263     sqlite3_bind_int(g.pStmt, 1, i);
1264     sqlite3_bind_int(g.pStmt, 2, x0);
1265     sqlite3_bind_int(g.pStmt, 3, x1);
1266     sqlite3_bind_int(g.pStmt, 4, y0);
1267     sqlite3_bind_int(g.pStmt, 5, y1);
1268     sqlite3_bind_int(g.pStmt, 6, z0);
1269     sqlite3_bind_int(g.pStmt, 7, z1);
1270     speedtest1_run();
1271   }
1272   speedtest1_exec("COMMIT");
1273   speedtest1_end_test();
1274
1275   speedtest1_begin_test(101, "Copy from rtree to a regular table");
1276   speedtest1_exec("CREATE TABLE t1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
1277   speedtest1_exec("INSERT INTO t1 SELECT * FROM rt1");
1278   speedtest1_end_test();
1279
1280   n = g.szTest*100;
1281   speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
1282   speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
1283   iStep = mxCoord/n;
1284   for(i=0; i<n; i++){
1285     sqlite3_bind_int(g.pStmt, 1, i*iStep);
1286     sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1287     speedtest1_run();
1288     aCheck[i] = atoi(g.zResult);
1289   }
1290   speedtest1_end_test();
1291
1292   if( g.bVerify ){
1293     n = g.szTest*100;
1294     speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
1295     speedtest1_prepare("SELECT count(*) FROM t1 WHERE x0>=?1 AND x1<=?2");
1296     iStep = mxCoord/n;
1297     for(i=0; i<n; i++){
1298       sqlite3_bind_int(g.pStmt, 1, i*iStep);
1299       sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1300       speedtest1_run();
1301       if( aCheck[i]!=atoi(g.zResult) ){
1302         fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
1303                     i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1304       }
1305     }
1306     speedtest1_end_test();
1307   }
1308
1309   n = g.szTest*100;
1310   speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
1311   speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
1312   iStep = mxCoord/n;
1313   for(i=0; i<n; i++){
1314     sqlite3_bind_int(g.pStmt, 1, i*iStep);
1315     sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1316     speedtest1_run();
1317     aCheck[i] = atoi(g.zResult);
1318   }
1319   speedtest1_end_test();
1320
1321   if( g.bVerify ){
1322     n = g.szTest*100;
1323     speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
1324     speedtest1_prepare("SELECT count(*) FROM t1 WHERE y1>=?1 AND y0<=?2");
1325     iStep = mxCoord/n;
1326     for(i=0; i<n; i++){
1327       sqlite3_bind_int(g.pStmt, 1, i*iStep);
1328       sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1329       speedtest1_run();
1330       if( aCheck[i]!=atoi(g.zResult) ){
1331         fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
1332                     i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1333       }
1334     }
1335     speedtest1_end_test();
1336   }
1337
1338
1339   n = g.szTest*100;
1340   speedtest1_begin_test(125, "%d custom geometry callback queries", n);
1341   sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
1342   speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
1343   iStep = mxCoord/n;
1344   for(i=0; i<n; i++){
1345     sqlite3_bind_int(g.pStmt, 1, i*iStep);
1346     sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1347     speedtest1_run();
1348     if( aCheck[i]!=atoi(g.zResult) ){
1349       fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
1350                   i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1351     }
1352   }
1353   speedtest1_end_test();
1354
1355   n = g.szTest*400;
1356   speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
1357   speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
1358                      " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
1359   iStep = mxCoord/n;
1360   for(i=0; i<n; i++){
1361     sqlite3_bind_int(g.pStmt, 1, i*iStep);
1362     sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1363     speedtest1_run();
1364     aCheck[i] = atoi(g.zResult);
1365   }
1366   speedtest1_end_test();
1367
1368   n = g.szTest*500;
1369   speedtest1_begin_test(140, "%d rowid queries", n);
1370   speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
1371   for(i=1; i<=n; i++){
1372     sqlite3_bind_int(g.pStmt, 1, i);
1373     speedtest1_run();
1374   }
1375   speedtest1_end_test();
1376 }
1377 #endif /* SQLITE_ENABLE_RTREE */
1378
1379 /*
1380 ** A testset that does key/value storage on tables with many columns.
1381 ** This is the kind of workload generated by ORMs such as CoreData.
1382 */
1383 void testset_orm(void){
1384   unsigned i, j, n;
1385   unsigned nRow;
1386   unsigned x1, len;
1387   char zNum[2000];              /* A number name */
1388   static const char zType[] =   /* Types for all non-PK columns, in order */
1389     "IBBIIITIVVITBTBFBFITTFBTBVBVIFTBBFITFFVBIFIVBVVVBTVTIBBFFIVIBTB"
1390     "TVTTFTVTVFFIITIFBITFTTFFFVBIIBTTITFTFFVVVFIIITVBBVFFTVVB";
1391
1392   nRow = n = g.szTest*250;
1393   speedtest1_begin_test(100, "Fill %d rows", n);
1394   speedtest1_exec(
1395     "BEGIN;"
1396     "CREATE TABLE ZLOOKSLIKECOREDATA ("
1397     "  ZPK INTEGER PRIMARY KEY,"
1398     "  ZTERMFITTINGHOUSINGCOMMAND INTEGER,"
1399     "  ZBRIEFGOBYDODGERHEIGHT BLOB,"
1400     "  ZCAPABLETRIPDOORALMOND BLOB,"
1401     "  ZDEPOSITPAIRCOLLEGECOMET INTEGER,"
1402     "  ZFRAMEENTERSIMPLEMOUTH INTEGER,"
1403     "  ZHOPEFULGATEHOLECHALK INTEGER,"
1404     "  ZSLEEPYUSERGRANDBOWL TIMESTAMP,"
1405     "  ZDEWPEACHCAREERCELERY INTEGER,"
1406     "  ZHANGERLITHIUMDINNERMEET VARCHAR,"
1407     "  ZCLUBRELEASELIZARDADVICE VARCHAR,"
1408     "  ZCHARGECLICKHUMANEHIRE INTEGER,"
1409     "  ZFINGERDUEPIZZAOPTION TIMESTAMP,"
1410     "  ZFLYINGDOCTORTABLEMELODY BLOB,"
1411     "  ZLONGFINLEAVEIMAGEOIL TIMESTAMP,"
1412     "  ZFAMILYVISUALOWNERMATTER BLOB,"
1413     "  ZGOLDYOUNGINITIALNOSE FLOAT,"
1414     "  ZCAUSESALAMITERMCYAN BLOB,"
1415     "  ZSPREADMOTORBISCUITBACON FLOAT,"
1416     "  ZGIFTICEFISHGLUEHAIR INTEGER,"
1417     "  ZNOTICEPEARPOLICYJUICE TIMESTAMP,"
1418     "  ZBANKBUFFALORECOVERORBIT TIMESTAMP,"
1419     "  ZLONGDIETESSAYNATURE FLOAT,"
1420     "  ZACTIONRANGEELEGANTNEUTRON BLOB,"
1421     "  ZCADETBRIGHTPLANETBANK TIMESTAMP,"
1422     "  ZAIRFORGIVEHEADFROG BLOB,"
1423     "  ZSHARKJUSTFRUITMOVIE VARCHAR,"
1424     "  ZFARMERMORNINGMIRRORCONCERN BLOB,"
1425     "  ZWOODPOETRYCOBBLERBENCH VARCHAR,"
1426     "  ZHAFNIUMSCRIPTSALADMOTOR INTEGER,"
1427     "  ZPROBLEMCLUBPOPOVERJELLY FLOAT,"
1428     "  ZEIGHTLEADERWORKERMOST TIMESTAMP,"
1429     "  ZGLASSRESERVEBARIUMMEAL BLOB,"
1430     "  ZCLAMBITARUGULAFAJITA BLOB,"
1431     "  ZDECADEJOYOUSWAVEHABIT FLOAT,"
1432     "  ZCOMPANYSUMMERFIBERELF INTEGER,"
1433     "  ZTREATTESTQUILLCHARGE TIMESTAMP,"
1434     "  ZBROWBALANCEKEYCHOWDER FLOAT,"
1435     "  ZPEACHCOPPERDINNERLAKE FLOAT,"
1436     "  ZDRYWALLBEYONDBROWNBOWL VARCHAR,"
1437     "  ZBELLYCRASHITEMLACK BLOB,"
1438     "  ZTENNISCYCLEBILLOFFICER INTEGER,"
1439     "  ZMALLEQUIPTHANKSGLUE FLOAT,"
1440     "  ZMISSREPLYHUMANLIVING INTEGER,"
1441     "  ZKIWIVISUALPRIDEAPPLE VARCHAR,"
1442     "  ZWISHHITSKINMOTOR BLOB,"
1443     "  ZCALMRACCOONPROGRAMDEBIT VARCHAR,"
1444     "  ZSHINYASSISTLIVINGCRAB VARCHAR,"
1445     "  ZRESOLVEWRISTWRAPAPPLE VARCHAR,"
1446     "  ZAPPEALSIMPLESECONDHOUSING BLOB,"
1447     "  ZCORNERANCHORTAPEDIVER TIMESTAMP,"
1448     "  ZMEMORYREQUESTSOURCEBIG VARCHAR,"
1449     "  ZTRYFACTKEEPMILK TIMESTAMP,"
1450     "  ZDIVERPAINTLEATHEREASY INTEGER,"
1451     "  ZSORTMISTYQUOTECABBAGE BLOB,"
1452     "  ZTUNEGASBUFFALOCAPITAL BLOB,"
1453     "  ZFILLSTOPLAWJOYFUL FLOAT,"
1454     "  ZSTEELCAREFULPLATENUMBER FLOAT,"
1455     "  ZGIVEVIVIDDIVINEMEANING INTEGER,"
1456     "  ZTREATPACKFUTURECONVERT VARCHAR,"
1457     "  ZCALMLYGEMFINISHEFFECT INTEGER,"
1458     "  ZCABBAGESOCKEASEMINUTE BLOB,"
1459     "  ZPLANETFAMILYPUREMEMORY TIMESTAMP,"
1460     "  ZMERRYCRACKTRAINLEADER BLOB,"
1461     "  ZMINORWAYPAPERCLASSY TIMESTAMP,"
1462     "  ZEAGLELINEMINEMAIL VARCHAR,"
1463     "  ZRESORTYARDGREENLET TIMESTAMP,"
1464     "  ZYARDOREGANOVIVIDJEWEL TIMESTAMP,"
1465     "  ZPURECAKEVIVIDNEATLY FLOAT,"
1466     "  ZASKCONTACTMONITORFUN TIMESTAMP,"
1467     "  ZMOVEWHOGAMMAINCH VARCHAR,"
1468     "  ZLETTUCEBIRDMEETDEBATE TIMESTAMP,"
1469     "  ZGENENATURALHEARINGKITE VARCHAR,"
1470     "  ZMUFFINDRYERDRAWFORTUNE FLOAT,"
1471     "  ZGRAYSURVEYWIRELOVE FLOAT,"
1472     "  ZPLIERSPRINTASKOREGANO INTEGER,"
1473     "  ZTRAVELDRIVERCONTESTLILY INTEGER,"
1474     "  ZHUMORSPICESANDKIDNEY TIMESTAMP,"
1475     "  ZARSENICSAMPLEWAITMUON INTEGER,"
1476     "  ZLACEADDRESSGROUNDCAREFUL FLOAT,"
1477     "  ZBAMBOOMESSWASABIEVENING BLOB,"
1478     "  ZONERELEASEAVERAGENURSE INTEGER,"
1479     "  ZRADIANTWHENTRYCARD TIMESTAMP,"
1480     "  ZREWARDINSIDEMANGOINTENSE FLOAT,"
1481     "  ZNEATSTEWPARTIRON TIMESTAMP,"
1482     "  ZOUTSIDEPEAHENCOUNTICE TIMESTAMP,"
1483     "  ZCREAMEVENINGLIPBRANCH FLOAT,"
1484     "  ZWHALEMATHAVOCADOCOPPER FLOAT,"
1485     "  ZLIFEUSELEAFYBELL FLOAT,"
1486     "  ZWEALTHLINENGLEEFULDAY VARCHAR,"
1487     "  ZFACEINVITETALKGOLD BLOB,"
1488     "  ZWESTAMOUNTAFFECTHEARING INTEGER,"
1489     "  ZDELAYOUTCOMEHORNAGENCY INTEGER,"
1490     "  ZBIGTHINKCONVERTECONOMY BLOB,"
1491     "  ZBASEGOUDAREGULARFORGIVE TIMESTAMP,"
1492     "  ZPATTERNCLORINEGRANDCOLBY TIMESTAMP,"
1493     "  ZCYANBASEFEEDADROIT INTEGER,"
1494     "  ZCARRYFLOORMINNOWDRAGON TIMESTAMP,"
1495     "  ZIMAGEPENCILOTHERBOTTOM FLOAT,"
1496     "  ZXENONFLIGHTPALEAPPLE TIMESTAMP,"
1497     "  ZHERRINGJOKEFEATUREHOPEFUL FLOAT,"
1498     "  ZCAPYEARLYRIVETBRUSH FLOAT,"
1499     "  ZAGEREEDFROGBASKET VARCHAR,"
1500     "  ZUSUALBODYHALIBUTDIAMOND VARCHAR,"
1501     "  ZFOOTTAPWORDENTRY VARCHAR,"
1502     "  ZDISHKEEPBLESTMONITOR FLOAT,"
1503     "  ZBROADABLESOLIDCASUAL INTEGER,"
1504     "  ZSQUAREGLEEFULCHILDLIGHT INTEGER,"
1505     "  ZHOLIDAYHEADPONYDETAIL INTEGER,"
1506     "  ZGENERALRESORTSKYOPEN TIMESTAMP,"
1507     "  ZGLADSPRAYKIDNEYGUPPY VARCHAR,"
1508     "  ZSWIMHEAVYMENTIONKIND BLOB,"
1509     "  ZMESSYSULFURDREAMFESTIVE BLOB,"
1510     "  ZSKYSKYCLASSICBRIEF VARCHAR,"
1511     "  ZDILLASKHOKILEMON FLOAT,"
1512     "  ZJUNIORSHOWPRESSNOVA FLOAT,"
1513     "  ZSIZETOEAWARDFRESH TIMESTAMP,"
1514     "  ZKEYFAILAPRICOTMETAL VARCHAR,"
1515     "  ZHANDYREPAIRPROTONAIRPORT VARCHAR,"
1516     "  ZPOSTPROTEINHANDLEACTOR BLOB"
1517     ");"
1518   );
1519   speedtest1_prepare(
1520     "INSERT INTO ZLOOKSLIKECOREDATA(ZPK,ZAIRFORGIVEHEADFROG,"
1521     "ZGIFTICEFISHGLUEHAIR,ZDELAYOUTCOMEHORNAGENCY,ZSLEEPYUSERGRANDBOWL,"
1522     "ZGLASSRESERVEBARIUMMEAL,ZBRIEFGOBYDODGERHEIGHT,"
1523     "ZBAMBOOMESSWASABIEVENING,ZFARMERMORNINGMIRRORCONCERN,"
1524     "ZTREATPACKFUTURECONVERT,ZCAUSESALAMITERMCYAN,ZCALMRACCOONPROGRAMDEBIT,"
1525     "ZHOLIDAYHEADPONYDETAIL,ZWOODPOETRYCOBBLERBENCH,ZHAFNIUMSCRIPTSALADMOTOR,"
1526     "ZUSUALBODYHALIBUTDIAMOND,ZOUTSIDEPEAHENCOUNTICE,ZDIVERPAINTLEATHEREASY,"
1527     "ZWESTAMOUNTAFFECTHEARING,ZSIZETOEAWARDFRESH,ZDEWPEACHCAREERCELERY,"
1528     "ZSTEELCAREFULPLATENUMBER,ZCYANBASEFEEDADROIT,ZCALMLYGEMFINISHEFFECT,"
1529     "ZHANDYREPAIRPROTONAIRPORT,ZGENENATURALHEARINGKITE,ZBROADABLESOLIDCASUAL,"
1530     "ZPOSTPROTEINHANDLEACTOR,ZLACEADDRESSGROUNDCAREFUL,ZIMAGEPENCILOTHERBOTTOM,"
1531     "ZPROBLEMCLUBPOPOVERJELLY,ZPATTERNCLORINEGRANDCOLBY,ZNEATSTEWPARTIRON,"
1532     "ZAPPEALSIMPLESECONDHOUSING,ZMOVEWHOGAMMAINCH,ZTENNISCYCLEBILLOFFICER,"
1533     "ZSHARKJUSTFRUITMOVIE,ZKEYFAILAPRICOTMETAL,ZCOMPANYSUMMERFIBERELF,"
1534     "ZTERMFITTINGHOUSINGCOMMAND,ZRESORTYARDGREENLET,ZCABBAGESOCKEASEMINUTE,"
1535     "ZSQUAREGLEEFULCHILDLIGHT,ZONERELEASEAVERAGENURSE,ZBIGTHINKCONVERTECONOMY,"
1536     "ZPLIERSPRINTASKOREGANO,ZDECADEJOYOUSWAVEHABIT,ZDRYWALLBEYONDBROWNBOWL,"
1537     "ZCLUBRELEASELIZARDADVICE,ZWHALEMATHAVOCADOCOPPER,ZBELLYCRASHITEMLACK,"
1538     "ZLETTUCEBIRDMEETDEBATE,ZCAPABLETRIPDOORALMOND,ZRADIANTWHENTRYCARD,"
1539     "ZCAPYEARLYRIVETBRUSH,ZAGEREEDFROGBASKET,ZSWIMHEAVYMENTIONKIND,"
1540     "ZTRAVELDRIVERCONTESTLILY,ZGLADSPRAYKIDNEYGUPPY,ZBANKBUFFALORECOVERORBIT,"
1541     "ZFINGERDUEPIZZAOPTION,ZCLAMBITARUGULAFAJITA,ZLONGFINLEAVEIMAGEOIL,"
1542     "ZLONGDIETESSAYNATURE,ZJUNIORSHOWPRESSNOVA,ZHOPEFULGATEHOLECHALK,"
1543     "ZDEPOSITPAIRCOLLEGECOMET,ZWEALTHLINENGLEEFULDAY,ZFILLSTOPLAWJOYFUL,"
1544     "ZTUNEGASBUFFALOCAPITAL,ZGRAYSURVEYWIRELOVE,ZCORNERANCHORTAPEDIVER,"
1545     "ZREWARDINSIDEMANGOINTENSE,ZCADETBRIGHTPLANETBANK,ZPLANETFAMILYPUREMEMORY,"
1546     "ZTREATTESTQUILLCHARGE,ZCREAMEVENINGLIPBRANCH,ZSKYSKYCLASSICBRIEF,"
1547     "ZARSENICSAMPLEWAITMUON,ZBROWBALANCEKEYCHOWDER,ZFLYINGDOCTORTABLEMELODY,"
1548     "ZHANGERLITHIUMDINNERMEET,ZNOTICEPEARPOLICYJUICE,ZSHINYASSISTLIVINGCRAB,"
1549     "ZLIFEUSELEAFYBELL,ZFACEINVITETALKGOLD,ZGENERALRESORTSKYOPEN,"
1550     "ZPURECAKEVIVIDNEATLY,ZKIWIVISUALPRIDEAPPLE,ZMESSYSULFURDREAMFESTIVE,"
1551     "ZCHARGECLICKHUMANEHIRE,ZHERRINGJOKEFEATUREHOPEFUL,ZYARDOREGANOVIVIDJEWEL,"
1552     "ZFOOTTAPWORDENTRY,ZWISHHITSKINMOTOR,ZBASEGOUDAREGULARFORGIVE,"
1553     "ZMUFFINDRYERDRAWFORTUNE,ZACTIONRANGEELEGANTNEUTRON,ZTRYFACTKEEPMILK,"
1554     "ZPEACHCOPPERDINNERLAKE,ZFRAMEENTERSIMPLEMOUTH,ZMERRYCRACKTRAINLEADER,"
1555     "ZMEMORYREQUESTSOURCEBIG,ZCARRYFLOORMINNOWDRAGON,ZMINORWAYPAPERCLASSY,"
1556     "ZDILLASKHOKILEMON,ZRESOLVEWRISTWRAPAPPLE,ZASKCONTACTMONITORFUN,"
1557     "ZGIVEVIVIDDIVINEMEANING,ZEIGHTLEADERWORKERMOST,ZMISSREPLYHUMANLIVING,"
1558     "ZXENONFLIGHTPALEAPPLE,ZSORTMISTYQUOTECABBAGE,ZEAGLELINEMINEMAIL,"
1559     "ZFAMILYVISUALOWNERMATTER,ZSPREADMOTORBISCUITBACON,ZDISHKEEPBLESTMONITOR,"
1560     "ZMALLEQUIPTHANKSGLUE,ZGOLDYOUNGINITIALNOSE,ZHUMORSPICESANDKIDNEY)"
1561     "VALUES(?1,?26,?20,?93,?8,?33,?3,?81,?28,?60,?18,?47,?109,?29,?30,?104,?86,"
1562     "?54,?92,?117,?9,?58,?97,?61,?119,?73,?107,?120,?80,?99,?31,?96,?85,?50,?71,"
1563     "?42,?27,?118,?36,?2,?67,?62,?108,?82,?94,?76,?35,?40,?11,?88,?41,?72,?4,"
1564     "?83,?102,?103,?112,?77,?111,?22,?13,?34,?15,?23,?116,?7,?5,?90,?57,?56,"
1565     "?75,?51,?84,?25,?63,?37,?87,?114,?79,?38,?14,?10,?21,?48,?89,?91,?110,"
1566     "?69,?45,?113,?12,?101,?68,?105,?46,?95,?74,?24,?53,?39,?6,?64,?52,?98,"
1567     "?65,?115,?49,?70,?59,?32,?44,?100,?55,?66,?16,?19,?106,?43,?17,?78);"
1568   );
1569   for(i=0; i<n; i++){
1570     x1 = speedtest1_random();
1571     speedtest1_numbername(x1%1000, zNum, sizeof(zNum));
1572     len = (int)strlen(zNum);
1573     sqlite3_bind_int(g.pStmt, 1, i^0xf);
1574     for(j=0; zType[j]; j++){
1575       switch( zType[j] ){
1576         case 'I':
1577         case 'T':
1578           sqlite3_bind_int64(g.pStmt, j+2, x1);
1579           break;
1580         case 'F':
1581           sqlite3_bind_double(g.pStmt, j+2, (double)x1);
1582           break;
1583         case 'V':
1584         case 'B':
1585           sqlite3_bind_text64(g.pStmt, j+2, zNum, len,
1586                               SQLITE_STATIC, SQLITE_UTF8);
1587           break;
1588       }
1589     }
1590     speedtest1_run();
1591   }
1592   speedtest1_exec("COMMIT;");
1593   speedtest1_end_test();
1594
1595   n = g.szTest*250;
1596   speedtest1_begin_test(110, "Query %d rows by rowid", n);
1597   speedtest1_prepare(
1598     "SELECT ZCYANBASEFEEDADROIT,ZJUNIORSHOWPRESSNOVA,ZCAUSESALAMITERMCYAN,"
1599     "ZHOPEFULGATEHOLECHALK,ZHUMORSPICESANDKIDNEY,ZSWIMHEAVYMENTIONKIND,"
1600     "ZMOVEWHOGAMMAINCH,ZAPPEALSIMPLESECONDHOUSING,ZHAFNIUMSCRIPTSALADMOTOR,"
1601     "ZNEATSTEWPARTIRON,ZLONGFINLEAVEIMAGEOIL,ZDEWPEACHCAREERCELERY,"
1602     "ZXENONFLIGHTPALEAPPLE,ZCALMRACCOONPROGRAMDEBIT,ZUSUALBODYHALIBUTDIAMOND,"
1603     "ZTRYFACTKEEPMILK,ZWEALTHLINENGLEEFULDAY,ZLONGDIETESSAYNATURE,"
1604     "ZLIFEUSELEAFYBELL,ZTREATPACKFUTURECONVERT,ZMEMORYREQUESTSOURCEBIG,"
1605     "ZYARDOREGANOVIVIDJEWEL,ZDEPOSITPAIRCOLLEGECOMET,ZSLEEPYUSERGRANDBOWL,"
1606     "ZBRIEFGOBYDODGERHEIGHT,ZCLUBRELEASELIZARDADVICE,ZCAPABLETRIPDOORALMOND,"
1607     "ZDRYWALLBEYONDBROWNBOWL,ZASKCONTACTMONITORFUN,ZKIWIVISUALPRIDEAPPLE,"
1608     "ZNOTICEPEARPOLICYJUICE,ZPEACHCOPPERDINNERLAKE,ZSTEELCAREFULPLATENUMBER,"
1609     "ZGLADSPRAYKIDNEYGUPPY,ZCOMPANYSUMMERFIBERELF,ZTENNISCYCLEBILLOFFICER,"
1610     "ZIMAGEPENCILOTHERBOTTOM,ZWESTAMOUNTAFFECTHEARING,ZDIVERPAINTLEATHEREASY,"
1611     "ZSKYSKYCLASSICBRIEF,ZMESSYSULFURDREAMFESTIVE,ZMERRYCRACKTRAINLEADER,"
1612     "ZBROADABLESOLIDCASUAL,ZGLASSRESERVEBARIUMMEAL,ZTUNEGASBUFFALOCAPITAL,"
1613     "ZBANKBUFFALORECOVERORBIT,ZTREATTESTQUILLCHARGE,ZBAMBOOMESSWASABIEVENING,"
1614     "ZREWARDINSIDEMANGOINTENSE,ZEAGLELINEMINEMAIL,ZCALMLYGEMFINISHEFFECT,"
1615     "ZKEYFAILAPRICOTMETAL,ZFINGERDUEPIZZAOPTION,ZCADETBRIGHTPLANETBANK,"
1616     "ZGOLDYOUNGINITIALNOSE,ZMISSREPLYHUMANLIVING,ZEIGHTLEADERWORKERMOST,"
1617     "ZFRAMEENTERSIMPLEMOUTH,ZBIGTHINKCONVERTECONOMY,ZFACEINVITETALKGOLD,"
1618     "ZPOSTPROTEINHANDLEACTOR,ZHERRINGJOKEFEATUREHOPEFUL,ZCABBAGESOCKEASEMINUTE,"
1619     "ZMUFFINDRYERDRAWFORTUNE,ZPROBLEMCLUBPOPOVERJELLY,ZGIVEVIVIDDIVINEMEANING,"
1620     "ZGENENATURALHEARINGKITE,ZGENERALRESORTSKYOPEN,ZLETTUCEBIRDMEETDEBATE,"
1621     "ZBASEGOUDAREGULARFORGIVE,ZCHARGECLICKHUMANEHIRE,ZPLANETFAMILYPUREMEMORY,"
1622     "ZMINORWAYPAPERCLASSY,ZCAPYEARLYRIVETBRUSH,ZSIZETOEAWARDFRESH,"
1623     "ZARSENICSAMPLEWAITMUON,ZSQUAREGLEEFULCHILDLIGHT,ZSHINYASSISTLIVINGCRAB,"
1624     "ZCORNERANCHORTAPEDIVER,ZDECADEJOYOUSWAVEHABIT,ZTRAVELDRIVERCONTESTLILY,"
1625     "ZFLYINGDOCTORTABLEMELODY,ZSHARKJUSTFRUITMOVIE,ZFAMILYVISUALOWNERMATTER,"
1626     "ZFARMERMORNINGMIRRORCONCERN,ZGIFTICEFISHGLUEHAIR,ZOUTSIDEPEAHENCOUNTICE,"
1627     "ZSPREADMOTORBISCUITBACON,ZWISHHITSKINMOTOR,ZHOLIDAYHEADPONYDETAIL,"
1628     "ZWOODPOETRYCOBBLERBENCH,ZAIRFORGIVEHEADFROG,ZBROWBALANCEKEYCHOWDER,"
1629     "ZDISHKEEPBLESTMONITOR,ZCLAMBITARUGULAFAJITA,ZPLIERSPRINTASKOREGANO,"
1630     "ZRADIANTWHENTRYCARD,ZDELAYOUTCOMEHORNAGENCY,ZPURECAKEVIVIDNEATLY,"
1631     "ZPATTERNCLORINEGRANDCOLBY,ZHANDYREPAIRPROTONAIRPORT,ZAGEREEDFROGBASKET,"
1632     "ZSORTMISTYQUOTECABBAGE,ZFOOTTAPWORDENTRY,ZRESOLVEWRISTWRAPAPPLE,"
1633     "ZDILLASKHOKILEMON,ZFILLSTOPLAWJOYFUL,ZACTIONRANGEELEGANTNEUTRON,"
1634     "ZRESORTYARDGREENLET,ZCREAMEVENINGLIPBRANCH,ZWHALEMATHAVOCADOCOPPER,"
1635     "ZGRAYSURVEYWIRELOVE,ZBELLYCRASHITEMLACK,ZHANGERLITHIUMDINNERMEET,"
1636     "ZCARRYFLOORMINNOWDRAGON,ZMALLEQUIPTHANKSGLUE,ZTERMFITTINGHOUSINGCOMMAND,"
1637     "ZONERELEASEAVERAGENURSE,ZLACEADDRESSGROUNDCAREFUL"
1638     " FROM ZLOOKSLIKECOREDATA WHERE ZPK=?1;"
1639   );
1640   for(i=0; i<n; i++){
1641     x1 = speedtest1_random()%nRow;
1642     sqlite3_bind_int(g.pStmt, 1, x1);
1643     speedtest1_run();
1644   }
1645   speedtest1_end_test();
1646 }
1647
1648 /*
1649 ** A testset used for debugging speedtest1 itself.
1650 */
1651 void testset_debug1(void){
1652   unsigned i, n;
1653   unsigned x1, x2;
1654   char zNum[2000];              /* A number name */
1655
1656   n = g.szTest;
1657   for(i=1; i<=n; i++){
1658     x1 = swizzle(i, n);
1659     x2 = swizzle(x1, n);
1660     speedtest1_numbername(x1, zNum, sizeof(zNum));
1661     printf("%5d %5d %5d %s\n", i, x1, x2, zNum);
1662   }
1663 }
1664
1665 #ifdef __linux__
1666 #include <sys/types.h>
1667 #include <unistd.h>
1668
1669 /*
1670 ** Attempt to display I/O stats on Linux using /proc/PID/io
1671 */
1672 static void displayLinuxIoStats(FILE *out){
1673   FILE *in;
1674   char z[200];
1675   sqlite3_snprintf(sizeof(z), z, "/proc/%d/io", getpid());
1676   in = fopen(z, "rb");
1677   if( in==0 ) return;
1678   while( fgets(z, sizeof(z), in)!=0 ){
1679     static const struct {
1680       const char *zPattern;
1681       const char *zDesc;
1682     } aTrans[] = {
1683       { "rchar: ",                  "Bytes received by read():" },
1684       { "wchar: ",                  "Bytes sent to write():"    },
1685       { "syscr: ",                  "Read() system calls:"      },
1686       { "syscw: ",                  "Write() system calls:"     },
1687       { "read_bytes: ",             "Bytes rcvd from storage:"  },
1688       { "write_bytes: ",            "Bytes sent to storage:"    },
1689       { "cancelled_write_bytes: ",  "Cancelled write bytes:"    },
1690     };
1691     int i;
1692     for(i=0; i<sizeof(aTrans)/sizeof(aTrans[0]); i++){
1693       int n = (int)strlen(aTrans[i].zPattern);
1694       if( strncmp(aTrans[i].zPattern, z, n)==0 ){
1695         fprintf(out, "-- %-28s %s", aTrans[i].zDesc, &z[n]);
1696         break;
1697       }
1698     }
1699   }
1700   fclose(in);
1701 }
1702 #endif
1703
1704 #if SQLITE_VERSION_NUMBER<3006018
1705 #  define sqlite3_sourceid(X) "(before 3.6.18)"
1706 #endif
1707
1708 static int xCompileOptions(void *pCtx, int nVal, char **azVal, char **azCol){
1709   printf("-- Compile option: %s\n", azVal[0]);
1710   return SQLITE_OK;
1711 }
1712
1713 int main(int argc, char **argv){
1714   int doAutovac = 0;            /* True for --autovacuum */
1715   int cacheSize = 0;            /* Desired cache size.  0 means default */
1716   int doExclusive = 0;          /* True for --exclusive */
1717   int nHeap = 0, mnHeap = 0;    /* Heap size from --heap */
1718   int doIncrvac = 0;            /* True for --incrvacuum */
1719   const char *zJMode = 0;       /* Journal mode */
1720   const char *zKey = 0;         /* Encryption key */
1721   int nLook = -1, szLook = 0;   /* --lookaside configuration */
1722   int noSync = 0;               /* True for --nosync */
1723   int pageSize = 0;             /* Desired page size.  0 means default */
1724   int nPCache = 0, szPCache = 0;/* --pcache configuration */
1725   int doPCache = 0;             /* True if --pcache is seen */
1726   int showStats = 0;            /* True for --stats */
1727   int nThread = 0;              /* --threads value */
1728   int mmapSize = 0;             /* How big of a memory map to use */
1729   const char *zTSet = "main";   /* Which --testset torun */
1730   int doTrace = 0;              /* True for --trace */
1731   const char *zEncoding = 0;    /* --utf16be or --utf16le */
1732   const char *zDbName = 0;      /* Name of the test database */
1733
1734   void *pHeap = 0;              /* Allocated heap space */
1735   void *pLook = 0;              /* Allocated lookaside space */
1736   void *pPCache = 0;            /* Allocated storage for pcache */
1737   int iCur, iHi;                /* Stats values, current and "highwater" */
1738   int i;                        /* Loop counter */
1739   int rc;                       /* API return code */
1740
1741   /* Display the version of SQLite being tested */
1742   printf("-- Speedtest1 for SQLite %s %.50s\n",
1743          sqlite3_libversion(), sqlite3_sourceid());
1744
1745   /* Process command-line arguments */
1746   g.zWR = "";
1747   g.zNN = "";
1748   g.zPK = "UNIQUE";
1749   g.szTest = 100;
1750   g.nRepeat = 1;
1751   for(i=1; i<argc; i++){
1752     const char *z = argv[i];
1753     if( z[0]=='-' ){
1754       do{ z++; }while( z[0]=='-' );
1755       if( strcmp(z,"autovacuum")==0 ){
1756         doAutovac = 1;
1757       }else if( strcmp(z,"cachesize")==0 ){
1758         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1759         i++;
1760         cacheSize = integerValue(argv[i]);
1761       }else if( strcmp(z,"exclusive")==0 ){
1762         doExclusive = 1;
1763       }else if( strcmp(z,"explain")==0 ){
1764         g.bSqlOnly = 1;
1765         g.bExplain = 1;
1766       }else if( strcmp(z,"heap")==0 ){
1767         if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1768         nHeap = integerValue(argv[i+1]);
1769         mnHeap = integerValue(argv[i+2]);
1770         i += 2;
1771       }else if( strcmp(z,"incrvacuum")==0 ){
1772         doIncrvac = 1;
1773       }else if( strcmp(z,"journal")==0 ){
1774         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1775         zJMode = argv[++i];
1776       }else if( strcmp(z,"key")==0 ){
1777         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1778         zKey = argv[++i];
1779       }else if( strcmp(z,"lookaside")==0 ){
1780         if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1781         nLook = integerValue(argv[i+1]);
1782         szLook = integerValue(argv[i+2]);
1783         i += 2;
1784 #if SQLITE_VERSION_NUMBER>=3006000
1785       }else if( strcmp(z,"multithread")==0 ){
1786         sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
1787       }else if( strcmp(z,"nomemstat")==0 ){
1788         sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
1789 #endif
1790 #if SQLITE_VERSION_NUMBER>=3007017
1791       }else if( strcmp(z, "mmap")==0 ){
1792         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1793         mmapSize = integerValue(argv[++i]);
1794  #endif
1795       }else if( strcmp(z,"nosync")==0 ){
1796         noSync = 1;
1797       }else if( strcmp(z,"notnull")==0 ){
1798         g.zNN = "NOT NULL";
1799       }else if( strcmp(z,"pagesize")==0 ){
1800         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1801         pageSize = integerValue(argv[++i]);
1802       }else if( strcmp(z,"pcache")==0 ){
1803         if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1804         nPCache = integerValue(argv[i+1]);
1805         szPCache = integerValue(argv[i+2]);
1806         doPCache = 1;
1807         i += 2;
1808       }else if( strcmp(z,"primarykey")==0 ){
1809         g.zPK = "PRIMARY KEY";
1810       }else if( strcmp(z,"repeat")==0 ){
1811         if( i>=argc-1 ) fatal_error("missing arguments on %s\n", argv[i]);
1812         g.nRepeat = integerValue(argv[i+1]);
1813         i += 1;
1814       }else if( strcmp(z,"reprepare")==0 ){
1815         g.bReprepare = 1;
1816 #if SQLITE_VERSION_NUMBER>=3006000
1817       }else if( strcmp(z,"serialized")==0 ){
1818         sqlite3_config(SQLITE_CONFIG_SERIALIZED);
1819       }else if( strcmp(z,"singlethread")==0 ){
1820         sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
1821 #endif
1822       }else if( strcmp(z,"sqlonly")==0 ){
1823         g.bSqlOnly = 1;
1824       }else if( strcmp(z,"shrink-memory")==0 ){
1825         g.bMemShrink = 1;
1826       }else if( strcmp(z,"size")==0 ){
1827         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1828         g.szTest = integerValue(argv[++i]);
1829       }else if( strcmp(z,"stats")==0 ){
1830         showStats = 1;
1831       }else if( strcmp(z,"temp")==0 ){
1832         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1833         i++;
1834         if( argv[i][0]<'0' || argv[i][0]>'9' || argv[i][1]!=0 ){
1835           fatal_error("argument to --temp should be integer between 0 and 9");
1836         }
1837         g.eTemp = argv[i][0] - '0';
1838       }else if( strcmp(z,"testset")==0 ){
1839         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1840         zTSet = argv[++i];
1841       }else if( strcmp(z,"trace")==0 ){
1842         doTrace = 1;
1843       }else if( strcmp(z,"threads")==0 ){
1844         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1845         nThread = integerValue(argv[++i]);
1846       }else if( strcmp(z,"utf16le")==0 ){
1847         zEncoding = "utf16le";
1848       }else if( strcmp(z,"utf16be")==0 ){
1849         zEncoding = "utf16be";
1850       }else if( strcmp(z,"verify")==0 ){
1851         g.bVerify = 1;
1852       }else if( strcmp(z,"without-rowid")==0 ){
1853         g.zWR = "WITHOUT ROWID";
1854         g.zPK = "PRIMARY KEY";
1855       }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
1856         printf(zHelp, argv[0]);
1857         exit(0);
1858       }else{
1859         fatal_error("unknown option: %s\nUse \"%s -?\" for help\n",
1860                     argv[i], argv[0]);
1861       }
1862     }else if( zDbName==0 ){
1863       zDbName = argv[i];
1864     }else{
1865       fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n",
1866                   argv[i], argv[0]);
1867     }
1868   }
1869   if( zDbName!=0 ) unlink(zDbName);
1870 #if SQLITE_VERSION_NUMBER>=3006001
1871   if( nHeap>0 ){
1872     pHeap = malloc( nHeap );
1873     if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap);
1874     rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap);
1875     if( rc ) fatal_error("heap configuration failed: %d\n", rc);
1876   }
1877   if( doPCache ){
1878     if( nPCache>0 && szPCache>0 ){
1879       pPCache = malloc( nPCache*(sqlite3_int64)szPCache );
1880       if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n",
1881                                    nPCache*(sqlite3_int64)szPCache);
1882     }
1883     rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache);
1884     if( rc ) fatal_error("pcache configuration failed: %d\n", rc);
1885   }
1886   if( nLook>=0 ){
1887     sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0);
1888   }
1889 #endif
1890
1891   /* Open the database and the input file */
1892   if( sqlite3_open(zDbName, &g.db) ){
1893     fatal_error("Cannot open database file: %s\n", zDbName);
1894   }
1895 #if SQLITE_VERSION_NUMBER>=3006001
1896   if( nLook>0 && szLook>0 ){
1897     pLook = malloc( nLook*szLook );
1898     rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE, pLook, szLook,nLook);
1899     if( rc ) fatal_error("lookaside configuration failed: %d\n", rc);
1900   }
1901 #endif
1902
1903   /* Set database connection options */
1904   sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc, 0, 0);
1905 #ifndef SQLITE_OMIT_DEPRECATED
1906   if( doTrace ) sqlite3_trace(g.db, traceCallback, 0);
1907 #endif
1908   if( mmapSize>0 ){
1909     speedtest1_exec("PRAGMA mmap_size=%d", mmapSize);
1910   }
1911   speedtest1_exec("PRAGMA threads=%d", nThread);
1912   if( zKey ){
1913     speedtest1_exec("PRAGMA key('%s')", zKey);
1914   }
1915   if( zEncoding ){
1916     speedtest1_exec("PRAGMA encoding=%s", zEncoding);
1917   }
1918   if( doAutovac ){
1919     speedtest1_exec("PRAGMA auto_vacuum=FULL");
1920   }else if( doIncrvac ){
1921     speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL");
1922   }
1923   if( pageSize ){
1924     speedtest1_exec("PRAGMA page_size=%d", pageSize);
1925   }
1926   if( cacheSize ){
1927     speedtest1_exec("PRAGMA cache_size=%d", cacheSize);
1928   }
1929   if( noSync ) speedtest1_exec("PRAGMA synchronous=OFF");
1930   if( doExclusive ){
1931     speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
1932   }
1933   if( zJMode ){
1934     speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
1935   }
1936
1937   if( g.bExplain ) printf(".explain\n.echo on\n");
1938   if( strcmp(zTSet,"main")==0 ){
1939     testset_main();
1940   }else if( strcmp(zTSet,"debug1")==0 ){
1941     testset_debug1();
1942   }else if( strcmp(zTSet,"orm")==0 ){
1943     testset_orm();
1944   }else if( strcmp(zTSet,"cte")==0 ){
1945     testset_cte();
1946   }else if( strcmp(zTSet,"fp")==0 ){
1947     testset_fp();
1948   }else if( strcmp(zTSet,"rtree")==0 ){
1949 #ifdef SQLITE_ENABLE_RTREE
1950     testset_rtree(6, 147);
1951 #else
1952     fatal_error("compile with -DSQLITE_ENABLE_RTREE to enable "
1953                 "the R-Tree tests\n");
1954 #endif
1955   }else{
1956     fatal_error("unknown testset: \"%s\"\nChoices: main debug1 cte rtree fp\n",
1957                  zTSet);
1958   }
1959   speedtest1_final();
1960
1961   if( showStats ){
1962     sqlite3_exec(g.db, "PRAGMA compile_options", xCompileOptions, 0, 0);
1963   }
1964
1965   /* Database connection statistics printed after both prepared statements
1966   ** have been finalized */
1967 #if SQLITE_VERSION_NUMBER>=3007009
1968   if( showStats ){
1969     sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0);
1970     printf("-- Lookaside Slots Used:        %d (max %d)\n", iCur,iHi);
1971     sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0);
1972     printf("-- Successful lookasides:       %d\n", iHi);
1973     sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0);
1974     printf("-- Lookaside size faults:       %d\n", iHi);
1975     sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0);
1976     printf("-- Lookaside OOM faults:        %d\n", iHi);
1977     sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0);
1978     printf("-- Pager Heap Usage:            %d bytes\n", iCur);
1979     sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1);
1980     printf("-- Page cache hits:             %d\n", iCur);
1981     sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1);
1982     printf("-- Page cache misses:           %d\n", iCur);
1983 #if SQLITE_VERSION_NUMBER>=3007012
1984     sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1);
1985     printf("-- Page cache writes:           %d\n", iCur);
1986 #endif
1987     sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0);
1988     printf("-- Schema Heap Usage:           %d bytes\n", iCur);
1989     sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0);
1990     printf("-- Statement Heap Usage:        %d bytes\n", iCur);
1991   }
1992 #endif
1993
1994   sqlite3_close(g.db);
1995
1996 #if SQLITE_VERSION_NUMBER>=3006001
1997   /* Global memory usage statistics printed after the database connection
1998   ** has closed.  Memory usage should be zero at this point. */
1999   if( showStats ){
2000     sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0);
2001     printf("-- Memory Used (bytes):         %d (max %d)\n", iCur,iHi);
2002 #if SQLITE_VERSION_NUMBER>=3007000
2003     sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0);
2004     printf("-- Outstanding Allocations:     %d (max %d)\n", iCur,iHi);
2005 #endif
2006     sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0);
2007     printf("-- Pcache Overflow Bytes:       %d (max %d)\n", iCur,iHi);
2008     sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0);
2009     printf("-- Largest Allocation:          %d bytes\n",iHi);
2010     sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0);
2011     printf("-- Largest Pcache Allocation:   %d bytes\n",iHi);
2012   }
2013 #endif
2014
2015 #ifdef __linux__
2016   if( showStats ){
2017     displayLinuxIoStats(stdout);
2018   }
2019 #endif
2020
2021   /* Release memory */
2022   free( pLook );
2023   free( pPCache );
2024   free( pHeap );
2025   return 0;
2026 }