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