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