modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/speedtest1.c (about) 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, 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 #ifdef SQLITE_ENABLE_RTREE 1127 /* Generate two numbers between 1 and mx. The first number is less than 1128 ** the second. Usually the numbers are near each other but can sometimes 1129 ** be far apart. 1130 */ 1131 static void twoCoords( 1132 int p1, int p2, /* Parameters adjusting sizes */ 1133 unsigned mx, /* Range of 1..mx */ 1134 unsigned *pX0, unsigned *pX1 /* OUT: write results here */ 1135 ){ 1136 unsigned d, x0, x1, span; 1137 1138 span = mx/100 + 1; 1139 if( speedtest1_random()%3==0 ) span *= p1; 1140 if( speedtest1_random()%p2==0 ) span = mx/2; 1141 d = speedtest1_random()%span + 1; 1142 x0 = speedtest1_random()%(mx-d) + 1; 1143 x1 = x0 + d; 1144 *pX0 = x0; 1145 *pX1 = x1; 1146 } 1147 #endif 1148 1149 #ifdef SQLITE_ENABLE_RTREE 1150 /* The following routine is an R-Tree geometry callback. It returns 1151 ** true if the object overlaps a slice on the Y coordinate between the 1152 ** two values given as arguments. In other words 1153 ** 1154 ** SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20); 1155 ** 1156 ** Is the same as saying: 1157 ** 1158 ** SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20; 1159 */ 1160 static int xsliceGeometryCallback( 1161 sqlite3_rtree_geometry *p, 1162 int nCoord, 1163 double *aCoord, 1164 int *pRes 1165 ){ 1166 *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1]; 1167 return SQLITE_OK; 1168 } 1169 #endif /* SQLITE_ENABLE_RTREE */ 1170 1171 #ifdef SQLITE_ENABLE_RTREE 1172 /* 1173 ** A testset for the R-Tree virtual table 1174 */ 1175 void testset_rtree(int p1, int p2){ 1176 unsigned i, n; 1177 unsigned mxCoord; 1178 unsigned x0, x1, y0, y1, z0, z1; 1179 unsigned iStep; 1180 int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*500 ); 1181 1182 mxCoord = 15000; 1183 n = g.szTest*500; 1184 speedtest1_begin_test(100, "%d INSERTs into an r-tree", n); 1185 speedtest1_exec("BEGIN"); 1186 speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)"); 1187 speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)" 1188 "VALUES(?1,?2,?3,?4,?5,?6,?7)"); 1189 for(i=1; i<=n; i++){ 1190 twoCoords(p1, p2, mxCoord, &x0, &x1); 1191 twoCoords(p1, p2, mxCoord, &y0, &y1); 1192 twoCoords(p1, p2, mxCoord, &z0, &z1); 1193 sqlite3_bind_int(g.pStmt, 1, i); 1194 sqlite3_bind_int(g.pStmt, 2, x0); 1195 sqlite3_bind_int(g.pStmt, 3, x1); 1196 sqlite3_bind_int(g.pStmt, 4, y0); 1197 sqlite3_bind_int(g.pStmt, 5, y1); 1198 sqlite3_bind_int(g.pStmt, 6, z0); 1199 sqlite3_bind_int(g.pStmt, 7, z1); 1200 speedtest1_run(); 1201 } 1202 speedtest1_exec("COMMIT"); 1203 speedtest1_end_test(); 1204 1205 speedtest1_begin_test(101, "Copy from rtree to a regular table"); 1206 speedtest1_exec("CREATE TABLE t1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)"); 1207 speedtest1_exec("INSERT INTO t1 SELECT * FROM rt1"); 1208 speedtest1_end_test(); 1209 1210 n = g.szTest*100; 1211 speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n); 1212 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2"); 1213 iStep = mxCoord/n; 1214 for(i=0; i<n; i++){ 1215 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1216 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1217 speedtest1_run(); 1218 aCheck[i] = atoi(g.zResult); 1219 } 1220 speedtest1_end_test(); 1221 1222 if( g.bVerify ){ 1223 n = g.szTest*100; 1224 speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries"); 1225 speedtest1_prepare("SELECT count(*) FROM t1 WHERE x0>=?1 AND x1<=?2"); 1226 iStep = mxCoord/n; 1227 for(i=0; i<n; i++){ 1228 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1229 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1230 speedtest1_run(); 1231 if( aCheck[i]!=atoi(g.zResult) ){ 1232 fatal_error("Count disagree step %d: %d..%d. %d vs %d", 1233 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult)); 1234 } 1235 } 1236 speedtest1_end_test(); 1237 } 1238 1239 n = g.szTest*100; 1240 speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n); 1241 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2"); 1242 iStep = mxCoord/n; 1243 for(i=0; i<n; i++){ 1244 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1245 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1246 speedtest1_run(); 1247 aCheck[i] = atoi(g.zResult); 1248 } 1249 speedtest1_end_test(); 1250 1251 if( g.bVerify ){ 1252 n = g.szTest*100; 1253 speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries"); 1254 speedtest1_prepare("SELECT count(*) FROM t1 WHERE y1>=?1 AND y0<=?2"); 1255 iStep = mxCoord/n; 1256 for(i=0; i<n; i++){ 1257 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1258 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1259 speedtest1_run(); 1260 if( aCheck[i]!=atoi(g.zResult) ){ 1261 fatal_error("Count disagree step %d: %d..%d. %d vs %d", 1262 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult)); 1263 } 1264 } 1265 speedtest1_end_test(); 1266 } 1267 1268 1269 n = g.szTest*100; 1270 speedtest1_begin_test(125, "%d custom geometry callback queries", n); 1271 sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0); 1272 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)"); 1273 iStep = mxCoord/n; 1274 for(i=0; i<n; i++){ 1275 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1276 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1277 speedtest1_run(); 1278 if( aCheck[i]!=atoi(g.zResult) ){ 1279 fatal_error("Count disagree step %d: %d..%d. %d vs %d", 1280 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult)); 1281 } 1282 } 1283 speedtest1_end_test(); 1284 1285 n = g.szTest*400; 1286 speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n); 1287 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2" 1288 " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2"); 1289 iStep = mxCoord/n; 1290 for(i=0; i<n; i++){ 1291 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1292 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1293 speedtest1_run(); 1294 aCheck[i] = atoi(g.zResult); 1295 } 1296 speedtest1_end_test(); 1297 1298 n = g.szTest*500; 1299 speedtest1_begin_test(140, "%d rowid queries", n); 1300 speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1"); 1301 for(i=1; i<=n; i++){ 1302 sqlite3_bind_int(g.pStmt, 1, i); 1303 speedtest1_run(); 1304 } 1305 speedtest1_end_test(); 1306 } 1307 #endif /* SQLITE_ENABLE_RTREE */ 1308 1309 /* 1310 ** A testset that does key/value storage on tables with many columns. 1311 ** This is the kind of workload generated by ORMs such as CoreData. 1312 */ 1313 void testset_orm(void){ 1314 unsigned i, j, n; 1315 unsigned nRow; 1316 unsigned x1, len; 1317 char zNum[2000]; /* A number name */ 1318 static const char zType[] = /* Types for all non-PK columns, in order */ 1319 "IBBIIITIVVITBTBFBFITTFBTBVBVIFTBBFITFFVBIFIVBVVVBTVTIBBFFIVIBTB" 1320 "TVTTFTVTVFFIITIFBITFTTFFFVBIIBTTITFTFFVVVFIIITVBBVFFTVVB"; 1321 1322 nRow = n = g.szTest*250; 1323 speedtest1_begin_test(100, "Fill %d rows", n); 1324 speedtest1_exec( 1325 "BEGIN;" 1326 "CREATE TABLE ZLOOKSLIKECOREDATA (" 1327 " ZPK INTEGER PRIMARY KEY," 1328 " ZTERMFITTINGHOUSINGCOMMAND INTEGER," 1329 " ZBRIEFGOBYDODGERHEIGHT BLOB," 1330 " ZCAPABLETRIPDOORALMOND BLOB," 1331 " ZDEPOSITPAIRCOLLEGECOMET INTEGER," 1332 " ZFRAMEENTERSIMPLEMOUTH INTEGER," 1333 " ZHOPEFULGATEHOLECHALK INTEGER," 1334 " ZSLEEPYUSERGRANDBOWL TIMESTAMP," 1335 " ZDEWPEACHCAREERCELERY INTEGER," 1336 " ZHANGERLITHIUMDINNERMEET VARCHAR," 1337 " ZCLUBRELEASELIZARDADVICE VARCHAR," 1338 " ZCHARGECLICKHUMANEHIRE INTEGER," 1339 " ZFINGERDUEPIZZAOPTION TIMESTAMP," 1340 " ZFLYINGDOCTORTABLEMELODY BLOB," 1341 " ZLONGFINLEAVEIMAGEOIL TIMESTAMP," 1342 " ZFAMILYVISUALOWNERMATTER BLOB," 1343 " ZGOLDYOUNGINITIALNOSE FLOAT," 1344 " ZCAUSESALAMITERMCYAN BLOB," 1345 " ZSPREADMOTORBISCUITBACON FLOAT," 1346 " ZGIFTICEFISHGLUEHAIR INTEGER," 1347 " ZNOTICEPEARPOLICYJUICE TIMESTAMP," 1348 " ZBANKBUFFALORECOVERORBIT TIMESTAMP," 1349 " ZLONGDIETESSAYNATURE FLOAT," 1350 " ZACTIONRANGEELEGANTNEUTRON BLOB," 1351 " ZCADETBRIGHTPLANETBANK TIMESTAMP," 1352 " ZAIRFORGIVEHEADFROG BLOB," 1353 " ZSHARKJUSTFRUITMOVIE VARCHAR," 1354 " ZFARMERMORNINGMIRRORCONCERN BLOB," 1355 " ZWOODPOETRYCOBBLERBENCH VARCHAR," 1356 " ZHAFNIUMSCRIPTSALADMOTOR INTEGER," 1357 " ZPROBLEMCLUBPOPOVERJELLY FLOAT," 1358 " ZEIGHTLEADERWORKERMOST TIMESTAMP," 1359 " ZGLASSRESERVEBARIUMMEAL BLOB," 1360 " ZCLAMBITARUGULAFAJITA BLOB," 1361 " ZDECADEJOYOUSWAVEHABIT FLOAT," 1362 " ZCOMPANYSUMMERFIBERELF INTEGER," 1363 " ZTREATTESTQUILLCHARGE TIMESTAMP," 1364 " ZBROWBALANCEKEYCHOWDER FLOAT," 1365 " ZPEACHCOPPERDINNERLAKE FLOAT," 1366 " ZDRYWALLBEYONDBROWNBOWL VARCHAR," 1367 " ZBELLYCRASHITEMLACK BLOB," 1368 " ZTENNISCYCLEBILLOFFICER INTEGER," 1369 " ZMALLEQUIPTHANKSGLUE FLOAT," 1370 " ZMISSREPLYHUMANLIVING INTEGER," 1371 " ZKIWIVISUALPRIDEAPPLE VARCHAR," 1372 " ZWISHHITSKINMOTOR BLOB," 1373 " ZCALMRACCOONPROGRAMDEBIT VARCHAR," 1374 " ZSHINYASSISTLIVINGCRAB VARCHAR," 1375 " ZRESOLVEWRISTWRAPAPPLE VARCHAR," 1376 " ZAPPEALSIMPLESECONDHOUSING BLOB," 1377 " ZCORNERANCHORTAPEDIVER TIMESTAMP," 1378 " ZMEMORYREQUESTSOURCEBIG VARCHAR," 1379 " ZTRYFACTKEEPMILK TIMESTAMP," 1380 " ZDIVERPAINTLEATHEREASY INTEGER," 1381 " ZSORTMISTYQUOTECABBAGE BLOB," 1382 " ZTUNEGASBUFFALOCAPITAL BLOB," 1383 " ZFILLSTOPLAWJOYFUL FLOAT," 1384 " ZSTEELCAREFULPLATENUMBER FLOAT," 1385 " ZGIVEVIVIDDIVINEMEANING INTEGER," 1386 " ZTREATPACKFUTURECONVERT VARCHAR," 1387 " ZCALMLYGEMFINISHEFFECT INTEGER," 1388 " ZCABBAGESOCKEASEMINUTE BLOB," 1389 " ZPLANETFAMILYPUREMEMORY TIMESTAMP," 1390 " ZMERRYCRACKTRAINLEADER BLOB," 1391 " ZMINORWAYPAPERCLASSY TIMESTAMP," 1392 " ZEAGLELINEMINEMAIL VARCHAR," 1393 " ZRESORTYARDGREENLET TIMESTAMP," 1394 " ZYARDOREGANOVIVIDJEWEL TIMESTAMP," 1395 " ZPURECAKEVIVIDNEATLY FLOAT," 1396 " ZASKCONTACTMONITORFUN TIMESTAMP," 1397 " ZMOVEWHOGAMMAINCH VARCHAR," 1398 " ZLETTUCEBIRDMEETDEBATE TIMESTAMP," 1399 " ZGENENATURALHEARINGKITE VARCHAR," 1400 " ZMUFFINDRYERDRAWFORTUNE FLOAT," 1401 " ZGRAYSURVEYWIRELOVE FLOAT," 1402 " ZPLIERSPRINTASKOREGANO INTEGER," 1403 " ZTRAVELDRIVERCONTESTLILY INTEGER," 1404 " ZHUMORSPICESANDKIDNEY TIMESTAMP," 1405 " ZARSENICSAMPLEWAITMUON INTEGER," 1406 " ZLACEADDRESSGROUNDCAREFUL FLOAT," 1407 " ZBAMBOOMESSWASABIEVENING BLOB," 1408 " ZONERELEASEAVERAGENURSE INTEGER," 1409 " ZRADIANTWHENTRYCARD TIMESTAMP," 1410 " ZREWARDINSIDEMANGOINTENSE FLOAT," 1411 " ZNEATSTEWPARTIRON TIMESTAMP," 1412 " ZOUTSIDEPEAHENCOUNTICE TIMESTAMP," 1413 " ZCREAMEVENINGLIPBRANCH FLOAT," 1414 " ZWHALEMATHAVOCADOCOPPER FLOAT," 1415 " ZLIFEUSELEAFYBELL FLOAT," 1416 " ZWEALTHLINENGLEEFULDAY VARCHAR," 1417 " ZFACEINVITETALKGOLD BLOB," 1418 " ZWESTAMOUNTAFFECTHEARING INTEGER," 1419 " ZDELAYOUTCOMEHORNAGENCY INTEGER," 1420 " ZBIGTHINKCONVERTECONOMY BLOB," 1421 " ZBASEGOUDAREGULARFORGIVE TIMESTAMP," 1422 " ZPATTERNCLORINEGRANDCOLBY TIMESTAMP," 1423 " ZCYANBASEFEEDADROIT INTEGER," 1424 " ZCARRYFLOORMINNOWDRAGON TIMESTAMP," 1425 " ZIMAGEPENCILOTHERBOTTOM FLOAT," 1426 " ZXENONFLIGHTPALEAPPLE TIMESTAMP," 1427 " ZHERRINGJOKEFEATUREHOPEFUL FLOAT," 1428 " ZCAPYEARLYRIVETBRUSH FLOAT," 1429 " ZAGEREEDFROGBASKET VARCHAR," 1430 " ZUSUALBODYHALIBUTDIAMOND VARCHAR," 1431 " ZFOOTTAPWORDENTRY VARCHAR," 1432 " ZDISHKEEPBLESTMONITOR FLOAT," 1433 " ZBROADABLESOLIDCASUAL INTEGER," 1434 " ZSQUAREGLEEFULCHILDLIGHT INTEGER," 1435 " ZHOLIDAYHEADPONYDETAIL INTEGER," 1436 " ZGENERALRESORTSKYOPEN TIMESTAMP," 1437 " ZGLADSPRAYKIDNEYGUPPY VARCHAR," 1438 " ZSWIMHEAVYMENTIONKIND BLOB," 1439 " ZMESSYSULFURDREAMFESTIVE BLOB," 1440 " ZSKYSKYCLASSICBRIEF VARCHAR," 1441 " ZDILLASKHOKILEMON FLOAT," 1442 " ZJUNIORSHOWPRESSNOVA FLOAT," 1443 " ZSIZETOEAWARDFRESH TIMESTAMP," 1444 " ZKEYFAILAPRICOTMETAL VARCHAR," 1445 " ZHANDYREPAIRPROTONAIRPORT VARCHAR," 1446 " ZPOSTPROTEINHANDLEACTOR BLOB" 1447 ");" 1448 ); 1449 speedtest1_prepare( 1450 "INSERT INTO ZLOOKSLIKECOREDATA(ZPK,ZAIRFORGIVEHEADFROG," 1451 "ZGIFTICEFISHGLUEHAIR,ZDELAYOUTCOMEHORNAGENCY,ZSLEEPYUSERGRANDBOWL," 1452 "ZGLASSRESERVEBARIUMMEAL,ZBRIEFGOBYDODGERHEIGHT," 1453 "ZBAMBOOMESSWASABIEVENING,ZFARMERMORNINGMIRRORCONCERN," 1454 "ZTREATPACKFUTURECONVERT,ZCAUSESALAMITERMCYAN,ZCALMRACCOONPROGRAMDEBIT," 1455 "ZHOLIDAYHEADPONYDETAIL,ZWOODPOETRYCOBBLERBENCH,ZHAFNIUMSCRIPTSALADMOTOR," 1456 "ZUSUALBODYHALIBUTDIAMOND,ZOUTSIDEPEAHENCOUNTICE,ZDIVERPAINTLEATHEREASY," 1457 "ZWESTAMOUNTAFFECTHEARING,ZSIZETOEAWARDFRESH,ZDEWPEACHCAREERCELERY," 1458 "ZSTEELCAREFULPLATENUMBER,ZCYANBASEFEEDADROIT,ZCALMLYGEMFINISHEFFECT," 1459 "ZHANDYREPAIRPROTONAIRPORT,ZGENENATURALHEARINGKITE,ZBROADABLESOLIDCASUAL," 1460 "ZPOSTPROTEINHANDLEACTOR,ZLACEADDRESSGROUNDCAREFUL,ZIMAGEPENCILOTHERBOTTOM," 1461 "ZPROBLEMCLUBPOPOVERJELLY,ZPATTERNCLORINEGRANDCOLBY,ZNEATSTEWPARTIRON," 1462 "ZAPPEALSIMPLESECONDHOUSING,ZMOVEWHOGAMMAINCH,ZTENNISCYCLEBILLOFFICER," 1463 "ZSHARKJUSTFRUITMOVIE,ZKEYFAILAPRICOTMETAL,ZCOMPANYSUMMERFIBERELF," 1464 "ZTERMFITTINGHOUSINGCOMMAND,ZRESORTYARDGREENLET,ZCABBAGESOCKEASEMINUTE," 1465 "ZSQUAREGLEEFULCHILDLIGHT,ZONERELEASEAVERAGENURSE,ZBIGTHINKCONVERTECONOMY," 1466 "ZPLIERSPRINTASKOREGANO,ZDECADEJOYOUSWAVEHABIT,ZDRYWALLBEYONDBROWNBOWL," 1467 "ZCLUBRELEASELIZARDADVICE,ZWHALEMATHAVOCADOCOPPER,ZBELLYCRASHITEMLACK," 1468 "ZLETTUCEBIRDMEETDEBATE,ZCAPABLETRIPDOORALMOND,ZRADIANTWHENTRYCARD," 1469 "ZCAPYEARLYRIVETBRUSH,ZAGEREEDFROGBASKET,ZSWIMHEAVYMENTIONKIND," 1470 "ZTRAVELDRIVERCONTESTLILY,ZGLADSPRAYKIDNEYGUPPY,ZBANKBUFFALORECOVERORBIT," 1471 "ZFINGERDUEPIZZAOPTION,ZCLAMBITARUGULAFAJITA,ZLONGFINLEAVEIMAGEOIL," 1472 "ZLONGDIETESSAYNATURE,ZJUNIORSHOWPRESSNOVA,ZHOPEFULGATEHOLECHALK," 1473 "ZDEPOSITPAIRCOLLEGECOMET,ZWEALTHLINENGLEEFULDAY,ZFILLSTOPLAWJOYFUL," 1474 "ZTUNEGASBUFFALOCAPITAL,ZGRAYSURVEYWIRELOVE,ZCORNERANCHORTAPEDIVER," 1475 "ZREWARDINSIDEMANGOINTENSE,ZCADETBRIGHTPLANETBANK,ZPLANETFAMILYPUREMEMORY," 1476 "ZTREATTESTQUILLCHARGE,ZCREAMEVENINGLIPBRANCH,ZSKYSKYCLASSICBRIEF," 1477 "ZARSENICSAMPLEWAITMUON,ZBROWBALANCEKEYCHOWDER,ZFLYINGDOCTORTABLEMELODY," 1478 "ZHANGERLITHIUMDINNERMEET,ZNOTICEPEARPOLICYJUICE,ZSHINYASSISTLIVINGCRAB," 1479 "ZLIFEUSELEAFYBELL,ZFACEINVITETALKGOLD,ZGENERALRESORTSKYOPEN," 1480 "ZPURECAKEVIVIDNEATLY,ZKIWIVISUALPRIDEAPPLE,ZMESSYSULFURDREAMFESTIVE," 1481 "ZCHARGECLICKHUMANEHIRE,ZHERRINGJOKEFEATUREHOPEFUL,ZYARDOREGANOVIVIDJEWEL," 1482 "ZFOOTTAPWORDENTRY,ZWISHHITSKINMOTOR,ZBASEGOUDAREGULARFORGIVE," 1483 "ZMUFFINDRYERDRAWFORTUNE,ZACTIONRANGEELEGANTNEUTRON,ZTRYFACTKEEPMILK," 1484 "ZPEACHCOPPERDINNERLAKE,ZFRAMEENTERSIMPLEMOUTH,ZMERRYCRACKTRAINLEADER," 1485 "ZMEMORYREQUESTSOURCEBIG,ZCARRYFLOORMINNOWDRAGON,ZMINORWAYPAPERCLASSY," 1486 "ZDILLASKHOKILEMON,ZRESOLVEWRISTWRAPAPPLE,ZASKCONTACTMONITORFUN," 1487 "ZGIVEVIVIDDIVINEMEANING,ZEIGHTLEADERWORKERMOST,ZMISSREPLYHUMANLIVING," 1488 "ZXENONFLIGHTPALEAPPLE,ZSORTMISTYQUOTECABBAGE,ZEAGLELINEMINEMAIL," 1489 "ZFAMILYVISUALOWNERMATTER,ZSPREADMOTORBISCUITBACON,ZDISHKEEPBLESTMONITOR," 1490 "ZMALLEQUIPTHANKSGLUE,ZGOLDYOUNGINITIALNOSE,ZHUMORSPICESANDKIDNEY)" 1491 "VALUES(?1,?26,?20,?93,?8,?33,?3,?81,?28,?60,?18,?47,?109,?29,?30,?104,?86," 1492 "?54,?92,?117,?9,?58,?97,?61,?119,?73,?107,?120,?80,?99,?31,?96,?85,?50,?71," 1493 "?42,?27,?118,?36,?2,?67,?62,?108,?82,?94,?76,?35,?40,?11,?88,?41,?72,?4," 1494 "?83,?102,?103,?112,?77,?111,?22,?13,?34,?15,?23,?116,?7,?5,?90,?57,?56," 1495 "?75,?51,?84,?25,?63,?37,?87,?114,?79,?38,?14,?10,?21,?48,?89,?91,?110," 1496 "?69,?45,?113,?12,?101,?68,?105,?46,?95,?74,?24,?53,?39,?6,?64,?52,?98," 1497 "?65,?115,?49,?70,?59,?32,?44,?100,?55,?66,?16,?19,?106,?43,?17,?78);" 1498 ); 1499 for(i=0; i<n; i++){ 1500 x1 = speedtest1_random(); 1501 speedtest1_numbername(x1%1000, zNum, sizeof(zNum)); 1502 len = (int)strlen(zNum); 1503 sqlite3_bind_int(g.pStmt, 1, i^0xf); 1504 for(j=0; zType[j]; j++){ 1505 switch( zType[j] ){ 1506 case 'I': 1507 case 'T': 1508 sqlite3_bind_int64(g.pStmt, j+2, x1); 1509 break; 1510 case 'F': 1511 sqlite3_bind_double(g.pStmt, j+2, (double)x1); 1512 break; 1513 case 'V': 1514 case 'B': 1515 sqlite3_bind_text64(g.pStmt, j+2, zNum, len, 1516 SQLITE_STATIC, SQLITE_UTF8); 1517 break; 1518 } 1519 } 1520 speedtest1_run(); 1521 } 1522 speedtest1_exec("COMMIT;"); 1523 speedtest1_end_test(); 1524 1525 n = g.szTest*250; 1526 speedtest1_begin_test(110, "Query %d rows by rowid", n); 1527 speedtest1_prepare( 1528 "SELECT ZCYANBASEFEEDADROIT,ZJUNIORSHOWPRESSNOVA,ZCAUSESALAMITERMCYAN," 1529 "ZHOPEFULGATEHOLECHALK,ZHUMORSPICESANDKIDNEY,ZSWIMHEAVYMENTIONKIND," 1530 "ZMOVEWHOGAMMAINCH,ZAPPEALSIMPLESECONDHOUSING,ZHAFNIUMSCRIPTSALADMOTOR," 1531 "ZNEATSTEWPARTIRON,ZLONGFINLEAVEIMAGEOIL,ZDEWPEACHCAREERCELERY," 1532 "ZXENONFLIGHTPALEAPPLE,ZCALMRACCOONPROGRAMDEBIT,ZUSUALBODYHALIBUTDIAMOND," 1533 "ZTRYFACTKEEPMILK,ZWEALTHLINENGLEEFULDAY,ZLONGDIETESSAYNATURE," 1534 "ZLIFEUSELEAFYBELL,ZTREATPACKFUTURECONVERT,ZMEMORYREQUESTSOURCEBIG," 1535 "ZYARDOREGANOVIVIDJEWEL,ZDEPOSITPAIRCOLLEGECOMET,ZSLEEPYUSERGRANDBOWL," 1536 "ZBRIEFGOBYDODGERHEIGHT,ZCLUBRELEASELIZARDADVICE,ZCAPABLETRIPDOORALMOND," 1537 "ZDRYWALLBEYONDBROWNBOWL,ZASKCONTACTMONITORFUN,ZKIWIVISUALPRIDEAPPLE," 1538 "ZNOTICEPEARPOLICYJUICE,ZPEACHCOPPERDINNERLAKE,ZSTEELCAREFULPLATENUMBER," 1539 "ZGLADSPRAYKIDNEYGUPPY,ZCOMPANYSUMMERFIBERELF,ZTENNISCYCLEBILLOFFICER," 1540 "ZIMAGEPENCILOTHERBOTTOM,ZWESTAMOUNTAFFECTHEARING,ZDIVERPAINTLEATHEREASY," 1541 "ZSKYSKYCLASSICBRIEF,ZMESSYSULFURDREAMFESTIVE,ZMERRYCRACKTRAINLEADER," 1542 "ZBROADABLESOLIDCASUAL,ZGLASSRESERVEBARIUMMEAL,ZTUNEGASBUFFALOCAPITAL," 1543 "ZBANKBUFFALORECOVERORBIT,ZTREATTESTQUILLCHARGE,ZBAMBOOMESSWASABIEVENING," 1544 "ZREWARDINSIDEMANGOINTENSE,ZEAGLELINEMINEMAIL,ZCALMLYGEMFINISHEFFECT," 1545 "ZKEYFAILAPRICOTMETAL,ZFINGERDUEPIZZAOPTION,ZCADETBRIGHTPLANETBANK," 1546 "ZGOLDYOUNGINITIALNOSE,ZMISSREPLYHUMANLIVING,ZEIGHTLEADERWORKERMOST," 1547 "ZFRAMEENTERSIMPLEMOUTH,ZBIGTHINKCONVERTECONOMY,ZFACEINVITETALKGOLD," 1548 "ZPOSTPROTEINHANDLEACTOR,ZHERRINGJOKEFEATUREHOPEFUL,ZCABBAGESOCKEASEMINUTE," 1549 "ZMUFFINDRYERDRAWFORTUNE,ZPROBLEMCLUBPOPOVERJELLY,ZGIVEVIVIDDIVINEMEANING," 1550 "ZGENENATURALHEARINGKITE,ZGENERALRESORTSKYOPEN,ZLETTUCEBIRDMEETDEBATE," 1551 "ZBASEGOUDAREGULARFORGIVE,ZCHARGECLICKHUMANEHIRE,ZPLANETFAMILYPUREMEMORY," 1552 "ZMINORWAYPAPERCLASSY,ZCAPYEARLYRIVETBRUSH,ZSIZETOEAWARDFRESH," 1553 "ZARSENICSAMPLEWAITMUON,ZSQUAREGLEEFULCHILDLIGHT,ZSHINYASSISTLIVINGCRAB," 1554 "ZCORNERANCHORTAPEDIVER,ZDECADEJOYOUSWAVEHABIT,ZTRAVELDRIVERCONTESTLILY," 1555 "ZFLYINGDOCTORTABLEMELODY,ZSHARKJUSTFRUITMOVIE,ZFAMILYVISUALOWNERMATTER," 1556 "ZFARMERMORNINGMIRRORCONCERN,ZGIFTICEFISHGLUEHAIR,ZOUTSIDEPEAHENCOUNTICE," 1557 "ZSPREADMOTORBISCUITBACON,ZWISHHITSKINMOTOR,ZHOLIDAYHEADPONYDETAIL," 1558 "ZWOODPOETRYCOBBLERBENCH,ZAIRFORGIVEHEADFROG,ZBROWBALANCEKEYCHOWDER," 1559 "ZDISHKEEPBLESTMONITOR,ZCLAMBITARUGULAFAJITA,ZPLIERSPRINTASKOREGANO," 1560 "ZRADIANTWHENTRYCARD,ZDELAYOUTCOMEHORNAGENCY,ZPURECAKEVIVIDNEATLY," 1561 "ZPATTERNCLORINEGRANDCOLBY,ZHANDYREPAIRPROTONAIRPORT,ZAGEREEDFROGBASKET," 1562 "ZSORTMISTYQUOTECABBAGE,ZFOOTTAPWORDENTRY,ZRESOLVEWRISTWRAPAPPLE," 1563 "ZDILLASKHOKILEMON,ZFILLSTOPLAWJOYFUL,ZACTIONRANGEELEGANTNEUTRON," 1564 "ZRESORTYARDGREENLET,ZCREAMEVENINGLIPBRANCH,ZWHALEMATHAVOCADOCOPPER," 1565 "ZGRAYSURVEYWIRELOVE,ZBELLYCRASHITEMLACK,ZHANGERLITHIUMDINNERMEET," 1566 "ZCARRYFLOORMINNOWDRAGON,ZMALLEQUIPTHANKSGLUE,ZTERMFITTINGHOUSINGCOMMAND," 1567 "ZONERELEASEAVERAGENURSE,ZLACEADDRESSGROUNDCAREFUL" 1568 " FROM ZLOOKSLIKECOREDATA WHERE ZPK=?1;" 1569 ); 1570 for(i=0; i<n; i++){ 1571 x1 = speedtest1_random()%nRow; 1572 sqlite3_bind_int(g.pStmt, 1, x1); 1573 speedtest1_run(); 1574 } 1575 speedtest1_end_test(); 1576 } 1577 1578 /* 1579 ** A testset used for debugging speedtest1 itself. 1580 */ 1581 void testset_debug1(void){ 1582 unsigned i, n; 1583 unsigned x1, x2; 1584 char zNum[2000]; /* A number name */ 1585 1586 n = g.szTest; 1587 for(i=1; i<=n; i++){ 1588 x1 = swizzle(i, n); 1589 x2 = swizzle(x1, n); 1590 speedtest1_numbername(x1, zNum, sizeof(zNum)); 1591 printf("%5d %5d %5d %s\n", i, x1, x2, zNum); 1592 } 1593 } 1594 1595 #ifdef __linux__ 1596 #include <sys/types.h> 1597 #include <unistd.h> 1598 1599 /* 1600 ** Attempt to display I/O stats on Linux using /proc/PID/io 1601 */ 1602 static void displayLinuxIoStats(FILE *out){ 1603 FILE *in; 1604 char z[200]; 1605 sqlite3_snprintf(sizeof(z), z, "/proc/%d/io", getpid()); 1606 in = fopen(z, "rb"); 1607 if( in==0 ) return; 1608 while( fgets(z, sizeof(z), in)!=0 ){ 1609 static const struct { 1610 const char *zPattern; 1611 const char *zDesc; 1612 } aTrans[] = { 1613 { "rchar: ", "Bytes received by read():" }, 1614 { "wchar: ", "Bytes sent to write():" }, 1615 { "syscr: ", "Read() system calls:" }, 1616 { "syscw: ", "Write() system calls:" }, 1617 { "read_bytes: ", "Bytes rcvd from storage:" }, 1618 { "write_bytes: ", "Bytes sent to storage:" }, 1619 { "cancelled_write_bytes: ", "Cancelled write bytes:" }, 1620 }; 1621 int i; 1622 for(i=0; i<sizeof(aTrans)/sizeof(aTrans[0]); i++){ 1623 int n = (int)strlen(aTrans[i].zPattern); 1624 if( strncmp(aTrans[i].zPattern, z, n)==0 ){ 1625 fprintf(out, "-- %-28s %s", aTrans[i].zDesc, &z[n]); 1626 break; 1627 } 1628 } 1629 } 1630 fclose(in); 1631 } 1632 #endif 1633 1634 #if SQLITE_VERSION_NUMBER<3006018 1635 # define sqlite3_sourceid(X) "(before 3.6.18)" 1636 #endif 1637 1638 static int xCompileOptions(void *pCtx, int nVal, char **azVal, char **azCol){ 1639 printf("-- Compile option: %s\n", azVal[0]); 1640 return SQLITE_OK; 1641 } 1642 1643 int main(int argc, char **argv){ 1644 int doAutovac = 0; /* True for --autovacuum */ 1645 int cacheSize = 0; /* Desired cache size. 0 means default */ 1646 int doExclusive = 0; /* True for --exclusive */ 1647 int nHeap = 0, mnHeap = 0; /* Heap size from --heap */ 1648 int doIncrvac = 0; /* True for --incrvacuum */ 1649 const char *zJMode = 0; /* Journal mode */ 1650 const char *zKey = 0; /* Encryption key */ 1651 int nLook = -1, szLook = 0; /* --lookaside configuration */ 1652 int noSync = 0; /* True for --nosync */ 1653 int pageSize = 0; /* Desired page size. 0 means default */ 1654 int nPCache = 0, szPCache = 0;/* --pcache configuration */ 1655 int doPCache = 0; /* True if --pcache is seen */ 1656 int showStats = 0; /* True for --stats */ 1657 int nThread = 0; /* --threads value */ 1658 int mmapSize = 0; /* How big of a memory map to use */ 1659 const char *zTSet = "main"; /* Which --testset torun */ 1660 int doTrace = 0; /* True for --trace */ 1661 const char *zEncoding = 0; /* --utf16be or --utf16le */ 1662 const char *zDbName = 0; /* Name of the test database */ 1663 1664 void *pHeap = 0; /* Allocated heap space */ 1665 void *pLook = 0; /* Allocated lookaside space */ 1666 void *pPCache = 0; /* Allocated storage for pcache */ 1667 int iCur, iHi; /* Stats values, current and "highwater" */ 1668 int i; /* Loop counter */ 1669 int rc; /* API return code */ 1670 1671 /* Display the version of SQLite being tested */ 1672 printf("-- Speedtest1 for SQLite %s %.50s\n", 1673 sqlite3_libversion(), sqlite3_sourceid()); 1674 1675 /* Process command-line arguments */ 1676 g.zWR = ""; 1677 g.zNN = ""; 1678 g.zPK = "UNIQUE"; 1679 g.szTest = 100; 1680 g.nRepeat = 1; 1681 for(i=1; i<argc; i++){ 1682 const char *z = argv[i]; 1683 if( z[0]=='-' ){ 1684 do{ z++; }while( z[0]=='-' ); 1685 if( strcmp(z,"autovacuum")==0 ){ 1686 doAutovac = 1; 1687 }else if( strcmp(z,"cachesize")==0 ){ 1688 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1689 i++; 1690 cacheSize = integerValue(argv[i]); 1691 }else if( strcmp(z,"exclusive")==0 ){ 1692 doExclusive = 1; 1693 }else if( strcmp(z,"explain")==0 ){ 1694 g.bSqlOnly = 1; 1695 g.bExplain = 1; 1696 }else if( strcmp(z,"heap")==0 ){ 1697 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]); 1698 nHeap = integerValue(argv[i+1]); 1699 mnHeap = integerValue(argv[i+2]); 1700 i += 2; 1701 }else if( strcmp(z,"incrvacuum")==0 ){ 1702 doIncrvac = 1; 1703 }else if( strcmp(z,"journal")==0 ){ 1704 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1705 zJMode = argv[++i]; 1706 }else if( strcmp(z,"key")==0 ){ 1707 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1708 zKey = argv[++i]; 1709 }else if( strcmp(z,"lookaside")==0 ){ 1710 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]); 1711 nLook = integerValue(argv[i+1]); 1712 szLook = integerValue(argv[i+2]); 1713 i += 2; 1714 #if SQLITE_VERSION_NUMBER>=3006000 1715 }else if( strcmp(z,"multithread")==0 ){ 1716 sqlite3_config(SQLITE_CONFIG_MULTITHREAD); 1717 }else if( strcmp(z,"nomemstat")==0 ){ 1718 sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0); 1719 #endif 1720 #if SQLITE_VERSION_NUMBER>=3007017 1721 }else if( strcmp(z, "mmap")==0 ){ 1722 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1723 mmapSize = integerValue(argv[++i]); 1724 #endif 1725 }else if( strcmp(z,"nosync")==0 ){ 1726 noSync = 1; 1727 }else if( strcmp(z,"notnull")==0 ){ 1728 g.zNN = "NOT NULL"; 1729 }else if( strcmp(z,"pagesize")==0 ){ 1730 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1731 pageSize = integerValue(argv[++i]); 1732 }else if( strcmp(z,"pcache")==0 ){ 1733 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]); 1734 nPCache = integerValue(argv[i+1]); 1735 szPCache = integerValue(argv[i+2]); 1736 doPCache = 1; 1737 i += 2; 1738 }else if( strcmp(z,"primarykey")==0 ){ 1739 g.zPK = "PRIMARY KEY"; 1740 }else if( strcmp(z,"repeat")==0 ){ 1741 if( i>=argc-1 ) fatal_error("missing arguments on %s\n", argv[i]); 1742 g.nRepeat = integerValue(argv[i+1]); 1743 i += 1; 1744 }else if( strcmp(z,"reprepare")==0 ){ 1745 g.bReprepare = 1; 1746 #if SQLITE_VERSION_NUMBER>=3006000 1747 }else if( strcmp(z,"serialized")==0 ){ 1748 sqlite3_config(SQLITE_CONFIG_SERIALIZED); 1749 }else if( strcmp(z,"singlethread")==0 ){ 1750 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); 1751 #endif 1752 }else if( strcmp(z,"sqlonly")==0 ){ 1753 g.bSqlOnly = 1; 1754 }else if( strcmp(z,"shrink-memory")==0 ){ 1755 g.bMemShrink = 1; 1756 }else if( strcmp(z,"size")==0 ){ 1757 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1758 g.szTest = integerValue(argv[++i]); 1759 }else if( strcmp(z,"stats")==0 ){ 1760 showStats = 1; 1761 }else if( strcmp(z,"temp")==0 ){ 1762 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1763 i++; 1764 if( argv[i][0]<'0' || argv[i][0]>'9' || argv[i][1]!=0 ){ 1765 fatal_error("argument to --temp should be integer between 0 and 9"); 1766 } 1767 g.eTemp = argv[i][0] - '0'; 1768 }else if( strcmp(z,"testset")==0 ){ 1769 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1770 zTSet = argv[++i]; 1771 }else if( strcmp(z,"trace")==0 ){ 1772 doTrace = 1; 1773 }else if( strcmp(z,"threads")==0 ){ 1774 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1775 nThread = integerValue(argv[++i]); 1776 }else if( strcmp(z,"utf16le")==0 ){ 1777 zEncoding = "utf16le"; 1778 }else if( strcmp(z,"utf16be")==0 ){ 1779 zEncoding = "utf16be"; 1780 }else if( strcmp(z,"verify")==0 ){ 1781 g.bVerify = 1; 1782 }else if( strcmp(z,"without-rowid")==0 ){ 1783 g.zWR = "WITHOUT ROWID"; 1784 g.zPK = "PRIMARY KEY"; 1785 }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){ 1786 printf(zHelp, argv[0]); 1787 exit(0); 1788 }else{ 1789 fatal_error("unknown option: %s\nUse \"%s -?\" for help\n", 1790 argv[i], argv[0]); 1791 } 1792 }else if( zDbName==0 ){ 1793 zDbName = argv[i]; 1794 }else{ 1795 fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n", 1796 argv[i], argv[0]); 1797 } 1798 } 1799 if( zDbName!=0 ) unlink(zDbName); 1800 #if SQLITE_VERSION_NUMBER>=3006001 1801 if( nHeap>0 ){ 1802 pHeap = malloc( nHeap ); 1803 if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap); 1804 rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap); 1805 if( rc ) fatal_error("heap configuration failed: %d\n", rc); 1806 } 1807 if( doPCache ){ 1808 if( nPCache>0 && szPCache>0 ){ 1809 pPCache = malloc( nPCache*(sqlite3_int64)szPCache ); 1810 if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n", 1811 nPCache*(sqlite3_int64)szPCache); 1812 } 1813 rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache); 1814 if( rc ) fatal_error("pcache configuration failed: %d\n", rc); 1815 } 1816 if( nLook>=0 ){ 1817 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0); 1818 } 1819 #endif 1820 1821 /* Open the database and the input file */ 1822 if( sqlite3_open(zDbName, &g.db) ){ 1823 fatal_error("Cannot open database file: %s\n", zDbName); 1824 } 1825 #if SQLITE_VERSION_NUMBER>=3006001 1826 if( nLook>0 && szLook>0 ){ 1827 pLook = malloc( nLook*szLook ); 1828 rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE, pLook, szLook,nLook); 1829 if( rc ) fatal_error("lookaside configuration failed: %d\n", rc); 1830 } 1831 #endif 1832 1833 /* Set database connection options */ 1834 sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc, 0, 0); 1835 #ifndef SQLITE_OMIT_DEPRECATED 1836 if( doTrace ) sqlite3_trace(g.db, traceCallback, 0); 1837 #endif 1838 if( mmapSize>0 ){ 1839 speedtest1_exec("PRAGMA mmap_size=%d", mmapSize); 1840 } 1841 speedtest1_exec("PRAGMA threads=%d", nThread); 1842 if( zKey ){ 1843 speedtest1_exec("PRAGMA key('%s')", zKey); 1844 } 1845 if( zEncoding ){ 1846 speedtest1_exec("PRAGMA encoding=%s", zEncoding); 1847 } 1848 if( doAutovac ){ 1849 speedtest1_exec("PRAGMA auto_vacuum=FULL"); 1850 }else if( doIncrvac ){ 1851 speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL"); 1852 } 1853 if( pageSize ){ 1854 speedtest1_exec("PRAGMA page_size=%d", pageSize); 1855 } 1856 if( cacheSize ){ 1857 speedtest1_exec("PRAGMA cache_size=%d", cacheSize); 1858 } 1859 if( noSync ) speedtest1_exec("PRAGMA synchronous=OFF"); 1860 if( doExclusive ){ 1861 speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE"); 1862 } 1863 if( zJMode ){ 1864 speedtest1_exec("PRAGMA journal_mode=%s", zJMode); 1865 } 1866 1867 if( g.bExplain ) printf(".explain\n.echo on\n"); 1868 if( strcmp(zTSet,"main")==0 ){ 1869 testset_main(); 1870 }else if( strcmp(zTSet,"debug1")==0 ){ 1871 testset_debug1(); 1872 }else if( strcmp(zTSet,"orm")==0 ){ 1873 testset_orm(); 1874 }else if( strcmp(zTSet,"cte")==0 ){ 1875 testset_cte(); 1876 }else if( strcmp(zTSet,"rtree")==0 ){ 1877 #ifdef SQLITE_ENABLE_RTREE 1878 testset_rtree(6, 147); 1879 #else 1880 fatal_error("compile with -DSQLITE_ENABLE_RTREE to enable " 1881 "the R-Tree tests\n"); 1882 #endif 1883 }else{ 1884 fatal_error("unknown testset: \"%s\"\nChoices: main debug1 cte rtree\n", 1885 zTSet); 1886 } 1887 speedtest1_final(); 1888 1889 if( showStats ){ 1890 sqlite3_exec(g.db, "PRAGMA compile_options", xCompileOptions, 0, 0); 1891 } 1892 1893 /* Database connection statistics printed after both prepared statements 1894 ** have been finalized */ 1895 #if SQLITE_VERSION_NUMBER>=3007009 1896 if( showStats ){ 1897 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0); 1898 printf("-- Lookaside Slots Used: %d (max %d)\n", iCur,iHi); 1899 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0); 1900 printf("-- Successful lookasides: %d\n", iHi); 1901 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0); 1902 printf("-- Lookaside size faults: %d\n", iHi); 1903 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0); 1904 printf("-- Lookaside OOM faults: %d\n", iHi); 1905 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0); 1906 printf("-- Pager Heap Usage: %d bytes\n", iCur); 1907 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1); 1908 printf("-- Page cache hits: %d\n", iCur); 1909 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1); 1910 printf("-- Page cache misses: %d\n", iCur); 1911 #if SQLITE_VERSION_NUMBER>=3007012 1912 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1); 1913 printf("-- Page cache writes: %d\n", iCur); 1914 #endif 1915 sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0); 1916 printf("-- Schema Heap Usage: %d bytes\n", iCur); 1917 sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0); 1918 printf("-- Statement Heap Usage: %d bytes\n", iCur); 1919 } 1920 #endif 1921 1922 sqlite3_close(g.db); 1923 1924 #if SQLITE_VERSION_NUMBER>=3006001 1925 /* Global memory usage statistics printed after the database connection 1926 ** has closed. Memory usage should be zero at this point. */ 1927 if( showStats ){ 1928 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0); 1929 printf("-- Memory Used (bytes): %d (max %d)\n", iCur,iHi); 1930 #if SQLITE_VERSION_NUMBER>=3007000 1931 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0); 1932 printf("-- Outstanding Allocations: %d (max %d)\n", iCur,iHi); 1933 #endif 1934 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0); 1935 printf("-- Pcache Overflow Bytes: %d (max %d)\n", iCur,iHi); 1936 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0); 1937 printf("-- Largest Allocation: %d bytes\n",iHi); 1938 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0); 1939 printf("-- Largest Pcache Allocation: %d bytes\n",iHi); 1940 } 1941 #endif 1942 1943 #ifdef __linux__ 1944 if( showStats ){ 1945 displayLinuxIoStats(stdout); 1946 } 1947 #endif 1948 1949 /* Release memory */ 1950 free( pLook ); 1951 free( pPCache ); 1952 free( pHeap ); 1953 return 0; 1954 }