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 " --multithread Set multithreaded mode\n" 19 " --nomemstat Disable memory statistics\n" 20 " --nosync Set PRAGMA synchronous=OFF\n" 21 " --notnull Add NOT NULL constraints to table columns\n" 22 " --pagesize N Set the page size to N\n" 23 " --pcache N SZ Configure N pages of pagecache each of size SZ bytes\n" 24 " --primarykey Use PRIMARY KEY instead of UNIQUE where appropriate\n" 25 " --reprepare Reprepare each statement upon every invocation\n" 26 " --scratch N SZ Configure scratch memory for N slots of SZ bytes each\n" 27 " --serialized Set serialized threading mode\n" 28 " --singlethread Set single-threaded mode - disables all mutexing\n" 29 " --sqlonly No-op. Only show the SQL that would have been run.\n" 30 " --shrink-memory Invoke sqlite3_db_release_memory() frequently.\n" 31 " --size N Relative test size. Default=100\n" 32 " --stats Show statistics at the end\n" 33 " --temp N N from 0 to 9. 0: no temp table. 9: all temp tables\n" 34 " --testset T Run test-set T\n" 35 " --trace Turn on SQL tracing\n" 36 " --threads N Use up to N threads for sorting\n" 37 " --utf16be Set text encoding to UTF-16BE\n" 38 " --utf16le Set text encoding to UTF-16LE\n" 39 " --verify Run additional verification steps.\n" 40 " --without-rowid Use WITHOUT ROWID where appropriate\n" 41 ; 42 43 44 #include "sqlite3.h" 45 #include <assert.h> 46 #include <stdio.h> 47 #include <stdlib.h> 48 #include <stdarg.h> 49 #include <string.h> 50 #include <ctype.h> 51 #define ISSPACE(X) isspace((unsigned char)(X)) 52 #define ISDIGIT(X) isdigit((unsigned char)(X)) 53 54 #if SQLITE_VERSION_NUMBER<3005000 55 # define sqlite3_int64 sqlite_int64 56 #endif 57 #ifdef SQLITE_ENABLE_RBU 58 # include "sqlite3rbu.h" 59 #endif 60 61 /* All global state is held in this structure */ 62 static struct Global { 63 sqlite3 *db; /* The open database connection */ 64 sqlite3_stmt *pStmt; /* Current SQL statement */ 65 sqlite3_int64 iStart; /* Start-time for the current test */ 66 sqlite3_int64 iTotal; /* Total time */ 67 int bWithoutRowid; /* True for --without-rowid */ 68 int bReprepare; /* True to reprepare the SQL on each rerun */ 69 int bSqlOnly; /* True to print the SQL once only */ 70 int bExplain; /* Print SQL with EXPLAIN prefix */ 71 int bVerify; /* Try to verify that results are correct */ 72 int bMemShrink; /* Call sqlite3_db_release_memory() often */ 73 int eTemp; /* 0: no TEMP. 9: always TEMP. */ 74 int szTest; /* Scale factor for test iterations */ 75 const char *zWR; /* Might be WITHOUT ROWID */ 76 const char *zNN; /* Might be NOT NULL */ 77 const char *zPK; /* Might be UNIQUE or PRIMARY KEY */ 78 unsigned int x, y; /* Pseudo-random number generator state */ 79 int nResult; /* Size of the current result */ 80 char zResult[3000]; /* Text of the current result */ 81 } g; 82 83 /* Return " TEMP" or "", as appropriate for creating a table. 84 */ 85 static const char *isTemp(int N){ 86 return g.eTemp>=N ? " TEMP" : ""; 87 } 88 89 90 /* Print an error message and exit */ 91 static void fatal_error(const char *zMsg, ...){ 92 va_list ap; 93 va_start(ap, zMsg); 94 vfprintf(stderr, zMsg, ap); 95 va_end(ap); 96 exit(1); 97 } 98 99 /* 100 ** Return the value of a hexadecimal digit. Return -1 if the input 101 ** is not a hex digit. 102 */ 103 static int hexDigitValue(char c){ 104 if( c>='0' && c<='9' ) return c - '0'; 105 if( c>='a' && c<='f' ) return c - 'a' + 10; 106 if( c>='A' && c<='F' ) return c - 'A' + 10; 107 return -1; 108 } 109 110 /* Provide an alternative to sqlite3_stricmp() in older versions of 111 ** SQLite */ 112 #if SQLITE_VERSION_NUMBER<3007011 113 # define sqlite3_stricmp strcmp 114 #endif 115 116 /* 117 ** Interpret zArg as an integer value, possibly with suffixes. 118 */ 119 static int integerValue(const char *zArg){ 120 sqlite3_int64 v = 0; 121 static const struct { char *zSuffix; int iMult; } aMult[] = { 122 { "KiB", 1024 }, 123 { "MiB", 1024*1024 }, 124 { "GiB", 1024*1024*1024 }, 125 { "KB", 1000 }, 126 { "MB", 1000000 }, 127 { "GB", 1000000000 }, 128 { "K", 1000 }, 129 { "M", 1000000 }, 130 { "G", 1000000000 }, 131 }; 132 int i; 133 int isNeg = 0; 134 if( zArg[0]=='-' ){ 135 isNeg = 1; 136 zArg++; 137 }else if( zArg[0]=='+' ){ 138 zArg++; 139 } 140 if( zArg[0]=='0' && zArg[1]=='x' ){ 141 int x; 142 zArg += 2; 143 while( (x = hexDigitValue(zArg[0]))>=0 ){ 144 v = (v<<4) + x; 145 zArg++; 146 } 147 }else{ 148 while( isdigit(zArg[0]) ){ 149 v = v*10 + zArg[0] - '0'; 150 zArg++; 151 } 152 } 153 for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){ 154 if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){ 155 v *= aMult[i].iMult; 156 break; 157 } 158 } 159 if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648"); 160 return (int)(isNeg? -v : v); 161 } 162 163 /* Return the current wall-clock time, in milliseconds */ 164 sqlite3_int64 speedtest1_timestamp(void){ 165 #if SQLITE_VERSION_NUMBER<3005000 166 return 0; 167 #else 168 static sqlite3_vfs *clockVfs = 0; 169 sqlite3_int64 t; 170 if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0); 171 #if SQLITE_VERSION_NUMBER>=3007000 172 if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){ 173 clockVfs->xCurrentTimeInt64(clockVfs, &t); 174 }else 175 #endif 176 { 177 double r; 178 clockVfs->xCurrentTime(clockVfs, &r); 179 t = (sqlite3_int64)(r*86400000.0); 180 } 181 return t; 182 #endif 183 } 184 185 /* Return a pseudo-random unsigned integer */ 186 unsigned int speedtest1_random(void){ 187 g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001); 188 g.y = g.y*1103515245 + 12345; 189 return g.x ^ g.y; 190 } 191 192 /* Map the value in within the range of 1...limit into another 193 ** number in a way that is chatic and invertable. 194 */ 195 unsigned swizzle(unsigned in, unsigned limit){ 196 unsigned out = 0; 197 while( limit ){ 198 out = (out<<1) | (in&1); 199 in >>= 1; 200 limit >>= 1; 201 } 202 return out; 203 } 204 205 /* Round up a number so that it is a power of two minus one 206 */ 207 unsigned roundup_allones(unsigned limit){ 208 unsigned m = 1; 209 while( m<limit ) m = (m<<1)+1; 210 return m; 211 } 212 213 /* The speedtest1_numbername procedure below converts its argment (an integer) 214 ** into a string which is the English-language name for that number. 215 ** The returned string should be freed with sqlite3_free(). 216 ** 217 ** Example: 218 ** 219 ** speedtest1_numbername(123) -> "one hundred twenty three" 220 */ 221 int speedtest1_numbername(unsigned int n, char *zOut, int nOut){ 222 static const char *ones[] = { "zero", "one", "two", "three", "four", "five", 223 "six", "seven", "eight", "nine", "ten", "eleven", "twelve", 224 "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", 225 "eighteen", "nineteen" }; 226 static const char *tens[] = { "", "ten", "twenty", "thirty", "forty", 227 "fifty", "sixty", "seventy", "eighty", "ninety" }; 228 int i = 0; 229 230 if( n>=1000000000 ){ 231 i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i); 232 sqlite3_snprintf(nOut-i, zOut+i, " billion"); 233 i += (int)strlen(zOut+i); 234 n = n % 1000000000; 235 } 236 if( n>=1000000 ){ 237 if( i && i<nOut-1 ) zOut[i++] = ' '; 238 i += speedtest1_numbername(n/1000000, zOut+i, nOut-i); 239 sqlite3_snprintf(nOut-i, zOut+i, " million"); 240 i += (int)strlen(zOut+i); 241 n = n % 1000000; 242 } 243 if( n>=1000 ){ 244 if( i && i<nOut-1 ) zOut[i++] = ' '; 245 i += speedtest1_numbername(n/1000, zOut+i, nOut-i); 246 sqlite3_snprintf(nOut-i, zOut+i, " thousand"); 247 i += (int)strlen(zOut+i); 248 n = n % 1000; 249 } 250 if( n>=100 ){ 251 if( i && i<nOut-1 ) zOut[i++] = ' '; 252 sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]); 253 i += (int)strlen(zOut+i); 254 n = n % 100; 255 } 256 if( n>=20 ){ 257 if( i && i<nOut-1 ) zOut[i++] = ' '; 258 sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]); 259 i += (int)strlen(zOut+i); 260 n = n % 10; 261 } 262 if( n>0 ){ 263 if( i && i<nOut-1 ) zOut[i++] = ' '; 264 sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]); 265 i += (int)strlen(zOut+i); 266 } 267 if( i==0 ){ 268 sqlite3_snprintf(nOut-i, zOut+i, "zero"); 269 i += (int)strlen(zOut+i); 270 } 271 return i; 272 } 273 274 275 /* Start a new test case */ 276 #define NAMEWIDTH 60 277 static const char zDots[] = 278 "......................................................................."; 279 void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){ 280 int n = (int)strlen(zTestName); 281 char *zName; 282 va_list ap; 283 va_start(ap, zTestName); 284 zName = sqlite3_vmprintf(zTestName, ap); 285 va_end(ap); 286 n = (int)strlen(zName); 287 if( n>NAMEWIDTH ){ 288 zName[NAMEWIDTH] = 0; 289 n = NAMEWIDTH; 290 } 291 if( g.bSqlOnly ){ 292 printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots); 293 }else{ 294 printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots); 295 fflush(stdout); 296 } 297 sqlite3_free(zName); 298 g.nResult = 0; 299 g.iStart = speedtest1_timestamp(); 300 g.x = 0xad131d0b; 301 g.y = 0x44f9eac8; 302 } 303 304 /* Complete a test case */ 305 void speedtest1_end_test(void){ 306 sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart; 307 if( !g.bSqlOnly ){ 308 g.iTotal += iElapseTime; 309 printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000)); 310 } 311 if( g.pStmt ){ 312 sqlite3_finalize(g.pStmt); 313 g.pStmt = 0; 314 } 315 } 316 317 /* Report end of testing */ 318 void speedtest1_final(void){ 319 if( !g.bSqlOnly ){ 320 printf(" TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots, 321 (int)(g.iTotal/1000), (int)(g.iTotal%1000)); 322 } 323 } 324 325 /* Print an SQL statement to standard output */ 326 static void printSql(const char *zSql){ 327 int n = (int)strlen(zSql); 328 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ){ n--; } 329 if( g.bExplain ) printf("EXPLAIN "); 330 printf("%.*s;\n", n, zSql); 331 if( g.bExplain 332 #if SQLITE_VERSION_NUMBER>=3007017 333 && ( sqlite3_strglob("CREATE *", zSql)==0 334 || sqlite3_strglob("DROP *", zSql)==0 335 || sqlite3_strglob("ALTER *", zSql)==0 336 ) 337 #endif 338 ){ 339 printf("%.*s;\n", n, zSql); 340 } 341 } 342 343 /* Shrink memory used, if appropriate and if the SQLite version is capable 344 ** of doing so. 345 */ 346 void speedtest1_shrink_memory(void){ 347 #if SQLITE_VERSION_NUMBER>=3007010 348 if( g.bMemShrink ) sqlite3_db_release_memory(g.db); 349 #endif 350 } 351 352 /* Run SQL */ 353 void speedtest1_exec(const char *zFormat, ...){ 354 va_list ap; 355 char *zSql; 356 va_start(ap, zFormat); 357 zSql = sqlite3_vmprintf(zFormat, ap); 358 va_end(ap); 359 if( g.bSqlOnly ){ 360 printSql(zSql); 361 }else{ 362 char *zErrMsg = 0; 363 int rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg); 364 if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql); 365 if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db)); 366 } 367 sqlite3_free(zSql); 368 speedtest1_shrink_memory(); 369 } 370 371 /* Prepare an SQL statement */ 372 void speedtest1_prepare(const char *zFormat, ...){ 373 va_list ap; 374 char *zSql; 375 va_start(ap, zFormat); 376 zSql = sqlite3_vmprintf(zFormat, ap); 377 va_end(ap); 378 if( g.bSqlOnly ){ 379 printSql(zSql); 380 }else{ 381 int rc; 382 if( g.pStmt ) sqlite3_finalize(g.pStmt); 383 rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0); 384 if( rc ){ 385 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db)); 386 } 387 } 388 sqlite3_free(zSql); 389 } 390 391 /* Run an SQL statement previously prepared */ 392 void speedtest1_run(void){ 393 int i, n, len; 394 if( g.bSqlOnly ) return; 395 assert( g.pStmt ); 396 g.nResult = 0; 397 while( sqlite3_step(g.pStmt)==SQLITE_ROW ){ 398 n = sqlite3_column_count(g.pStmt); 399 for(i=0; i<n; i++){ 400 const char *z = (const char*)sqlite3_column_text(g.pStmt, i); 401 if( z==0 ) z = "nil"; 402 len = (int)strlen(z); 403 if( g.nResult+len<sizeof(g.zResult)-2 ){ 404 if( g.nResult>0 ) g.zResult[g.nResult++] = ' '; 405 memcpy(g.zResult + g.nResult, z, len+1); 406 g.nResult += len; 407 } 408 } 409 } 410 #if SQLITE_VERSION_NUMBER>=3006001 411 if( g.bReprepare ){ 412 sqlite3_stmt *pNew; 413 sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0); 414 sqlite3_finalize(g.pStmt); 415 g.pStmt = pNew; 416 }else 417 #endif 418 { 419 sqlite3_reset(g.pStmt); 420 } 421 speedtest1_shrink_memory(); 422 } 423 424 #ifndef SQLITE_OMIT_DEPRECATED 425 /* The sqlite3_trace() callback function */ 426 static void traceCallback(void *NotUsed, const char *zSql){ 427 int n = (int)strlen(zSql); 428 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ) n--; 429 fprintf(stderr,"%.*s;\n", n, zSql); 430 } 431 #endif /* SQLITE_OMIT_DEPRECATED */ 432 433 /* Substitute random() function that gives the same random 434 ** sequence on each run, for repeatability. */ 435 static void randomFunc( 436 sqlite3_context *context, 437 int NotUsed, 438 sqlite3_value **NotUsed2 439 ){ 440 sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random()); 441 } 442 443 /* Estimate the square root of an integer */ 444 static int est_square_root(int x){ 445 int y0 = x/2; 446 int y1; 447 int n; 448 for(n=0; y0>0 && n<10; n++){ 449 y1 = (y0 + x/y0)/2; 450 if( y1==y0 ) break; 451 y0 = y1; 452 } 453 return y0; 454 } 455 456 /* 457 ** The main and default testset 458 */ 459 void testset_main(void){ 460 int i; /* Loop counter */ 461 int n; /* iteration count */ 462 int sz; /* Size of the tables */ 463 int maxb; /* Maximum swizzled value */ 464 unsigned x1, x2; /* Parameters */ 465 int len; /* Length of the zNum[] string */ 466 char zNum[2000]; /* A number name */ 467 468 sz = n = g.szTest*500; 469 maxb = roundup_allones(sz); 470 speedtest1_begin_test(100, "%d INSERTs into table with no index", n); 471 speedtest1_exec("BEGIN"); 472 speedtest1_exec("CREATE%s TABLE t1(a INTEGER %s, b INTEGER %s, c TEXT %s);", 473 isTemp(9), g.zNN, g.zNN, g.zNN); 474 speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); -- %d times", n); 475 for(i=1; i<=n; i++){ 476 x1 = swizzle(i,maxb); 477 speedtest1_numbername(x1, zNum, sizeof(zNum)); 478 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1); 479 sqlite3_bind_int(g.pStmt, 2, i); 480 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC); 481 speedtest1_run(); 482 } 483 speedtest1_exec("COMMIT"); 484 speedtest1_end_test(); 485 486 487 n = sz; 488 speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n); 489 speedtest1_exec("BEGIN"); 490 speedtest1_exec( 491 "CREATE%s TABLE t2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s", 492 isTemp(5), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR); 493 speedtest1_prepare("INSERT INTO t2 VALUES(?1,?2,?3); -- %d times", n); 494 for(i=1; i<=n; i++){ 495 x1 = swizzle(i,maxb); 496 speedtest1_numbername(x1, zNum, sizeof(zNum)); 497 sqlite3_bind_int(g.pStmt, 1, i); 498 sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1); 499 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC); 500 speedtest1_run(); 501 } 502 speedtest1_exec("COMMIT"); 503 speedtest1_end_test(); 504 505 506 n = sz; 507 speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n); 508 speedtest1_exec("BEGIN"); 509 speedtest1_exec( 510 "CREATE%s TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s", 511 isTemp(3), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR); 512 speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n); 513 for(i=1; i<=n; i++){ 514 x1 = swizzle(i,maxb); 515 speedtest1_numbername(x1, zNum, sizeof(zNum)); 516 sqlite3_bind_int(g.pStmt, 2, i); 517 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1); 518 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC); 519 speedtest1_run(); 520 } 521 speedtest1_exec("COMMIT"); 522 speedtest1_end_test(); 523 524 525 n = 25; 526 speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n); 527 speedtest1_exec("BEGIN"); 528 speedtest1_prepare( 529 "SELECT count(*), avg(b), sum(length(c)) FROM t1\n" 530 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n 531 ); 532 for(i=1; i<=n; i++){ 533 x1 = speedtest1_random()%maxb; 534 x2 = speedtest1_random()%10 + sz/5000 + x1; 535 sqlite3_bind_int(g.pStmt, 1, x1); 536 sqlite3_bind_int(g.pStmt, 2, x2); 537 speedtest1_run(); 538 } 539 speedtest1_exec("COMMIT"); 540 speedtest1_end_test(); 541 542 543 n = 10; 544 speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n); 545 speedtest1_exec("BEGIN"); 546 speedtest1_prepare( 547 "SELECT count(*), avg(b), sum(length(c)) FROM t1\n" 548 " WHERE c LIKE ?1; -- %d times", n 549 ); 550 for(i=1; i<=n; i++){ 551 x1 = speedtest1_random()%maxb; 552 zNum[0] = '%'; 553 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2); 554 zNum[len] = '%'; 555 zNum[len+1] = 0; 556 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC); 557 speedtest1_run(); 558 } 559 speedtest1_exec("COMMIT"); 560 speedtest1_end_test(); 561 562 563 n = 10; 564 speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n); 565 speedtest1_exec("BEGIN"); 566 speedtest1_prepare( 567 "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n" 568 " ORDER BY a; -- %d times", n 569 ); 570 for(i=1; i<=n; i++){ 571 x1 = speedtest1_random()%maxb; 572 zNum[0] = '%'; 573 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2); 574 zNum[len] = '%'; 575 zNum[len+1] = 0; 576 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC); 577 speedtest1_run(); 578 } 579 speedtest1_exec("COMMIT"); 580 speedtest1_end_test(); 581 582 n = 10; /* g.szTest/5; */ 583 speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n); 584 speedtest1_exec("BEGIN"); 585 speedtest1_prepare( 586 "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n" 587 " ORDER BY a LIMIT 10; -- %d times", n 588 ); 589 for(i=1; i<=n; i++){ 590 x1 = speedtest1_random()%maxb; 591 zNum[0] = '%'; 592 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2); 593 zNum[len] = '%'; 594 zNum[len+1] = 0; 595 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC); 596 speedtest1_run(); 597 } 598 speedtest1_exec("COMMIT"); 599 speedtest1_end_test(); 600 601 602 speedtest1_begin_test(150, "CREATE INDEX five times"); 603 speedtest1_exec("BEGIN;"); 604 speedtest1_exec("CREATE UNIQUE INDEX t1b ON t1(b);"); 605 speedtest1_exec("CREATE INDEX t1c ON t1(c);"); 606 speedtest1_exec("CREATE UNIQUE INDEX t2b ON t2(b);"); 607 speedtest1_exec("CREATE INDEX t2c ON t2(c DESC);"); 608 speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);"); 609 speedtest1_exec("COMMIT;"); 610 speedtest1_end_test(); 611 612 613 n = sz/5; 614 speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n); 615 speedtest1_exec("BEGIN"); 616 speedtest1_prepare( 617 "SELECT count(*), avg(b), sum(length(c)) FROM t1\n" 618 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n 619 ); 620 for(i=1; i<=n; i++){ 621 x1 = speedtest1_random()%maxb; 622 x2 = speedtest1_random()%10 + sz/5000 + x1; 623 sqlite3_bind_int(g.pStmt, 1, x1); 624 sqlite3_bind_int(g.pStmt, 2, x2); 625 speedtest1_run(); 626 } 627 speedtest1_exec("COMMIT"); 628 speedtest1_end_test(); 629 630 631 n = sz/5; 632 speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n); 633 speedtest1_exec("BEGIN"); 634 speedtest1_prepare( 635 "SELECT count(*), avg(b), sum(length(c)) FROM t2\n" 636 " WHERE a BETWEEN ?1 AND ?2; -- %d times", n 637 ); 638 for(i=1; i<=n; i++){ 639 x1 = speedtest1_random()%maxb; 640 x2 = speedtest1_random()%10 + sz/5000 + x1; 641 sqlite3_bind_int(g.pStmt, 1, x1); 642 sqlite3_bind_int(g.pStmt, 2, x2); 643 speedtest1_run(); 644 } 645 speedtest1_exec("COMMIT"); 646 speedtest1_end_test(); 647 648 649 n = sz/5; 650 speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n); 651 speedtest1_exec("BEGIN"); 652 speedtest1_prepare( 653 "SELECT count(*), avg(b), sum(length(c)) FROM t1\n" 654 " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n 655 ); 656 for(i=1; i<=n; i++){ 657 x1 = swizzle(i, maxb); 658 len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1); 659 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC); 660 speedtest1_run(); 661 } 662 speedtest1_exec("COMMIT"); 663 speedtest1_end_test(); 664 665 n = sz; 666 speedtest1_begin_test(180, "%d INSERTS with three indexes", n); 667 speedtest1_exec("BEGIN"); 668 speedtest1_exec( 669 "CREATE%s TABLE t4(\n" 670 " a INTEGER %s %s,\n" 671 " b INTEGER %s,\n" 672 " c TEXT %s\n" 673 ") %s", 674 isTemp(1), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR); 675 speedtest1_exec("CREATE INDEX t4b ON t4(b)"); 676 speedtest1_exec("CREATE INDEX t4c ON t4(c)"); 677 speedtest1_exec("INSERT INTO t4 SELECT * FROM t1"); 678 speedtest1_exec("COMMIT"); 679 speedtest1_end_test(); 680 681 n = sz; 682 speedtest1_begin_test(190, "DELETE and REFILL one table", n); 683 speedtest1_exec("DELETE FROM t2;"); 684 speedtest1_exec("INSERT INTO t2 SELECT * FROM t1;"); 685 speedtest1_end_test(); 686 687 688 speedtest1_begin_test(200, "VACUUM"); 689 speedtest1_exec("VACUUM"); 690 speedtest1_end_test(); 691 692 693 speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query"); 694 speedtest1_exec("ALTER TABLE t2 ADD COLUMN d DEFAULT 123"); 695 speedtest1_exec("SELECT sum(d) FROM t2"); 696 speedtest1_end_test(); 697 698 699 n = sz/5; 700 speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n); 701 speedtest1_exec("BEGIN"); 702 speedtest1_prepare( 703 "UPDATE t2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n 704 ); 705 for(i=1; i<=n; i++){ 706 x1 = speedtest1_random()%maxb; 707 x2 = speedtest1_random()%10 + sz/5000 + x1; 708 sqlite3_bind_int(g.pStmt, 1, x1); 709 sqlite3_bind_int(g.pStmt, 2, x2); 710 speedtest1_run(); 711 } 712 speedtest1_exec("COMMIT"); 713 speedtest1_end_test(); 714 715 716 n = sz; 717 speedtest1_begin_test(240, "%d UPDATES of individual rows", n); 718 speedtest1_exec("BEGIN"); 719 speedtest1_prepare( 720 "UPDATE t2 SET d=b*3 WHERE a=?1; -- %d times", n 721 ); 722 for(i=1; i<=n; i++){ 723 x1 = speedtest1_random()%sz + 1; 724 sqlite3_bind_int(g.pStmt, 1, x1); 725 speedtest1_run(); 726 } 727 speedtest1_exec("COMMIT"); 728 speedtest1_end_test(); 729 730 speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz); 731 speedtest1_exec("UPDATE t2 SET d=b*4"); 732 speedtest1_end_test(); 733 734 735 speedtest1_begin_test(260, "Query added column after filling"); 736 speedtest1_exec("SELECT sum(d) FROM t2"); 737 speedtest1_end_test(); 738 739 740 741 n = sz/5; 742 speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n); 743 speedtest1_exec("BEGIN"); 744 speedtest1_prepare( 745 "DELETE FROM t2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n 746 ); 747 for(i=1; i<=n; i++){ 748 x1 = speedtest1_random()%maxb + 1; 749 x2 = speedtest1_random()%10 + sz/5000 + x1; 750 sqlite3_bind_int(g.pStmt, 1, x1); 751 sqlite3_bind_int(g.pStmt, 2, x2); 752 speedtest1_run(); 753 } 754 speedtest1_exec("COMMIT"); 755 speedtest1_end_test(); 756 757 758 n = sz; 759 speedtest1_begin_test(280, "%d DELETEs of individual rows", n); 760 speedtest1_exec("BEGIN"); 761 speedtest1_prepare( 762 "DELETE FROM t3 WHERE a=?1; -- %d times", n 763 ); 764 for(i=1; i<=n; i++){ 765 x1 = speedtest1_random()%sz + 1; 766 sqlite3_bind_int(g.pStmt, 1, x1); 767 speedtest1_run(); 768 } 769 speedtest1_exec("COMMIT"); 770 speedtest1_end_test(); 771 772 773 speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz); 774 speedtest1_exec("REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1"); 775 speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1"); 776 speedtest1_end_test(); 777 778 speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz); 779 speedtest1_exec("DELETE FROM t2;"); 780 speedtest1_exec("INSERT INTO t2(a,b,c)\n" 781 " SELECT a,b,c FROM t1 WHERE (b&1)==(a&1);"); 782 speedtest1_exec("INSERT INTO t2(a,b,c)\n" 783 " SELECT a,b,c FROM t1 WHERE (b&1)<>(a&1);"); 784 speedtest1_end_test(); 785 786 787 n = sz/5; 788 speedtest1_begin_test(310, "%d four-ways joins", n); 789 speedtest1_exec("BEGIN"); 790 speedtest1_prepare( 791 "SELECT t1.c FROM t1, t2, t3, t4\n" 792 " WHERE t4.a BETWEEN ?1 AND ?2\n" 793 " AND t3.a=t4.b\n" 794 " AND t2.a=t3.b\n" 795 " AND t1.c=t2.c" 796 ); 797 for(i=1; i<=n; i++){ 798 x1 = speedtest1_random()%sz + 1; 799 x2 = speedtest1_random()%10 + x1 + 4; 800 sqlite3_bind_int(g.pStmt, 1, x1); 801 sqlite3_bind_int(g.pStmt, 2, x2); 802 speedtest1_run(); 803 } 804 speedtest1_exec("COMMIT"); 805 speedtest1_end_test(); 806 807 speedtest1_begin_test(320, "subquery in result set", n); 808 speedtest1_prepare( 809 "SELECT sum(a), max(c),\n" 810 " avg((SELECT a FROM t2 WHERE 5+t2.b=t1.b) AND rowid<?1), max(c)\n" 811 " FROM t1 WHERE rowid<?1;" 812 ); 813 sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50); 814 speedtest1_run(); 815 speedtest1_end_test(); 816 817 speedtest1_begin_test(980, "PRAGMA integrity_check"); 818 speedtest1_exec("PRAGMA integrity_check"); 819 speedtest1_end_test(); 820 821 822 speedtest1_begin_test(990, "ANALYZE"); 823 speedtest1_exec("ANALYZE"); 824 speedtest1_end_test(); 825 } 826 827 /* 828 ** A testset for common table expressions. This exercises code 829 ** for views, subqueries, co-routines, etc. 830 */ 831 void testset_cte(void){ 832 static const char *azPuzzle[] = { 833 /* Easy */ 834 "534...9.." 835 "67.195..." 836 ".98....6." 837 "8...6...3" 838 "4..8.3..1" 839 "....2...6" 840 ".6....28." 841 "...419..5" 842 "...28..79", 843 844 /* Medium */ 845 "53....9.." 846 "6..195..." 847 ".98....6." 848 "8...6...3" 849 "4..8.3..1" 850 "....2...6" 851 ".6....28." 852 "...419..5" 853 "....8..79", 854 855 /* Hard */ 856 "53......." 857 "6..195..." 858 ".98....6." 859 "8...6...3" 860 "4..8.3..1" 861 "....2...6" 862 ".6....28." 863 "...419..5" 864 "....8..79", 865 }; 866 const char *zPuz; 867 double rSpacing; 868 int nElem; 869 870 if( g.szTest<25 ){ 871 zPuz = azPuzzle[0]; 872 }else if( g.szTest<70 ){ 873 zPuz = azPuzzle[1]; 874 }else{ 875 zPuz = azPuzzle[2]; 876 } 877 speedtest1_begin_test(100, "Sudoku with recursive 'digits'"); 878 speedtest1_prepare( 879 "WITH RECURSIVE\n" 880 " input(sud) AS (VALUES(?1)),\n" 881 " digits(z,lp) AS (\n" 882 " VALUES('1', 1)\n" 883 " UNION ALL\n" 884 " SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9\n" 885 " ),\n" 886 " x(s, ind) AS (\n" 887 " SELECT sud, instr(sud, '.') FROM input\n" 888 " UNION ALL\n" 889 " SELECT\n" 890 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n" 891 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n" 892 " FROM x, digits AS z\n" 893 " WHERE ind>0\n" 894 " AND NOT EXISTS (\n" 895 " SELECT 1\n" 896 " FROM digits AS lp\n" 897 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n" 898 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n" 899 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n" 900 " + ((ind-1)/27) * 27 + lp\n" 901 " + ((lp-1) / 3) * 6, 1)\n" 902 " )\n" 903 " )\n" 904 "SELECT s FROM x WHERE ind=0;" 905 ); 906 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC); 907 speedtest1_run(); 908 speedtest1_end_test(); 909 910 speedtest1_begin_test(200, "Sudoku with VALUES 'digits'"); 911 speedtest1_prepare( 912 "WITH RECURSIVE\n" 913 " input(sud) AS (VALUES(?1)),\n" 914 " digits(z,lp) AS (VALUES('1',1),('2',2),('3',3),('4',4),('5',5),\n" 915 " ('6',6),('7',7),('8',8),('9',9)),\n" 916 " x(s, ind) AS (\n" 917 " SELECT sud, instr(sud, '.') FROM input\n" 918 " UNION ALL\n" 919 " SELECT\n" 920 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n" 921 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n" 922 " FROM x, digits AS z\n" 923 " WHERE ind>0\n" 924 " AND NOT EXISTS (\n" 925 " SELECT 1\n" 926 " FROM digits AS lp\n" 927 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n" 928 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n" 929 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n" 930 " + ((ind-1)/27) * 27 + lp\n" 931 " + ((lp-1) / 3) * 6, 1)\n" 932 " )\n" 933 " )\n" 934 "SELECT s FROM x WHERE ind=0;" 935 ); 936 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC); 937 speedtest1_run(); 938 speedtest1_end_test(); 939 940 rSpacing = 5.0/g.szTest; 941 speedtest1_begin_test(300, "Mandelbrot Set with spacing=%f", rSpacing); 942 speedtest1_prepare( 943 "WITH RECURSIVE \n" 944 " xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+?1 FROM xaxis WHERE x<1.2),\n" 945 " yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+?2 FROM yaxis WHERE y<1.0),\n" 946 " m(iter, cx, cy, x, y) AS (\n" 947 " SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis\n" 948 " UNION ALL\n" 949 " SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \n" 950 " WHERE (x*x + y*y) < 4.0 AND iter<28\n" 951 " ),\n" 952 " m2(iter, cx, cy) AS (\n" 953 " SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n" 954 " ),\n" 955 " a(t) AS (\n" 956 " SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n" 957 " FROM m2 GROUP BY cy\n" 958 " )\n" 959 "SELECT group_concat(rtrim(t),x'0a') FROM a;" 960 ); 961 sqlite3_bind_double(g.pStmt, 1, rSpacing*.05); 962 sqlite3_bind_double(g.pStmt, 2, rSpacing); 963 speedtest1_run(); 964 speedtest1_end_test(); 965 966 nElem = 10000*g.szTest; 967 speedtest1_begin_test(400, "EXCEPT operator on %d-element tables", nElem); 968 speedtest1_prepare( 969 "WITH RECURSIVE \n" 970 " t1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM t1 WHERE x<%d),\n" 971 " t2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM t2 WHERE y<%d)\n" 972 "SELECT count(x), avg(x) FROM (\n" 973 " SELECT x FROM t1 EXCEPT SELECT y FROM t2 ORDER BY 1\n" 974 ");", 975 nElem, nElem 976 ); 977 speedtest1_run(); 978 speedtest1_end_test(); 979 980 } 981 982 #ifdef SQLITE_ENABLE_RTREE 983 /* Generate two numbers between 1 and mx. The first number is less than 984 ** the second. Usually the numbers are near each other but can sometimes 985 ** be far apart. 986 */ 987 static void twoCoords( 988 int p1, int p2, /* Parameters adjusting sizes */ 989 unsigned mx, /* Range of 1..mx */ 990 unsigned *pX0, unsigned *pX1 /* OUT: write results here */ 991 ){ 992 unsigned d, x0, x1, span; 993 994 span = mx/100 + 1; 995 if( speedtest1_random()%3==0 ) span *= p1; 996 if( speedtest1_random()%p2==0 ) span = mx/2; 997 d = speedtest1_random()%span + 1; 998 x0 = speedtest1_random()%(mx-d) + 1; 999 x1 = x0 + d; 1000 *pX0 = x0; 1001 *pX1 = x1; 1002 } 1003 #endif 1004 1005 #ifdef SQLITE_ENABLE_RTREE 1006 /* The following routine is an R-Tree geometry callback. It returns 1007 ** true if the object overlaps a slice on the Y coordinate between the 1008 ** two values given as arguments. In other words 1009 ** 1010 ** SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20); 1011 ** 1012 ** Is the same as saying: 1013 ** 1014 ** SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20; 1015 */ 1016 static int xsliceGeometryCallback( 1017 sqlite3_rtree_geometry *p, 1018 int nCoord, 1019 double *aCoord, 1020 int *pRes 1021 ){ 1022 *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1]; 1023 return SQLITE_OK; 1024 } 1025 #endif /* SQLITE_ENABLE_RTREE */ 1026 1027 #ifdef SQLITE_ENABLE_RTREE 1028 /* 1029 ** A testset for the R-Tree virtual table 1030 */ 1031 void testset_rtree(int p1, int p2){ 1032 unsigned i, n; 1033 unsigned mxCoord; 1034 unsigned x0, x1, y0, y1, z0, z1; 1035 unsigned iStep; 1036 int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*100 ); 1037 1038 mxCoord = 15000; 1039 n = g.szTest*100; 1040 speedtest1_begin_test(100, "%d INSERTs into an r-tree", n); 1041 speedtest1_exec("BEGIN"); 1042 speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)"); 1043 speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)" 1044 "VALUES(?1,?2,?3,?4,?5,?6,?7)"); 1045 for(i=1; i<=n; i++){ 1046 twoCoords(p1, p2, mxCoord, &x0, &x1); 1047 twoCoords(p1, p2, mxCoord, &y0, &y1); 1048 twoCoords(p1, p2, mxCoord, &z0, &z1); 1049 sqlite3_bind_int(g.pStmt, 1, i); 1050 sqlite3_bind_int(g.pStmt, 2, x0); 1051 sqlite3_bind_int(g.pStmt, 3, x1); 1052 sqlite3_bind_int(g.pStmt, 4, y0); 1053 sqlite3_bind_int(g.pStmt, 5, y1); 1054 sqlite3_bind_int(g.pStmt, 6, z0); 1055 sqlite3_bind_int(g.pStmt, 7, z1); 1056 speedtest1_run(); 1057 } 1058 speedtest1_exec("COMMIT"); 1059 speedtest1_end_test(); 1060 1061 speedtest1_begin_test(101, "Copy from rtree to a regular table"); 1062 speedtest1_exec(" TABLE t1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)"); 1063 speedtest1_exec("INSERT INTO t1 SELECT * FROM rt1"); 1064 speedtest1_end_test(); 1065 1066 n = g.szTest*20; 1067 speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n); 1068 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2"); 1069 iStep = mxCoord/n; 1070 for(i=0; i<n; i++){ 1071 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1072 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1073 speedtest1_run(); 1074 aCheck[i] = atoi(g.zResult); 1075 } 1076 speedtest1_end_test(); 1077 1078 if( g.bVerify ){ 1079 n = g.szTest*20; 1080 speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries"); 1081 speedtest1_prepare("SELECT count(*) FROM t1 WHERE x0>=?1 AND x1<=?2"); 1082 iStep = mxCoord/n; 1083 for(i=0; i<n; i++){ 1084 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1085 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1086 speedtest1_run(); 1087 if( aCheck[i]!=atoi(g.zResult) ){ 1088 fatal_error("Count disagree step %d: %d..%d. %d vs %d", 1089 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult)); 1090 } 1091 } 1092 speedtest1_end_test(); 1093 } 1094 1095 n = g.szTest*20; 1096 speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n); 1097 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2"); 1098 iStep = mxCoord/n; 1099 for(i=0; i<n; i++){ 1100 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1101 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1102 speedtest1_run(); 1103 aCheck[i] = atoi(g.zResult); 1104 } 1105 speedtest1_end_test(); 1106 1107 if( g.bVerify ){ 1108 n = g.szTest*20; 1109 speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries"); 1110 speedtest1_prepare("SELECT count(*) FROM t1 WHERE y1>=?1 AND y0<=?2"); 1111 iStep = mxCoord/n; 1112 for(i=0; i<n; i++){ 1113 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1114 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1115 speedtest1_run(); 1116 if( aCheck[i]!=atoi(g.zResult) ){ 1117 fatal_error("Count disagree step %d: %d..%d. %d vs %d", 1118 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult)); 1119 } 1120 } 1121 speedtest1_end_test(); 1122 } 1123 1124 1125 n = g.szTest*20; 1126 speedtest1_begin_test(125, "%d custom geometry callback queries", n); 1127 sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0); 1128 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)"); 1129 iStep = mxCoord/n; 1130 for(i=0; i<n; i++){ 1131 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1132 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1133 speedtest1_run(); 1134 if( aCheck[i]!=atoi(g.zResult) ){ 1135 fatal_error("Count disagree step %d: %d..%d. %d vs %d", 1136 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult)); 1137 } 1138 } 1139 speedtest1_end_test(); 1140 1141 n = g.szTest*80; 1142 speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n); 1143 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2" 1144 " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2"); 1145 iStep = mxCoord/n; 1146 for(i=0; i<n; i++){ 1147 sqlite3_bind_int(g.pStmt, 1, i*iStep); 1148 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep); 1149 speedtest1_run(); 1150 aCheck[i] = atoi(g.zResult); 1151 } 1152 speedtest1_end_test(); 1153 1154 n = g.szTest*100; 1155 speedtest1_begin_test(140, "%d rowid queries", n); 1156 speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1"); 1157 for(i=1; i<=n; i++){ 1158 sqlite3_bind_int(g.pStmt, 1, i); 1159 speedtest1_run(); 1160 } 1161 speedtest1_end_test(); 1162 } 1163 #endif /* SQLITE_ENABLE_RTREE */ 1164 1165 /* 1166 ** A testset used for debugging speedtest1 itself. 1167 */ 1168 void testset_debug1(void){ 1169 unsigned i, n; 1170 unsigned x1, x2; 1171 char zNum[2000]; /* A number name */ 1172 1173 n = g.szTest; 1174 for(i=1; i<=n; i++){ 1175 x1 = swizzle(i, n); 1176 x2 = swizzle(x1, n); 1177 speedtest1_numbername(x1, zNum, sizeof(zNum)); 1178 printf("%5d %5d %5d %s\n", i, x1, x2, zNum); 1179 } 1180 } 1181 1182 #ifdef __linux__ 1183 #include <sys/types.h> 1184 #include <unistd.h> 1185 1186 /* 1187 ** Attempt to display I/O stats on Linux using /proc/PID/io 1188 */ 1189 static void displayLinuxIoStats(FILE *out){ 1190 FILE *in; 1191 char z[200]; 1192 sqlite3_snprintf(sizeof(z), z, "/proc/%d/io", getpid()); 1193 in = fopen(z, "rb"); 1194 if( in==0 ) return; 1195 while( fgets(z, sizeof(z), in)!=0 ){ 1196 static const struct { 1197 const char *zPattern; 1198 const char *zDesc; 1199 } aTrans[] = { 1200 { "rchar: ", "Bytes received by read():" }, 1201 { "wchar: ", "Bytes sent to write():" }, 1202 { "syscr: ", "Read() system calls:" }, 1203 { "syscw: ", "Write() system calls:" }, 1204 { "read_bytes: ", "Bytes rcvd from storage:" }, 1205 { "write_bytes: ", "Bytes sent to storage:" }, 1206 { "cancelled_write_bytes: ", "Cancelled write bytes:" }, 1207 }; 1208 int i; 1209 for(i=0; i<sizeof(aTrans)/sizeof(aTrans[0]); i++){ 1210 int n = (int)strlen(aTrans[i].zPattern); 1211 if( strncmp(aTrans[i].zPattern, z, n)==0 ){ 1212 fprintf(out, "-- %-28s %s", aTrans[i].zDesc, &z[n]); 1213 break; 1214 } 1215 } 1216 } 1217 fclose(in); 1218 } 1219 #endif 1220 1221 #if SQLITE_VERSION_NUMBER<3006018 1222 # define sqlite3_sourceid(X) "(before 3.6.18)" 1223 #endif 1224 1225 int main(int argc, char **argv){ 1226 int doAutovac = 0; /* True for --autovacuum */ 1227 int cacheSize = 0; /* Desired cache size. 0 means default */ 1228 int doExclusive = 0; /* True for --exclusive */ 1229 int nHeap = 0, mnHeap = 0; /* Heap size from --heap */ 1230 int doIncrvac = 0; /* True for --incrvacuum */ 1231 const char *zJMode = 0; /* Journal mode */ 1232 const char *zKey = 0; /* Encryption key */ 1233 int nLook = 0, szLook = 0; /* --lookaside configuration */ 1234 int noSync = 0; /* True for --nosync */ 1235 int pageSize = 0; /* Desired page size. 0 means default */ 1236 int nPCache = 0, szPCache = 0;/* --pcache configuration */ 1237 int doPCache = 0; /* True if --pcache is seen */ 1238 int nScratch = 0, szScratch=0;/* --scratch configuration */ 1239 int showStats = 0; /* True for --stats */ 1240 int nThread = 0; /* --threads value */ 1241 const char *zTSet = "main"; /* Which --testset torun */ 1242 int doTrace = 0; /* True for --trace */ 1243 const char *zEncoding = 0; /* --utf16be or --utf16le */ 1244 const char *zDbName = 0; /* Name of the test database */ 1245 1246 void *pHeap = 0; /* Allocated heap space */ 1247 void *pLook = 0; /* Allocated lookaside space */ 1248 void *pPCache = 0; /* Allocated storage for pcache */ 1249 void *pScratch = 0; /* Allocated storage for scratch */ 1250 int iCur, iHi; /* Stats values, current and "highwater" */ 1251 int i; /* Loop counter */ 1252 int rc; /* API return code */ 1253 1254 /* Display the version of SQLite being tested */ 1255 printf("-- Speedtest1 for SQLite %s %.50s\n", 1256 sqlite3_libversion(), sqlite3_sourceid()); 1257 1258 /* Process command-line arguments */ 1259 g.zWR = ""; 1260 g.zNN = ""; 1261 g.zPK = "UNIQUE"; 1262 g.szTest = 100; 1263 for(i=1; i<argc; i++){ 1264 const char *z = argv[i]; 1265 if( z[0]=='-' ){ 1266 do{ z++; }while( z[0]=='-' ); 1267 if( strcmp(z,"autovacuum")==0 ){ 1268 doAutovac = 1; 1269 }else if( strcmp(z,"cachesize")==0 ){ 1270 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1271 i++; 1272 cacheSize = integerValue(argv[i]); 1273 }else if( strcmp(z,"exclusive")==0 ){ 1274 doExclusive = 1; 1275 }else if( strcmp(z,"explain")==0 ){ 1276 g.bSqlOnly = 1; 1277 g.bExplain = 1; 1278 }else if( strcmp(z,"heap")==0 ){ 1279 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]); 1280 nHeap = integerValue(argv[i+1]); 1281 mnHeap = integerValue(argv[i+2]); 1282 i += 2; 1283 }else if( strcmp(z,"incrvacuum")==0 ){ 1284 doIncrvac = 1; 1285 }else if( strcmp(z,"journal")==0 ){ 1286 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1287 zJMode = argv[++i]; 1288 }else if( strcmp(z,"key")==0 ){ 1289 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1290 zKey = argv[++i]; 1291 }else if( strcmp(z,"lookaside")==0 ){ 1292 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]); 1293 nLook = integerValue(argv[i+1]); 1294 szLook = integerValue(argv[i+2]); 1295 i += 2; 1296 }else if( strcmp(z,"multithread")==0 ){ 1297 sqlite3_config(SQLITE_CONFIG_MULTITHREAD); 1298 }else if( strcmp(z,"nomemstat")==0 ){ 1299 sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0); 1300 }else if( strcmp(z,"nosync")==0 ){ 1301 noSync = 1; 1302 }else if( strcmp(z,"notnull")==0 ){ 1303 g.zNN = "NOT NULL"; 1304 #ifdef SQLITE_ENABLE_RBU 1305 }else if( strcmp(z,"rbu")==0 ){ 1306 sqlite3ota_create_vfs("rbu", 0); 1307 sqlite3_vfs_register(sqlite3_vfs_find("rbu"), 1); 1308 #endif 1309 }else if( strcmp(z,"pagesize")==0 ){ 1310 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1311 pageSize = integerValue(argv[++i]); 1312 }else if( strcmp(z,"pcache")==0 ){ 1313 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]); 1314 nPCache = integerValue(argv[i+1]); 1315 szPCache = integerValue(argv[i+2]); 1316 doPCache = 1; 1317 i += 2; 1318 }else if( strcmp(z,"primarykey")==0 ){ 1319 g.zPK = "PRIMARY KEY"; 1320 }else if( strcmp(z,"reprepare")==0 ){ 1321 g.bReprepare = 1; 1322 }else if( strcmp(z,"scratch")==0 ){ 1323 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]); 1324 nScratch = integerValue(argv[i+1]); 1325 szScratch = integerValue(argv[i+2]); 1326 i += 2; 1327 }else if( strcmp(z,"serialized")==0 ){ 1328 sqlite3_config(SQLITE_CONFIG_SERIALIZED); 1329 }else if( strcmp(z,"singlethread")==0 ){ 1330 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); 1331 }else if( strcmp(z,"sqlonly")==0 ){ 1332 g.bSqlOnly = 1; 1333 }else if( strcmp(z,"shrink-memory")==0 ){ 1334 g.bMemShrink = 1; 1335 }else if( strcmp(z,"size")==0 ){ 1336 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1337 g.szTest = integerValue(argv[++i]); 1338 }else if( strcmp(z,"stats")==0 ){ 1339 showStats = 1; 1340 }else if( strcmp(z,"temp")==0 ){ 1341 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1342 i++; 1343 if( argv[i][0]<'0' || argv[i][0]>'9' || argv[i][1]!=0 ){ 1344 fatal_error("argument to --temp should be integer between 0 and 9"); 1345 } 1346 g.eTemp = argv[i][0] - '0'; 1347 }else if( strcmp(z,"testset")==0 ){ 1348 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1349 zTSet = argv[++i]; 1350 }else if( strcmp(z,"trace")==0 ){ 1351 doTrace = 1; 1352 }else if( strcmp(z,"threads")==0 ){ 1353 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]); 1354 nThread = integerValue(argv[++i]); 1355 }else if( strcmp(z,"utf16le")==0 ){ 1356 zEncoding = "utf16le"; 1357 }else if( strcmp(z,"utf16be")==0 ){ 1358 zEncoding = "utf16be"; 1359 }else if( strcmp(z,"verify")==0 ){ 1360 g.bVerify = 1; 1361 }else if( strcmp(z,"without-rowid")==0 ){ 1362 g.zWR = "WITHOUT ROWID"; 1363 g.zPK = "PRIMARY KEY"; 1364 }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){ 1365 printf(zHelp, argv[0]); 1366 exit(0); 1367 }else{ 1368 fatal_error("unknown option: %s\nUse \"%s -?\" for help\n", 1369 argv[i], argv[0]); 1370 } 1371 }else if( zDbName==0 ){ 1372 zDbName = argv[i]; 1373 }else{ 1374 fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n", 1375 argv[i], argv[0]); 1376 } 1377 } 1378 if( zDbName!=0 ) unlink(zDbName); 1379 #if SQLITE_VERSION_NUMBER>=3006001 1380 if( nHeap>0 ){ 1381 pHeap = malloc( nHeap ); 1382 if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap); 1383 rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap); 1384 if( rc ) fatal_error("heap configuration failed: %d\n", rc); 1385 } 1386 if( doPCache ){ 1387 if( nPCache>0 && szPCache>0 ){ 1388 pPCache = malloc( nPCache*(sqlite3_int64)szPCache ); 1389 if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n", 1390 nPCache*(sqlite3_int64)szPCache); 1391 } 1392 rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache); 1393 if( rc ) fatal_error("pcache configuration failed: %d\n", rc); 1394 } 1395 if( nScratch>0 && szScratch>0 ){ 1396 pScratch = malloc( nScratch*(sqlite3_int64)szScratch ); 1397 if( pScratch==0 ) fatal_error("cannot allocate %lld-byte scratch\n", 1398 nScratch*(sqlite3_int64)szScratch); 1399 rc = sqlite3_config(SQLITE_CONFIG_SCRATCH, pScratch, szScratch, nScratch); 1400 if( rc ) fatal_error("scratch configuration failed: %d\n", rc); 1401 } 1402 if( nLook>0 ){ 1403 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0); 1404 } 1405 #endif 1406 1407 /* Open the database and the input file */ 1408 if( sqlite3_open(zDbName, &g.db) ){ 1409 fatal_error("Cannot open database file: %s\n", zDbName); 1410 } 1411 #if SQLITE_VERSION_NUMBER>=3006001 1412 if( nLook>0 && szLook>0 ){ 1413 pLook = malloc( nLook*szLook ); 1414 rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE, pLook, szLook,nLook); 1415 if( rc ) fatal_error("lookaside configuration failed: %d\n", rc); 1416 } 1417 #endif 1418 1419 /* Set database connection options */ 1420 sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc, 0, 0); 1421 #ifndef SQLITE_OMIT_DEPRECATED 1422 if( doTrace ) sqlite3_trace(g.db, traceCallback, 0); 1423 #endif 1424 speedtest1_exec("PRAGMA threads=%d", nThread); 1425 if( zKey ){ 1426 speedtest1_exec("PRAGMA key('%s')", zKey); 1427 } 1428 if( zEncoding ){ 1429 speedtest1_exec("PRAGMA encoding=%s", zEncoding); 1430 } 1431 if( doAutovac ){ 1432 speedtest1_exec("PRAGMA auto_vacuum=FULL"); 1433 }else if( doIncrvac ){ 1434 speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL"); 1435 } 1436 if( pageSize ){ 1437 speedtest1_exec("PRAGMA page_size=%d", pageSize); 1438 } 1439 if( cacheSize ){ 1440 speedtest1_exec("PRAGMA cache_size=%d", cacheSize); 1441 } 1442 if( noSync ) speedtest1_exec("PRAGMA synchronous=OFF"); 1443 if( doExclusive ){ 1444 speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE"); 1445 } 1446 if( zJMode ){ 1447 speedtest1_exec("PRAGMA journal_mode=%s", zJMode); 1448 } 1449 1450 if( g.bExplain ) printf(".explain\n.echo on\n"); 1451 if( strcmp(zTSet,"main")==0 ){ 1452 testset_main(); 1453 }else if( strcmp(zTSet,"debug1")==0 ){ 1454 testset_debug1(); 1455 }else if( strcmp(zTSet,"cte")==0 ){ 1456 testset_cte(); 1457 }else if( strcmp(zTSet,"rtree")==0 ){ 1458 #ifdef SQLITE_ENABLE_RTREE 1459 testset_rtree(6, 147); 1460 #else 1461 fatal_error("compile with -DSQLITE_ENABLE_RTREE to enable " 1462 "the R-Tree tests\n"); 1463 #endif 1464 }else{ 1465 fatal_error("unknown testset: \"%s\"\nChoices: main debug1 cte rtree\n", 1466 zTSet); 1467 } 1468 speedtest1_final(); 1469 1470 /* Database connection statistics printed after both prepared statements 1471 ** have been finalized */ 1472 #if SQLITE_VERSION_NUMBER>=3007009 1473 if( showStats ){ 1474 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0); 1475 printf("-- Lookaside Slots Used: %d (max %d)\n", iCur,iHi); 1476 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0); 1477 printf("-- Successful lookasides: %d\n", iHi); 1478 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0); 1479 printf("-- Lookaside size faults: %d\n", iHi); 1480 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0); 1481 printf("-- Lookaside OOM faults: %d\n", iHi); 1482 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0); 1483 printf("-- Pager Heap Usage: %d bytes\n", iCur); 1484 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1); 1485 printf("-- Page cache hits: %d\n", iCur); 1486 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1); 1487 printf("-- Page cache misses: %d\n", iCur); 1488 #if SQLITE_VERSION_NUMBER>=3007012 1489 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1); 1490 printf("-- Page cache writes: %d\n", iCur); 1491 #endif 1492 sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0); 1493 printf("-- Schema Heap Usage: %d bytes\n", iCur); 1494 sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0); 1495 printf("-- Statement Heap Usage: %d bytes\n", iCur); 1496 } 1497 #endif 1498 1499 sqlite3_close(g.db); 1500 1501 #if SQLITE_VERSION_NUMBER>=3006001 1502 /* Global memory usage statistics printed after the database connection 1503 ** has closed. Memory usage should be zero at this point. */ 1504 if( showStats ){ 1505 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0); 1506 printf("-- Memory Used (bytes): %d (max %d)\n", iCur,iHi); 1507 #if SQLITE_VERSION_NUMBER>=3007000 1508 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0); 1509 printf("-- Outstanding Allocations: %d (max %d)\n", iCur,iHi); 1510 #endif 1511 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0); 1512 printf("-- Pcache Overflow Bytes: %d (max %d)\n", iCur,iHi); 1513 sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHi, 0); 1514 printf("-- Scratch Overflow Bytes: %d (max %d)\n", iCur,iHi); 1515 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0); 1516 printf("-- Largest Allocation: %d bytes\n",iHi); 1517 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0); 1518 printf("-- Largest Pcache Allocation: %d bytes\n",iHi); 1519 sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHi, 0); 1520 printf("-- Largest Scratch Allocation: %d bytes\n", iHi); 1521 } 1522 #endif 1523 1524 #ifdef __linux__ 1525 if( showStats ){ 1526 displayLinuxIoStats(stdout); 1527 } 1528 #endif 1529 1530 /* Release memory */ 1531 free( pLook ); 1532 free( pPCache ); 1533 free( pScratch ); 1534 free( pHeap ); 1535 return 0; 1536 } 1537