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