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