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