AryaWu/sqlite
0
1/*2** A program for performance testing.3**4** To build this program against an historical version of SQLite for comparison5** testing:6**7** Unix:8**9** ./configure --all10** make clean speedtest111** mv speedtest1 speedtest1-current12** cp $HISTORICAL_SQLITE3_C_H .13** touch sqlite3.c sqlite3.h .target_source14** make speedtest115** mv speedtest1 speedtest1-baseline16**17** Windows:18**19** nmake /f Makefile.msc clean speedtest1.exe20** mv speedtest1.exe speedtest1-current.exe21** cp $HISTORICAL_SQLITE_C_H .22** touch sqlite3.c sqlite3.h .target_source23** nmake /f Makefile.msc speedtest1.exe24** mv speedtest1.exe speedtest1-baseline.exe25**26** The available command-line options are described below:27*/28static const char zHelp[] =29 "Usage: %s [--options] DATABASE\n"30 "Options:\n"31 " --autovacuum Enable AUTOVACUUM mode\n"32 " --big-transactions Add BEGIN/END around all large tests\n"33 " --cachesize N Set PRAGMA cache_size=N. Note: N is pages, not bytes\n"34 " --checkpoint Run PRAGMA wal_checkpoint after each test case\n"35 " --exclusive Enable locking_mode=EXCLUSIVE\n"36 " --explain Like --sqlonly but with added EXPLAIN keywords\n"37 " --fullfsync Enable fullfsync=TRUE\n"38 " --hard-heap-limit N The hard limit on the maximum heap size\n"39 " --heap SZ MIN Memory allocator uses SZ bytes & min allocation MIN\n"40 " --incrvacuum Enable incremenatal vacuum mode\n"41 " --journal M Set the journal_mode to M\n"42 " --key KEY Set the encryption key to KEY\n"43 " --lookaside N SZ Configure lookaside for N slots of SZ bytes each\n"44 " --memdb Use an in-memory database\n"45 " --mmap SZ MMAP the first SZ bytes of the database file\n"46 " --multithread Set multithreaded mode\n"47 " --nomemstat Disable memory statistics\n"48 " --nomutex Open db with SQLITE_OPEN_NOMUTEX\n"49 " --nosync Set PRAGMA synchronous=OFF\n"50 " --notnull Add NOT NULL constraints to table columns\n"51 " --output FILE Store SQL output in FILE\n"52 " --pagesize N Set the page size to N\n"53 " --pcache N SZ Configure N pages of pagecache each of size SZ bytes\n"54 " --primarykey Use PRIMARY KEY instead of UNIQUE where appropriate\n"55 " --repeat N Repeat each SELECT N times (default: 1)\n"56 " --reprepare Reprepare each statement upon every invocation\n"57 " --reserve N Reserve N bytes on each database page\n"58 " --script FILE Write an SQL script for the test into FILE\n"59 " --serialized Set serialized threading mode\n"60 " --singlethread Set single-threaded mode - disables all mutexing\n"61 " --sqlonly No-op. Only show the SQL that would have been run.\n"62 " --shrink-memory Invoke sqlite3_db_release_memory() frequently.\n"63 " --size N Relative test size. Default=100\n"64 " --soft-heap-limit N The soft limit on the maximum heap size\n"65 " --strict Use STRICT table where appropriate\n"66 " --stats Show statistics at the end\n"67 " --stmtscanstatus Activate SQLITE_DBCONFIG_STMT_SCANSTATUS\n"68 " --temp N N from 0 to 9. 0: no temp table. 9: all temp tables\n"69 " --testset T Run test-set T (main, cte, rtree, orm, fp, json,\n"70 " star, app, debug). Can be a comma-separated list\n"71 " of values, with /SCALE suffixes or macro \"mix1\"\n"72 " --trace Turn on SQL tracing\n"73 " --threads N Use up to N threads for sorting\n"74 " --utf16be Set text encoding to UTF-16BE\n"75 " --utf16le Set text encoding to UTF-16LE\n"76 " --verify Run additional verification steps\n"77 " --vfs NAME Use the given (preinstalled) VFS\n"78 " --without-rowid Use WITHOUT ROWID where appropriate\n"79;80 81#include "sqlite3.h"82#include <assert.h>83#include <stdio.h>84#include <stdlib.h>85#include <stdarg.h>86#include <string.h>87#include <ctype.h>88#ifndef _WIN3289# include <unistd.h>90#else91# include <io.h>92#endif93#define ISSPACE(X) isspace((unsigned char)(X))94#define ISDIGIT(X) isdigit((unsigned char)(X))95 96#if SQLITE_VERSION_NUMBER<300500097# define sqlite3_int64 sqlite_int6498#endif99 100typedef sqlite3_uint64 u64;101 102/*103** State structure for a Hash hash in progress104*/105typedef struct HashContext HashContext;106struct HashContext {107 unsigned char isInit; /* True if initialized */108 unsigned char i, j; /* State variables */109 unsigned char s[256]; /* State variables */110 unsigned char r[32]; /* Result */111};112 113 114/* All global state is held in this structure */115static struct Global {116 sqlite3 *db; /* The open database connection */117 const char *zDbName; /* Name of the database file */118 const char *zVfs; /* --vfs NAME */119 sqlite3_stmt *pStmt; /* Current SQL statement */120 sqlite3_int64 iStart; /* Start-time for the current test */121 sqlite3_int64 iTotal; /* Total time */122 int bWithoutRowid; /* True for --without-rowid */123 int bReprepare; /* True to reprepare the SQL on each rerun */124 int bSqlOnly; /* True to print the SQL once only */125 int bExplain; /* Print SQL with EXPLAIN prefix */126 int bVerify; /* Try to verify that results are correct */127 int bMemShrink; /* Call sqlite3_db_release_memory() often */128 int eTemp; /* 0: no TEMP. 9: always TEMP. */129 int szTest; /* Scale factor for test iterations */130 int szBase; /* Base size prior to testset scaling */131 int nRepeat; /* Repeat selects this many times */132 int doCheckpoint; /* Run PRAGMA wal_checkpoint after each trans */133 int nReserve; /* Reserve bytes */134 int stmtScanStatus; /* True to activate Stmt ScanStatus reporting */135 int doBigTransactions; /* Enable transactions on tests 410 and 510 */136 const char *zWR; /* Might be WITHOUT ROWID */137 const char *zNN; /* Might be NOT NULL */138 const char *zPK; /* Might be UNIQUE or PRIMARY KEY */139 unsigned int x, y; /* Pseudo-random number generator state */140 u64 nResByte; /* Total number of result bytes */141 int nResult; /* Size of the current result */142 char zResult[3000]; /* Text of the current result */143 FILE *pScript; /* Write an SQL script into this file */144#ifndef SPEEDTEST_OMIT_HASH145 FILE *hashFile; /* Store all hash results in this file */146 HashContext hash; /* Hash of all output */147#endif148} g;149 150/* Return " TEMP" or "", as appropriate for creating a table.151*/152static const char *isTemp(int N){153 return g.eTemp>=N ? " TEMP" : "";154}155 156/* Print an error message and exit */157static void fatal_error(const char *zMsg, ...){158 va_list ap;159 va_start(ap, zMsg);160 vfprintf(stderr, zMsg, ap);161 va_end(ap);162#ifdef SQLITE_SPEEDTEST1_WASM163 /* Emscripten complains when exit() is called and anything is left164 in the I/O buffers. */165 fflush(stdout);166 fflush(stderr);167#endif168 exit(1);169}170 171#ifndef SPEEDTEST_OMIT_HASH172/****************************************************************************173** Hash algorithm used to verify that compilation is not miscompiled174** in such a was as to generate an incorrect result.175*/176 177/*178** Initialize a new hash. iSize determines the size of the hash179** in bits and should be one of 224, 256, 384, or 512. Or iSize180** can be zero to use the default hash size of 256 bits.181*/182static void HashInit(void){183 unsigned int k;184 g.hash.i = 0;185 g.hash.j = 0;186 for(k=0; k<256; k++) g.hash.s[k] = k;187}188 189/*190** Make consecutive calls to the HashUpdate function to add new content191** to the hash192*/193static void HashUpdate(194 const unsigned char *aData,195 unsigned int nData196){197 unsigned char t;198 unsigned char i = g.hash.i;199 unsigned char j = g.hash.j;200 unsigned int k;201 if( g.hashFile ) fwrite(aData, 1, nData, g.hashFile);202 for(k=0; k<nData; k++){203 j += g.hash.s[i] + aData[k];204 t = g.hash.s[j];205 g.hash.s[j] = g.hash.s[i];206 g.hash.s[i] = t;207 i++;208 }209 g.hash.i = i;210 g.hash.j = j;211}212 213/*214** After all content has been added, invoke HashFinal() to compute215** the final hash. The hash result is stored in g.hash.r[].216*/217static void HashFinal(void){218 unsigned int k;219 unsigned char t, i, j;220 i = g.hash.i;221 j = g.hash.j;222 for(k=0; k<32; k++){223 i++;224 t = g.hash.s[i];225 j += t;226 g.hash.s[i] = g.hash.s[j];227 g.hash.s[j] = t;228 t += g.hash.s[i];229 g.hash.r[k] = g.hash.s[t];230 }231}232 233/* End of the Hash hashing logic234*****************************************************************************/235#endif /* SPEEDTEST_OMIT_HASH */236 237/*238** Return the value of a hexadecimal digit. Return -1 if the input239** is not a hex digit.240*/241static int hexDigitValue(char c){242 if( c>='0' && c<='9' ) return c - '0';243 if( c>='a' && c<='f' ) return c - 'a' + 10;244 if( c>='A' && c<='F' ) return c - 'A' + 10;245 return -1;246}247 248/* Provide an alternative to sqlite3_stricmp() in older versions of249** SQLite */250#if SQLITE_VERSION_NUMBER<3007011251# define sqlite3_stricmp strcmp252#endif253 254/*255** Interpret zArg as an integer value, possibly with suffixes.256*/257static int integerValue(const char *zArg){258 sqlite3_int64 v = 0;259 static const struct { char *zSuffix; int iMult; } aMult[] = {260 { "KiB", 1024 },261 { "MiB", 1024*1024 },262 { "GiB", 1024*1024*1024 },263 { "KB", 1000 },264 { "MB", 1000000 },265 { "GB", 1000000000 },266 { "K", 1000 },267 { "M", 1000000 },268 { "G", 1000000000 },269 };270 int i;271 int isNeg = 0;272 if( zArg[0]=='-' ){273 isNeg = 1;274 zArg++;275 }else if( zArg[0]=='+' ){276 zArg++;277 }278 if( zArg[0]=='0' && zArg[1]=='x' ){279 int x;280 zArg += 2;281 while( (x = hexDigitValue(zArg[0]))>=0 ){282 v = (v<<4) + x;283 zArg++;284 }285 }else{286 while( isdigit(zArg[0]) ){287 v = v*10 + zArg[0] - '0';288 zArg++;289 }290 }291 for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){292 if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){293 v *= aMult[i].iMult;294 break;295 }296 }297 if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");298 return (int)(isNeg? -v : v);299}300 301/* Return the current wall-clock time, in milliseconds */302sqlite3_int64 speedtest1_timestamp(void){303#if SQLITE_VERSION_NUMBER<3005000304 return 0;305#else306 static sqlite3_vfs *clockVfs = 0;307 sqlite3_int64 t;308 if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);309#if SQLITE_VERSION_NUMBER>=3007000310 if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){311 clockVfs->xCurrentTimeInt64(clockVfs, &t);312 }else313#endif314 {315 double r;316 clockVfs->xCurrentTime(clockVfs, &r);317 t = (sqlite3_int64)(r*86400000.0);318 }319 return t;320#endif321}322 323/* Return a pseudo-random unsigned integer */324unsigned int speedtest1_random(void){325 g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001);326 g.y = g.y*1103515245 + 12345;327 return g.x ^ g.y;328}329 330/* Map the value in within the range of 1...limit into another331** number in a way that is chatic and invertable.332*/333unsigned swizzle(unsigned in, unsigned limit){334 unsigned out = 0;335 while( limit ){336 out = (out<<1) | (in&1);337 in >>= 1;338 limit >>= 1;339 }340 return out;341}342 343/* Round up a number so that it is a power of two minus one344*/345unsigned roundup_allones(unsigned limit){346 unsigned m = 1;347 while( m<limit ) m = (m<<1)+1;348 return m;349}350 351/* The speedtest1_numbername procedure below converts its argment (an integer)352** into a string which is the English-language name for that number.353** The returned string should be freed with sqlite3_free().354**355** Example:356**357** speedtest1_numbername(123) -> "one hundred twenty three"358*/359int speedtest1_numbername(unsigned int n, char *zOut, int nOut){360 static const char *ones[] = { "zero", "one", "two", "three", "four", "five", 361 "six", "seven", "eight", "nine", "ten", "eleven", "twelve", 362 "thirteen", "fourteen", "fifteen", "sixteen", "seventeen",363 "eighteen", "nineteen" };364 static const char *tens[] = { "", "ten", "twenty", "thirty", "forty",365 "fifty", "sixty", "seventy", "eighty", "ninety" };366 int i = 0;367 368 if( n>=1000000000 ){369 i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i);370 sqlite3_snprintf(nOut-i, zOut+i, " billion");371 i += (int)strlen(zOut+i);372 n = n % 1000000000;373 }374 if( n>=1000000 ){375 if( i && i<nOut-1 ) zOut[i++] = ' ';376 i += speedtest1_numbername(n/1000000, zOut+i, nOut-i);377 sqlite3_snprintf(nOut-i, zOut+i, " million");378 i += (int)strlen(zOut+i);379 n = n % 1000000;380 }381 if( n>=1000 ){382 if( i && i<nOut-1 ) zOut[i++] = ' ';383 i += speedtest1_numbername(n/1000, zOut+i, nOut-i);384 sqlite3_snprintf(nOut-i, zOut+i, " thousand");385 i += (int)strlen(zOut+i);386 n = n % 1000;387 }388 if( n>=100 ){389 if( i && i<nOut-1 ) zOut[i++] = ' ';390 sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]);391 i += (int)strlen(zOut+i);392 n = n % 100;393 }394 if( n>=20 ){395 if( i && i<nOut-1 ) zOut[i++] = ' ';396 sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]);397 i += (int)strlen(zOut+i);398 n = n % 10;399 }400 if( n>0 ){401 if( i && i<nOut-1 ) zOut[i++] = ' ';402 sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]);403 i += (int)strlen(zOut+i);404 }405 if( i==0 ){406 sqlite3_snprintf(nOut-i, zOut+i, "zero");407 i += (int)strlen(zOut+i);408 }409 return i;410}411 412 413/* Start a new test case */414#define NAMEWIDTH 60415static const char zDots[] =416 ".......................................................................";417static int iTestNumber = 0; /* Current test # for begin/end_test(). */418void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){419 int n = (int)strlen(zTestName);420 char *zName;421 va_list ap;422 iTestNumber = iTestNum;423 va_start(ap, zTestName);424 zName = sqlite3_vmprintf(zTestName, ap);425 va_end(ap);426 n = (int)strlen(zName);427 if( n>NAMEWIDTH ){428 zName[NAMEWIDTH] = 0;429 n = NAMEWIDTH;430 }431 if( g.pScript ){432 fprintf(g.pScript,"-- begin test %d %.*s\n", iTestNumber, n, zName)433 /* maintenance reminder: ^^^ code in ext/wasm expects %d to be434 ** field #4 (as in: cut -d' ' -f4). */;435 }436 if( g.bSqlOnly ){437 printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots);438 }else{439 printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);440 fflush(stdout);441 }442 sqlite3_free(zName);443 g.nResult = 0;444 g.iStart = speedtest1_timestamp();445 g.x = 0xad131d0b;446 g.y = 0x44f9eac8;447}448 449/* Forward reference */450void speedtest1_exec(const char*,...);451 452/* Complete a test case */453void speedtest1_end_test(void){454 sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;455 if( g.doCheckpoint ) speedtest1_exec("PRAGMA wal_checkpoint;");456 assert( iTestNumber > 0 );457 if( g.pScript ){458 fprintf(g.pScript,"-- end test %d\n", iTestNumber);459 }460 if( !g.bSqlOnly ){461 g.iTotal += iElapseTime;462 printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000));463 }464 if( g.pStmt ){465 sqlite3_finalize(g.pStmt);466 g.pStmt = 0;467 }468 iTestNumber = 0;469}470 471/* Report end of testing */472void speedtest1_final(void){473 if( !g.bSqlOnly ){474 printf(" TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,475 (int)(g.iTotal/1000), (int)(g.iTotal%1000));476 }477 if( g.bVerify ){478#ifndef SPEEDTEST_OMIT_HASH479 int i;480#endif481 printf("Verification Hash: %llu ", g.nResByte);482#ifndef SPEEDTEST_OMIT_HASH483 HashUpdate((const unsigned char*)"\n", 1);484 HashFinal();485 for(i=0; i<24; i++){486 printf("%02x", g.hash.r[i]);487 }488 if( g.hashFile && g.hashFile!=stdout ) fclose(g.hashFile);489#endif490 printf("\n");491 }492}493 494/* Print an SQL statement to standard output */495static void printSql(const char *zSql){496 int n = (int)strlen(zSql);497 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ){ n--; }498 if( g.bExplain ) printf("EXPLAIN ");499 printf("%.*s;\n", n, zSql);500 if( g.bExplain501#if SQLITE_VERSION_NUMBER>=3007017 502 && ( sqlite3_strglob("CREATE *", zSql)==0503 || sqlite3_strglob("DROP *", zSql)==0504 || sqlite3_strglob("ALTER *", zSql)==0505 )506#endif507 ){508 printf("%.*s;\n", n, zSql);509 }510}511 512/* Shrink memory used, if appropriate and if the SQLite version is capable513** of doing so.514*/515void speedtest1_shrink_memory(void){516#if SQLITE_VERSION_NUMBER>=3007010517 if( g.bMemShrink ) sqlite3_db_release_memory(g.db);518#endif519}520 521/* Run SQL */522void speedtest1_exec(const char *zFormat, ...){523 va_list ap;524 char *zSql;525 va_start(ap, zFormat);526 zSql = sqlite3_vmprintf(zFormat, ap);527 va_end(ap);528 if( g.bSqlOnly ){529 printSql(zSql);530 }else{531 char *zErrMsg = 0;532 int rc;533 if( g.pScript ){534 fprintf(g.pScript,"%s;\n",zSql);535 }536 rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);537 if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);538 if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));539 }540 sqlite3_free(zSql);541 speedtest1_shrink_memory();542}543 544/* Run SQL and return the first column of the first row as a string. The545** returned string is obtained from sqlite_malloc() and must be freed by546** the caller.547*/548char *speedtest1_once(const char *zFormat, ...){549 va_list ap;550 char *zSql;551 sqlite3_stmt *pStmt;552 char *zResult = 0;553 int rc;554 va_start(ap, zFormat);555 zSql = sqlite3_vmprintf(zFormat, ap);556 va_end(ap);557 if( g.bSqlOnly ){558 printSql(zSql);559 }else{560 int rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, 0);561 if( rc ){562 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));563 }564 if( g.pScript ){565 char *z = sqlite3_expanded_sql(pStmt);566 fprintf(g.pScript,"%s\n",z);567 sqlite3_free(z);568 }569 if( sqlite3_step(pStmt)==SQLITE_ROW ){570 const char *z = (const char*)sqlite3_column_text(pStmt, 0);571 if( z ) zResult = sqlite3_mprintf("%s", z);572 }573 rc = sqlite3_reset(pStmt);574 if( rc!=SQLITE_OK ){575 fatal_error("%s\nError code %d: %s\n",576 sqlite3_sql(pStmt), rc, sqlite3_errmsg(g.db));577 }578 sqlite3_finalize(pStmt);579 }580 sqlite3_free(zSql);581 speedtest1_shrink_memory();582 return zResult;583}584 585/* Prepare an SQL statement */586void speedtest1_prepare(const char *zFormat, ...){587 va_list ap;588 char *zSql;589 va_start(ap, zFormat);590 zSql = sqlite3_vmprintf(zFormat, ap);591 va_end(ap);592 if( g.bSqlOnly ){593 printSql(zSql);594 }else{595 int rc;596 if( g.pStmt ) sqlite3_finalize(g.pStmt);597 rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);598 if( rc ){599 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));600 }601 }602 sqlite3_free(zSql);603}604 605/* Run an SQL statement previously prepared */606void speedtest1_run(void){607 int i, n, len, rc;608 if( g.bSqlOnly ) return;609 assert( g.pStmt );610 g.nResult = 0;611 if( g.pScript ){612 char *z = sqlite3_expanded_sql(g.pStmt);613 fprintf(g.pScript,"%s\n",z);614 sqlite3_free(z);615 }616 while( sqlite3_step(g.pStmt)==SQLITE_ROW ){617 n = sqlite3_column_count(g.pStmt);618 for(i=0; i<n; i++){619 const char *z = (const char*)sqlite3_column_text(g.pStmt, i);620 if( z==0 ) z = "nil";621 len = (int)strlen(z);622#ifndef SPEEDTEST_OMIT_HASH623 if( g.bVerify ){624 int eType = sqlite3_column_type(g.pStmt, i);625 unsigned char zPrefix[2];626 zPrefix[0] = '\n';627 zPrefix[1] = "-IFTBN"[eType];628 if( g.nResByte ){629 HashUpdate(zPrefix, 2);630 }else{631 HashUpdate(zPrefix+1, 1);632 }633 if( eType==SQLITE_FLOAT ){634 /* Omit the value of floating-point results from the verification635 ** hash. The only thing we record is the fact that the result was636 ** a floating-point value. */637 g.nResByte += 2;638 }else if( eType==SQLITE_BLOB ){639 int nBlob = sqlite3_column_bytes(g.pStmt, i);640 int iBlob;641 unsigned char zChar[2];642 const unsigned char *aBlob = sqlite3_column_blob(g.pStmt, i);643 for(iBlob=0; iBlob<nBlob; iBlob++){644 zChar[0] = "0123456789abcdef"[aBlob[iBlob]>>4];645 zChar[1] = "0123456789abcdef"[aBlob[iBlob]&15];646 HashUpdate(zChar,2);647 }648 g.nResByte += nBlob*2 + 2;649 }else{650 HashUpdate((unsigned char*)z, len);651 g.nResByte += len + 2;652 }653 }654#endif655 if( g.nResult+len<sizeof(g.zResult)-2 ){656 if( g.nResult>0 ) g.zResult[g.nResult++] = ' ';657 memcpy(g.zResult + g.nResult, z, len+1);658 g.nResult += len;659 }660 }661 }662#if SQLITE_VERSION_NUMBER>=3006001663 if( g.bReprepare ){664 sqlite3_stmt *pNew;665 sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0);666 rc = sqlite3_finalize(g.pStmt);667 if( rc!=SQLITE_OK ){668 fatal_error("%s\nError code %d: %s\n",669 sqlite3_sql(pNew), rc, sqlite3_errmsg(g.db));670 }671 g.pStmt = pNew;672 }else673#endif674 {675 rc = sqlite3_reset(g.pStmt);676 if( rc!=SQLITE_OK ){677 fatal_error("%s\nError code %d: %s\n",678 sqlite3_sql(g.pStmt), rc, sqlite3_errmsg(g.db));679 }680 }681 speedtest1_shrink_memory();682}683 684#ifndef SQLITE_OMIT_DEPRECATED685/* The sqlite3_trace() callback function */686static void traceCallback(void *NotUsed, const char *zSql){687 int n = (int)strlen(zSql);688 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ) n--;689 fprintf(stderr,"%.*s;\n", n, zSql);690}691#endif /* SQLITE_OMIT_DEPRECATED */692 693/* Substitute random() function that gives the same random694** sequence on each run, for repeatability. */695static void randomFunc(696 sqlite3_context *context,697 int NotUsed,698 sqlite3_value **NotUsed2699){700 sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random());701}702 703/* Estimate the square root of an integer */704static int est_square_root(int x){705 int y0 = x/2;706 int y1;707 int n;708 for(n=0; y0>0 && n<10; n++){709 y1 = (y0 + x/y0)/2;710 if( y1==y0 ) break;711 y0 = y1;712 }713 return y0;714}715 716 717#if SQLITE_VERSION_NUMBER<3005004718/*719** An implementation of group_concat(). Used only when testing older720** versions of SQLite that lack the built-in group_concat().721*/722struct groupConcat {723 char *z;724 int nAlloc;725 int nUsed;726};727static void groupAppend(struct groupConcat *p, const char *z, int n){728 if( p->nUsed+n >= p->nAlloc ){729 int n2 = (p->nAlloc+n+1)*2;730 char *z2 = sqlite3_realloc(p->z, n2);731 if( z2==0 ) return;732 p->z = z2;733 p->nAlloc = n2;734 }735 memcpy(p->z+p->nUsed, z, n);736 p->nUsed += n;737}738static void groupStep(739 sqlite3_context *context,740 int argc,741 sqlite3_value **argv742){743 const char *zVal;744 struct groupConcat *p;745 const char *zSep;746 int nVal, nSep;747 assert( argc==1 || argc==2 );748 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;749 p= (struct groupConcat*)sqlite3_aggregate_context(context, sizeof(*p));750 751 if( p ){752 int firstTerm = p->nUsed==0;753 if( !firstTerm ){754 if( argc==2 ){755 zSep = (char*)sqlite3_value_text(argv[1]);756 nSep = sqlite3_value_bytes(argv[1]);757 }else{758 zSep = ",";759 nSep = 1;760 }761 if( nSep ) groupAppend(p, zSep, nSep);762 }763 zVal = (char*)sqlite3_value_text(argv[0]);764 nVal = sqlite3_value_bytes(argv[0]);765 if( zVal ) groupAppend(p, zVal, nVal);766 }767}768static void groupFinal(sqlite3_context *context){769 struct groupConcat *p;770 p = sqlite3_aggregate_context(context, 0);771 if( p && p->z ){772 p->z[p->nUsed] = 0;773 sqlite3_result_text(context, p->z, p->nUsed, sqlite3_free);774 }775}776#endif777 778/*779** The main and default testset780*/781void testset_main(void){782 int i; /* Loop counter */783 int n; /* iteration count */784 int sz; /* Size of the tables */785 int maxb; /* Maximum swizzled value */786 unsigned x1 = 0, x2 = 0; /* Parameters */787 int len = 0; /* Length of the zNum[] string */788 char zNum[2000]; /* A number name */789 790 sz = n = g.szTest*500;791 zNum[0] = 0;792 maxb = roundup_allones(sz);793 speedtest1_begin_test(100, "%d INSERTs into table with no index", n);794 speedtest1_exec("BEGIN");795 speedtest1_exec("CREATE%s TABLE z1(a INTEGER %s, b INTEGER %s, c TEXT %s);",796 isTemp(9), g.zNN, g.zNN, g.zNN);797 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2,?3); -- %d times", n);798 for(i=1; i<=n; i++){799 x1 = swizzle(i,maxb);800 speedtest1_numbername(x1, zNum, sizeof(zNum));801 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);802 sqlite3_bind_int(g.pStmt, 2, i);803 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);804 speedtest1_run();805 }806 speedtest1_exec("COMMIT");807 speedtest1_end_test();808 809 810 n = sz;811 speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n);812 speedtest1_exec("BEGIN");813 speedtest1_exec(814 "CREATE%s TABLE z2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",815 isTemp(5), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);816 speedtest1_prepare("INSERT INTO z2 VALUES(?1,?2,?3); -- %d times", n);817 for(i=1; i<=n; i++){818 x1 = swizzle(i,maxb);819 speedtest1_numbername(x1, zNum, sizeof(zNum));820 sqlite3_bind_int(g.pStmt, 1, i);821 sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1);822 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);823 speedtest1_run();824 }825 speedtest1_exec("COMMIT");826 speedtest1_end_test();827 828 829 n = sz;830 speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n);831 speedtest1_exec("BEGIN");832 speedtest1_exec(833 "CREATE%s TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",834 isTemp(3), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);835 speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n);836 for(i=1; i<=n; i++){837 x1 = swizzle(i,maxb);838 speedtest1_numbername(x1, zNum, sizeof(zNum));839 sqlite3_bind_int(g.pStmt, 2, i);840 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);841 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);842 speedtest1_run();843 }844 speedtest1_exec("COMMIT");845 speedtest1_end_test();846 847#if SQLITE_VERSION_NUMBER<3005004848 sqlite3_create_function(g.db, "group_concat", 1, SQLITE_UTF8, 0,849 0, groupStep, groupFinal);850#endif851 852 n = 25;853 speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);854 speedtest1_exec("BEGIN");855 speedtest1_prepare(856 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"857 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n858 );859 for(i=1; i<=n; i++){860 if( (i-1)%g.nRepeat==0 ){861 x1 = speedtest1_random()%maxb;862 x2 = speedtest1_random()%10 + sz/5000 + x1;863 }864 sqlite3_bind_int(g.pStmt, 1, x1);865 sqlite3_bind_int(g.pStmt, 2, x2);866 speedtest1_run();867 }868 speedtest1_exec("COMMIT");869 speedtest1_end_test();870 871 872 n = 10;873 speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);874 speedtest1_exec("BEGIN");875 speedtest1_prepare(876 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"877 " WHERE c LIKE ?1; -- %d times", n878 );879 for(i=1; i<=n; i++){880 if( (i-1)%g.nRepeat==0 ){881 x1 = speedtest1_random()%maxb;882 zNum[0] = '%';883 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);884 zNum[len] = '%';885 zNum[len+1] = 0;886 }887 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);888 speedtest1_run();889 }890 speedtest1_exec("COMMIT");891 speedtest1_end_test();892 893 894 n = 10;895 speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);896 speedtest1_exec("BEGIN");897 speedtest1_prepare(898 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"899 " ORDER BY a; -- %d times", n900 );901 for(i=1; i<=n; i++){902 if( (i-1)%g.nRepeat==0 ){903 x1 = speedtest1_random()%maxb;904 zNum[0] = '%';905 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);906 zNum[len] = '%';907 zNum[len+1] = 0;908 }909 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);910 speedtest1_run();911 }912 speedtest1_exec("COMMIT");913 speedtest1_end_test();914 915 n = 10; /* g.szTest/5; */916 speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);917 speedtest1_exec("BEGIN");918 speedtest1_prepare(919 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"920 " ORDER BY a LIMIT 10; -- %d times", n921 );922 for(i=1; i<=n; i++){923 if( (i-1)%g.nRepeat==0 ){924 x1 = speedtest1_random()%maxb;925 zNum[0] = '%';926 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);927 zNum[len] = '%';928 zNum[len+1] = 0;929 }930 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);931 speedtest1_run();932 }933 speedtest1_exec("COMMIT");934 speedtest1_end_test();935 936 937 speedtest1_begin_test(150, "CREATE INDEX five times");938 speedtest1_exec("BEGIN;");939 speedtest1_exec("CREATE UNIQUE INDEX t1b ON z1(b);");940 speedtest1_exec("CREATE INDEX t1c ON z1(c);");941 speedtest1_exec("CREATE UNIQUE INDEX t2b ON z2(b);");942 speedtest1_exec("CREATE INDEX t2c ON z2(c DESC);");943 speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");944 speedtest1_exec("COMMIT;");945 speedtest1_end_test();946 947 948 n = sz/5;949 speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);950 speedtest1_exec("BEGIN");951 speedtest1_prepare(952 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"953 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n954 );955 for(i=1; i<=n; i++){956 if( (i-1)%g.nRepeat==0 ){957 x1 = speedtest1_random()%maxb;958 x2 = speedtest1_random()%10 + sz/5000 + x1;959 }960 sqlite3_bind_int(g.pStmt, 1, x1);961 sqlite3_bind_int(g.pStmt, 2, x2);962 speedtest1_run();963 }964 speedtest1_exec("COMMIT");965 speedtest1_end_test();966 967 968 n = sz/5;969 speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);970 speedtest1_exec("BEGIN");971 speedtest1_prepare(972 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z2\n"973 " WHERE a BETWEEN ?1 AND ?2; -- %d times", n974 );975 for(i=1; i<=n; i++){976 if( (i-1)%g.nRepeat==0 ){977 x1 = speedtest1_random()%maxb;978 x2 = speedtest1_random()%10 + sz/5000 + x1;979 }980 sqlite3_bind_int(g.pStmt, 1, x1);981 sqlite3_bind_int(g.pStmt, 2, x2);982 speedtest1_run();983 }984 speedtest1_exec("COMMIT");985 speedtest1_end_test();986 987 988 n = sz/5;989 speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);990 speedtest1_exec("BEGIN");991 speedtest1_prepare(992 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"993 " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n994 );995 for(i=1; i<=n; i++){996 if( (i-1)%g.nRepeat==0 ){997 x1 = swizzle(i, maxb);998 len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);999 }1000 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);1001 speedtest1_run();1002 }1003 speedtest1_exec("COMMIT");1004 speedtest1_end_test();1005 1006 n = sz;1007 speedtest1_begin_test(180, "%d INSERTS with three indexes", n);1008 speedtest1_exec("BEGIN");1009 speedtest1_exec(1010 "CREATE%s TABLE t4(\n"1011 " a INTEGER %s %s,\n"1012 " b INTEGER %s,\n"1013 " c TEXT %s\n"1014 ") %s",1015 isTemp(1), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);1016 speedtest1_exec("CREATE INDEX t4b ON t4(b)");1017 speedtest1_exec("CREATE INDEX t4c ON t4(c)");1018 speedtest1_exec("INSERT INTO t4 SELECT * FROM z1");1019 speedtest1_exec("COMMIT");1020 speedtest1_end_test();1021 1022 n = sz;1023 speedtest1_begin_test(190, "DELETE and REFILL one table", n);1024 speedtest1_exec("DELETE FROM z2;");1025 speedtest1_exec("INSERT INTO z2 SELECT * FROM z1;");1026 speedtest1_end_test();1027 1028 1029 speedtest1_begin_test(200, "VACUUM");1030 speedtest1_exec("VACUUM");1031 speedtest1_end_test();1032 1033 1034 speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query");1035 speedtest1_exec("ALTER TABLE z2 ADD COLUMN d INT DEFAULT 123");1036 speedtest1_exec("SELECT sum(d) FROM z2");1037 speedtest1_end_test();1038 1039 1040 n = sz/5;1041 speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n);1042 speedtest1_exec("BEGIN");1043 speedtest1_prepare(1044 "UPDATE z2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n1045 );1046 for(i=1; i<=n; i++){1047 x1 = speedtest1_random()%maxb;1048 x2 = speedtest1_random()%10 + sz/5000 + x1;1049 sqlite3_bind_int(g.pStmt, 1, x1);1050 sqlite3_bind_int(g.pStmt, 2, x2);1051 speedtest1_run();1052 }1053 speedtest1_exec("COMMIT");1054 speedtest1_end_test();1055 1056 1057 n = sz;1058 speedtest1_begin_test(240, "%d UPDATES of individual rows", n);1059 speedtest1_exec("BEGIN");1060 speedtest1_prepare(1061 "UPDATE z2 SET d=b*3 WHERE a=?1; -- %d times", n1062 );1063 for(i=1; i<=n; i++){1064 x1 = speedtest1_random()%sz + 1;1065 sqlite3_bind_int(g.pStmt, 1, x1);1066 speedtest1_run();1067 }1068 speedtest1_exec("COMMIT");1069 speedtest1_end_test();1070 1071 speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz);1072 speedtest1_exec("UPDATE z2 SET d=b*4");1073 speedtest1_end_test();1074 1075 1076 speedtest1_begin_test(260, "Query added column after filling");1077 speedtest1_exec("SELECT sum(d) FROM z2");1078 speedtest1_end_test();1079 1080 1081 1082 n = sz/5;1083 speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n);1084 speedtest1_exec("BEGIN");1085 speedtest1_prepare(1086 "DELETE FROM z2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n1087 );1088 for(i=1; i<=n; i++){1089 x1 = speedtest1_random()%maxb + 1;1090 x2 = speedtest1_random()%10 + sz/5000 + x1;1091 sqlite3_bind_int(g.pStmt, 1, x1);1092 sqlite3_bind_int(g.pStmt, 2, x2);1093 speedtest1_run();1094 }1095 speedtest1_exec("COMMIT");1096 speedtest1_end_test();1097 1098 1099 n = sz;1100 speedtest1_begin_test(280, "%d DELETEs of individual rows", n);1101 speedtest1_exec("BEGIN");1102 speedtest1_prepare(1103 "DELETE FROM t3 WHERE a=?1; -- %d times", n1104 );1105 for(i=1; i<=n; i++){1106 x1 = speedtest1_random()%sz + 1;1107 sqlite3_bind_int(g.pStmt, 1, x1);1108 speedtest1_run();1109 }1110 speedtest1_exec("COMMIT");1111 speedtest1_end_test();1112 1113 1114 speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);1115 speedtest1_exec("REPLACE INTO z2(a,b,c) SELECT a,b,c FROM z1");1116 speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM z1");1117 speedtest1_end_test();1118 1119 speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);1120 speedtest1_exec("DELETE FROM z2;");1121 speedtest1_exec("INSERT INTO z2(a,b,c)\n"1122 " SELECT a,b,c FROM z1 WHERE (b&1)==(a&1);");1123 speedtest1_exec("INSERT INTO z2(a,b,c)\n"1124 " SELECT a,b,c FROM z1 WHERE (b&1)<>(a&1);");1125 speedtest1_end_test();1126 1127 1128 n = sz/5;1129 speedtest1_begin_test(310, "%d four-ways joins", n);1130 speedtest1_exec("BEGIN");1131 speedtest1_prepare(1132 "SELECT z1.c FROM z1, z2, t3, t4\n"1133 " WHERE t4.a BETWEEN ?1 AND ?2\n"1134 " AND t3.a=t4.b\n"1135 " AND z2.a=t3.b\n"1136 " AND z1.c=z2.c;"1137 );1138 for(i=1; i<=n; i++){1139 x1 = speedtest1_random()%sz + 1;1140 x2 = speedtest1_random()%10 + x1 + 4;1141 sqlite3_bind_int(g.pStmt, 1, x1);1142 sqlite3_bind_int(g.pStmt, 2, x2);1143 speedtest1_run();1144 }1145 speedtest1_exec("COMMIT");1146 speedtest1_end_test();1147 1148 speedtest1_begin_test(320, "subquery in result set", n);1149 speedtest1_prepare(1150 "SELECT sum(a), max(c),\n"1151 " avg((SELECT a FROM z2 WHERE 5+z2.b=z1.b) AND rowid<?1), max(c)\n"1152 " FROM z1 WHERE rowid<?1;"1153 );1154 sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);1155 speedtest1_run();1156 speedtest1_end_test();1157 1158 sz = n = g.szTest*700;1159 zNum[0] = 0;1160 maxb = roundup_allones(sz/3);1161 speedtest1_begin_test(400, "%d REPLACE ops on an IPK", n);1162 speedtest1_exec("BEGIN");1163 speedtest1_exec("CREATE%s TABLE t5(a INTEGER PRIMARY KEY, b %s);",1164 isTemp(9), g.zNN);1165 speedtest1_prepare("REPLACE INTO t5 VALUES(?1,?2); -- %d times",n);1166 for(i=1; i<=n; i++){1167 x1 = swizzle(i,maxb);1168 speedtest1_numbername(i, zNum, sizeof(zNum));1169 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);1170 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);1171 speedtest1_run();1172 }1173 speedtest1_exec("COMMIT");1174 speedtest1_end_test();1175 speedtest1_begin_test(410, "%d SELECTS on an IPK", n);1176 if( g.doBigTransactions ){1177 /* Historical note: tests 410 and 510 have historically not used1178 ** explicit transactions. The --big-transactions flag was added1179 ** 2022-09-08 to support the WASM/OPFS build, as the run-times1180 ** approach 1 minute for each of these tests if they're not in an1181 ** explicit transaction. The run-time effect of --big-transaciions1182 ** on native builds is negligible. */1183 speedtest1_exec("BEGIN");1184 }1185 speedtest1_prepare("SELECT b FROM t5 WHERE a=?1; -- %d times",n);1186 for(i=1; i<=n; i++){1187 x1 = swizzle(i,maxb);1188 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);1189 speedtest1_run();1190 }1191 if( g.doBigTransactions ){1192 speedtest1_exec("COMMIT");1193 }1194 speedtest1_end_test();1195 1196 sz = n = g.szTest*700;1197 zNum[0] = 0;1198 maxb = roundup_allones(sz/3);1199 speedtest1_begin_test(500, "%d REPLACE on TEXT PK", n);1200 speedtest1_exec("BEGIN");