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