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