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 " --big-transactions Add BEGIN/END around all large tests\n"
11 " --cachesize N Set PRAGMA cache_size=N. Note: N is pages, not bytes\n"
12 " --checkpoint Run PRAGMA wal_checkpoint after each test case\n"
13 " --exclusive Enable locking_mode=EXCLUSIVE\n"
14 " --explain Like --sqlonly but with added EXPLAIN keywords\n"
15 " --heap SZ MIN Memory allocator uses SZ bytes & min allocation MIN\n"
16 " --incrvacuum Enable incremenatal vacuum mode\n"
17 " --journal M Set the journal_mode to M\n"
18 " --key KEY Set the encryption key to KEY\n"
19 " --lookaside N SZ Configure lookaside for N slots of SZ bytes each\n"
20 " --memdb Use an in-memory database\n"
21 " --mmap SZ MMAP the first SZ bytes of the database file\n"
22 " --multithread Set multithreaded mode\n"
23 " --nomemstat Disable memory statistics\n"
24 " --nomutex Open db with SQLITE_OPEN_NOMUTEX\n"
25 " --nosync Set PRAGMA synchronous=OFF\n"
26 " --notnull Add NOT NULL constraints to table columns\n"
27 " --output FILE Store SQL output in FILE\n"
28 " --pagesize N Set the page size to N\n"
29 " --pcache N SZ Configure N pages of pagecache each of size SZ bytes\n"
30 " --primarykey Use PRIMARY KEY instead of UNIQUE where appropriate\n"
31 " --repeat N Repeat each SELECT N times (default: 1)\n"
32 " --reprepare Reprepare each statement upon every invocation\n"
33 " --reserve N Reserve N bytes on each database page\n"
34 " --script FILE Write an SQL script for the test into FILE\n"
35 " --serialized Set serialized threading mode\n"
36 " --singlethread Set single-threaded mode - disables all mutexing\n"
37 " --sqlonly No-op. Only show the SQL that would have been run.\n"
38 " --shrink-memory Invoke sqlite3_db_release_memory() frequently.\n"
39 " --size N Relative test size. Default=100\n"
40 " --strict Use STRICT table where appropriate\n"
41 " --stats Show statistics at the end\n"
42 " --temp N N from 0 to 9. 0: no temp table. 9: all temp tables\n"
43 " --testset T Run test-set T (main, cte, rtree, orm, fp, debug)\n"
44 " --trace Turn on SQL tracing\n"
45 " --threads N Use up to N threads for sorting\n"
46 " --utf16be Set text encoding to UTF-16BE\n"
47 " --utf16le Set text encoding to UTF-16LE\n"
48 " --verify Run additional verification steps\n"
49 " --vfs NAME Use the given (preinstalled) VFS\n"
50 " --without-rowid Use WITHOUT ROWID where appropriate\n"
51 ;
52
53 #include "sqlite3.h"
54 #include <assert.h>
55 #include <stdio.h>
56 #include <stdlib.h>
57 #include <stdarg.h>
58 #include <string.h>
59 #include <ctype.h>
60 #ifndef _WIN32
61 # include <unistd.h>
62 #else
63 # include <io.h>
64 #endif
65 #define ISSPACE(X) isspace((unsigned char)(X))
66 #define ISDIGIT(X) isdigit((unsigned char)(X))
67
68 #if SQLITE_VERSION_NUMBER<3005000
69 # define sqlite3_int64 sqlite_int64
70 #endif
71
72 typedef sqlite3_uint64 u64;
73
74 /*
75 ** State structure for a Hash hash in progress
76 */
77 typedef struct HashContext HashContext;
78 struct HashContext {
79 unsigned char isInit; /* True if initialized */
80 unsigned char i, j; /* State variables */
81 unsigned char s[256]; /* State variables */
82 unsigned char r[32]; /* Result */
83 };
84
85
86 /* All global state is held in this structure */
87 static struct Global {
88 sqlite3 *db; /* The open database connection */
89 sqlite3_stmt *pStmt; /* Current SQL statement */
90 sqlite3_int64 iStart; /* Start-time for the current test */
91 sqlite3_int64 iTotal; /* Total time */
92 int bWithoutRowid; /* True for --without-rowid */
93 int bReprepare; /* True to reprepare the SQL on each rerun */
94 int bSqlOnly; /* True to print the SQL once only */
95 int bExplain; /* Print SQL with EXPLAIN prefix */
96 int bVerify; /* Try to verify that results are correct */
97 int bMemShrink; /* Call sqlite3_db_release_memory() often */
98 int eTemp; /* 0: no TEMP. 9: always TEMP. */
99 int szTest; /* Scale factor for test iterations */
100 int nRepeat; /* Repeat selects this many times */
101 int doCheckpoint; /* Run PRAGMA wal_checkpoint after each trans */
102 int nReserve; /* Reserve bytes */
103 int doBigTransactions; /* Enable transactions on tests 410 and 510 */
104 const char *zWR; /* Might be WITHOUT ROWID */
105 const char *zNN; /* Might be NOT NULL */
106 const char *zPK; /* Might be UNIQUE or PRIMARY KEY */
107 unsigned int x, y; /* Pseudo-random number generator state */
108 u64 nResByte; /* Total number of result bytes */
109 int nResult; /* Size of the current result */
110 char zResult[3000]; /* Text of the current result */
111 FILE *pScript; /* Write an SQL script into this file */
112 #ifndef SPEEDTEST_OMIT_HASH
113 FILE *hashFile; /* Store all hash results in this file */
114 HashContext hash; /* Hash of all output */
115 #endif
116 } g;
117
118 /* Return " TEMP" or "", as appropriate for creating a table.
119 */
isTemp(int N)120 static const char *isTemp(int N){
121 return g.eTemp>=N ? " TEMP" : "";
122 }
123
124 /* Print an error message and exit */
fatal_error(const char * zMsg,...)125 static void fatal_error(const char *zMsg, ...){
126 va_list ap;
127 va_start(ap, zMsg);
128 vfprintf(stderr, zMsg, ap);
129 va_end(ap);
130 exit(1);
131 }
132
133 #ifndef SPEEDTEST_OMIT_HASH
134 /****************************************************************************
135 ** Hash algorithm used to verify that compilation is not miscompiled
136 ** in such a was as to generate an incorrect result.
137 */
138
139 /*
140 ** Initialize a new hash. iSize determines the size of the hash
141 ** in bits and should be one of 224, 256, 384, or 512. Or iSize
142 ** can be zero to use the default hash size of 256 bits.
143 */
HashInit(void)144 static void HashInit(void){
145 unsigned int k;
146 g.hash.i = 0;
147 g.hash.j = 0;
148 for(k=0; k<256; k++) g.hash.s[k] = k;
149 }
150
151 /*
152 ** Make consecutive calls to the HashUpdate function to add new content
153 ** to the hash
154 */
HashUpdate(const unsigned char * aData,unsigned int nData)155 static void HashUpdate(
156 const unsigned char *aData,
157 unsigned int nData
158 ){
159 unsigned char t;
160 unsigned char i = g.hash.i;
161 unsigned char j = g.hash.j;
162 unsigned int k;
163 if( g.hashFile ) fwrite(aData, 1, nData, g.hashFile);
164 for(k=0; k<nData; k++){
165 j += g.hash.s[i] + aData[k];
166 t = g.hash.s[j];
167 g.hash.s[j] = g.hash.s[i];
168 g.hash.s[i] = t;
169 i++;
170 }
171 g.hash.i = i;
172 g.hash.j = j;
173 }
174
175 /*
176 ** After all content has been added, invoke HashFinal() to compute
177 ** the final hash. The hash result is stored in g.hash.r[].
178 */
HashFinal(void)179 static void HashFinal(void){
180 unsigned int k;
181 unsigned char t, i, j;
182 i = g.hash.i;
183 j = g.hash.j;
184 for(k=0; k<32; k++){
185 i++;
186 t = g.hash.s[i];
187 j += t;
188 g.hash.s[i] = g.hash.s[j];
189 g.hash.s[j] = t;
190 t += g.hash.s[i];
191 g.hash.r[k] = g.hash.s[t];
192 }
193 }
194
195 /* End of the Hash hashing logic
196 *****************************************************************************/
197 #endif /* SPEEDTEST_OMIT_HASH */
198
199 /*
200 ** Return the value of a hexadecimal digit. Return -1 if the input
201 ** is not a hex digit.
202 */
hexDigitValue(char c)203 static int hexDigitValue(char c){
204 if( c>='0' && c<='9' ) return c - '0';
205 if( c>='a' && c<='f' ) return c - 'a' + 10;
206 if( c>='A' && c<='F' ) return c - 'A' + 10;
207 return -1;
208 }
209
210 /* Provide an alternative to sqlite3_stricmp() in older versions of
211 ** SQLite */
212 #if SQLITE_VERSION_NUMBER<3007011
213 # define sqlite3_stricmp strcmp
214 #endif
215
216 /*
217 ** Interpret zArg as an integer value, possibly with suffixes.
218 */
integerValue(const char * zArg)219 static int integerValue(const char *zArg){
220 sqlite3_int64 v = 0;
221 static const struct { char *zSuffix; int iMult; } aMult[] = {
222 { "KiB", 1024 },
223 { "MiB", 1024*1024 },
224 { "GiB", 1024*1024*1024 },
225 { "KB", 1000 },
226 { "MB", 1000000 },
227 { "GB", 1000000000 },
228 { "K", 1000 },
229 { "M", 1000000 },
230 { "G", 1000000000 },
231 };
232 int i;
233 int isNeg = 0;
234 if( zArg[0]=='-' ){
235 isNeg = 1;
236 zArg++;
237 }else if( zArg[0]=='+' ){
238 zArg++;
239 }
240 if( zArg[0]=='0' && zArg[1]=='x' ){
241 int x;
242 zArg += 2;
243 while( (x = hexDigitValue(zArg[0]))>=0 ){
244 v = (v<<4) + x;
245 zArg++;
246 }
247 }else{
248 while( isdigit(zArg[0]) ){
249 v = v*10 + zArg[0] - '0';
250 zArg++;
251 }
252 }
253 for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){
254 if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){
255 v *= aMult[i].iMult;
256 break;
257 }
258 }
259 if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");
260 return (int)(isNeg? -v : v);
261 }
262
263 /* Return the current wall-clock time, in milliseconds */
speedtest1_timestamp(void)264 sqlite3_int64 speedtest1_timestamp(void){
265 #if SQLITE_VERSION_NUMBER<3005000
266 return 0;
267 #else
268 static sqlite3_vfs *clockVfs = 0;
269 sqlite3_int64 t;
270 if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
271 #if SQLITE_VERSION_NUMBER>=3007000
272 if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){
273 clockVfs->xCurrentTimeInt64(clockVfs, &t);
274 }else
275 #endif
276 {
277 double r;
278 clockVfs->xCurrentTime(clockVfs, &r);
279 t = (sqlite3_int64)(r*86400000.0);
280 }
281 return t;
282 #endif
283 }
284
285 /* Return a pseudo-random unsigned integer */
speedtest1_random(void)286 unsigned int speedtest1_random(void){
287 g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001);
288 g.y = g.y*1103515245 + 12345;
289 return g.x ^ g.y;
290 }
291
292 /* Map the value in within the range of 1...limit into another
293 ** number in a way that is chatic and invertable.
294 */
swizzle(unsigned in,unsigned limit)295 unsigned swizzle(unsigned in, unsigned limit){
296 unsigned out = 0;
297 while( limit ){
298 out = (out<<1) | (in&1);
299 in >>= 1;
300 limit >>= 1;
301 }
302 return out;
303 }
304
305 /* Round up a number so that it is a power of two minus one
306 */
roundup_allones(unsigned limit)307 unsigned roundup_allones(unsigned limit){
308 unsigned m = 1;
309 while( m<limit ) m = (m<<1)+1;
310 return m;
311 }
312
313 /* The speedtest1_numbername procedure below converts its argment (an integer)
314 ** into a string which is the English-language name for that number.
315 ** The returned string should be freed with sqlite3_free().
316 **
317 ** Example:
318 **
319 ** speedtest1_numbername(123) -> "one hundred twenty three"
320 */
speedtest1_numbername(unsigned int n,char * zOut,int nOut)321 int speedtest1_numbername(unsigned int n, char *zOut, int nOut){
322 static const char *ones[] = { "zero", "one", "two", "three", "four", "five",
323 "six", "seven", "eight", "nine", "ten", "eleven", "twelve",
324 "thirteen", "fourteen", "fifteen", "sixteen", "seventeen",
325 "eighteen", "nineteen" };
326 static const char *tens[] = { "", "ten", "twenty", "thirty", "forty",
327 "fifty", "sixty", "seventy", "eighty", "ninety" };
328 int i = 0;
329
330 if( n>=1000000000 ){
331 i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i);
332 sqlite3_snprintf(nOut-i, zOut+i, " billion");
333 i += (int)strlen(zOut+i);
334 n = n % 1000000000;
335 }
336 if( n>=1000000 ){
337 if( i && i<nOut-1 ) zOut[i++] = ' ';
338 i += speedtest1_numbername(n/1000000, zOut+i, nOut-i);
339 sqlite3_snprintf(nOut-i, zOut+i, " million");
340 i += (int)strlen(zOut+i);
341 n = n % 1000000;
342 }
343 if( n>=1000 ){
344 if( i && i<nOut-1 ) zOut[i++] = ' ';
345 i += speedtest1_numbername(n/1000, zOut+i, nOut-i);
346 sqlite3_snprintf(nOut-i, zOut+i, " thousand");
347 i += (int)strlen(zOut+i);
348 n = n % 1000;
349 }
350 if( n>=100 ){
351 if( i && i<nOut-1 ) zOut[i++] = ' ';
352 sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]);
353 i += (int)strlen(zOut+i);
354 n = n % 100;
355 }
356 if( n>=20 ){
357 if( i && i<nOut-1 ) zOut[i++] = ' ';
358 sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]);
359 i += (int)strlen(zOut+i);
360 n = n % 10;
361 }
362 if( n>0 ){
363 if( i && i<nOut-1 ) zOut[i++] = ' ';
364 sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]);
365 i += (int)strlen(zOut+i);
366 }
367 if( i==0 ){
368 sqlite3_snprintf(nOut-i, zOut+i, "zero");
369 i += (int)strlen(zOut+i);
370 }
371 return i;
372 }
373
374
375 /* Start a new test case */
376 #define NAMEWIDTH 60
377 static const char zDots[] =
378 ".......................................................................";
379 static int iTestNumber = 0; /* Current test # for begin/end_test(). */
speedtest1_begin_test(int iTestNum,const char * zTestName,...)380 void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){
381 int n = (int)strlen(zTestName);
382 char *zName;
383 va_list ap;
384 iTestNumber = iTestNum;
385 va_start(ap, zTestName);
386 zName = sqlite3_vmprintf(zTestName, ap);
387 va_end(ap);
388 n = (int)strlen(zName);
389 if( n>NAMEWIDTH ){
390 zName[NAMEWIDTH] = 0;
391 n = NAMEWIDTH;
392 }
393 if( g.pScript ){
394 fprintf(g.pScript,"-- begin test %d %.*s\n", iTestNumber, n, zName)
395 /* maintenance reminder: ^^^ code in ext/wasm expects %d to be
396 ** field #4 (as in: cut -d' ' -f4). */;
397 }
398 if( g.bSqlOnly ){
399 printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots);
400 }else{
401 printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);
402 fflush(stdout);
403 }
404 sqlite3_free(zName);
405 g.nResult = 0;
406 g.iStart = speedtest1_timestamp();
407 g.x = 0xad131d0b;
408 g.y = 0x44f9eac8;
409 }
410
411 /* Forward reference */
412 void speedtest1_exec(const char*,...);
413
414 /* Complete a test case */
speedtest1_end_test(void)415 void speedtest1_end_test(void){
416 sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;
417 if( g.doCheckpoint ) speedtest1_exec("PRAGMA wal_checkpoint;");
418 assert( iTestNumber > 0 );
419 if( g.pScript ){
420 fprintf(g.pScript,"-- end test %d\n", iTestNumber);
421 }
422 if( !g.bSqlOnly ){
423 g.iTotal += iElapseTime;
424 printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000));
425 }
426 if( g.pStmt ){
427 sqlite3_finalize(g.pStmt);
428 g.pStmt = 0;
429 }
430 iTestNumber = 0;
431 }
432
433 /* Report end of testing */
speedtest1_final(void)434 void speedtest1_final(void){
435 if( !g.bSqlOnly ){
436 printf(" TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
437 (int)(g.iTotal/1000), (int)(g.iTotal%1000));
438 }
439 if( g.bVerify ){
440 #ifndef SPEEDTEST_OMIT_HASH
441 int i;
442 #endif
443 printf("Verification Hash: %llu ", g.nResByte);
444 #ifndef SPEEDTEST_OMIT_HASH
445 HashUpdate((const unsigned char*)"\n", 1);
446 HashFinal();
447 for(i=0; i<24; i++){
448 printf("%02x", g.hash.r[i]);
449 }
450 if( g.hashFile && g.hashFile!=stdout ) fclose(g.hashFile);
451 #endif
452 printf("\n");
453 }
454 }
455
456 /* Print an SQL statement to standard output */
printSql(const char * zSql)457 static void printSql(const char *zSql){
458 int n = (int)strlen(zSql);
459 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ){ n--; }
460 if( g.bExplain ) printf("EXPLAIN ");
461 printf("%.*s;\n", n, zSql);
462 if( g.bExplain
463 #if SQLITE_VERSION_NUMBER>=3007017
464 && ( sqlite3_strglob("CREATE *", zSql)==0
465 || sqlite3_strglob("DROP *", zSql)==0
466 || sqlite3_strglob("ALTER *", zSql)==0
467 )
468 #endif
469 ){
470 printf("%.*s;\n", n, zSql);
471 }
472 }
473
474 /* Shrink memory used, if appropriate and if the SQLite version is capable
475 ** of doing so.
476 */
speedtest1_shrink_memory(void)477 void speedtest1_shrink_memory(void){
478 #if SQLITE_VERSION_NUMBER>=3007010
479 if( g.bMemShrink ) sqlite3_db_release_memory(g.db);
480 #endif
481 }
482
483 /* Run SQL */
speedtest1_exec(const char * zFormat,...)484 void speedtest1_exec(const char *zFormat, ...){
485 va_list ap;
486 char *zSql;
487 va_start(ap, zFormat);
488 zSql = sqlite3_vmprintf(zFormat, ap);
489 va_end(ap);
490 if( g.bSqlOnly ){
491 printSql(zSql);
492 }else{
493 char *zErrMsg = 0;
494 int rc;
495 if( g.pScript ){
496 fprintf(g.pScript,"%s;\n",zSql);
497 }
498 rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
499 if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
500 if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
501 }
502 sqlite3_free(zSql);
503 speedtest1_shrink_memory();
504 }
505
506 /* Run SQL and return the first column of the first row as a string. The
507 ** returned string is obtained from sqlite_malloc() and must be freed by
508 ** the caller.
509 */
speedtest1_once(const char * zFormat,...)510 char *speedtest1_once(const char *zFormat, ...){
511 va_list ap;
512 char *zSql;
513 sqlite3_stmt *pStmt;
514 char *zResult = 0;
515 va_start(ap, zFormat);
516 zSql = sqlite3_vmprintf(zFormat, ap);
517 va_end(ap);
518 if( g.bSqlOnly ){
519 printSql(zSql);
520 }else{
521 int rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, 0);
522 if( rc ){
523 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
524 }
525 if( g.pScript ){
526 char *z = sqlite3_expanded_sql(pStmt);
527 fprintf(g.pScript,"%s\n",z);
528 sqlite3_free(z);
529 }
530 if( sqlite3_step(pStmt)==SQLITE_ROW ){
531 const char *z = (const char*)sqlite3_column_text(pStmt, 0);
532 if( z ) zResult = sqlite3_mprintf("%s", z);
533 }
534 sqlite3_finalize(pStmt);
535 }
536 sqlite3_free(zSql);
537 speedtest1_shrink_memory();
538 return zResult;
539 }
540
541 /* Prepare an SQL statement */
speedtest1_prepare(const char * zFormat,...)542 void speedtest1_prepare(const char *zFormat, ...){
543 va_list ap;
544 char *zSql;
545 va_start(ap, zFormat);
546 zSql = sqlite3_vmprintf(zFormat, ap);
547 va_end(ap);
548 if( g.bSqlOnly ){
549 printSql(zSql);
550 }else{
551 int rc;
552 if( g.pStmt ) sqlite3_finalize(g.pStmt);
553 rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
554 if( rc ){
555 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
556 }
557 }
558 sqlite3_free(zSql);
559 }
560
561 /* Run an SQL statement previously prepared */
speedtest1_run(void)562 void speedtest1_run(void){
563 int i, n, len;
564 if( g.bSqlOnly ) return;
565 assert( g.pStmt );
566 g.nResult = 0;
567 if( g.pScript ){
568 char *z = sqlite3_expanded_sql(g.pStmt);
569 fprintf(g.pScript,"%s\n",z);
570 sqlite3_free(z);
571 }
572 while( sqlite3_step(g.pStmt)==SQLITE_ROW ){
573 n = sqlite3_column_count(g.pStmt);
574 for(i=0; i<n; i++){
575 const char *z = (const char*)sqlite3_column_text(g.pStmt, i);
576 if( z==0 ) z = "nil";
577 len = (int)strlen(z);
578 #ifndef SPEEDTEST_OMIT_HASH
579 if( g.bVerify ){
580 int eType = sqlite3_column_type(g.pStmt, i);
581 unsigned char zPrefix[2];
582 zPrefix[0] = '\n';
583 zPrefix[1] = "-IFTBN"[eType];
584 if( g.nResByte ){
585 HashUpdate(zPrefix, 2);
586 }else{
587 HashUpdate(zPrefix+1, 1);
588 }
589 if( eType==SQLITE_FLOAT ){
590 /* Omit the value of floating-point results from the verification
591 ** hash. The only thing we record is the fact that the result was
592 ** a floating-point value. */
593 g.nResByte += 2;
594 }else if( eType==SQLITE_BLOB ){
595 int nBlob = sqlite3_column_bytes(g.pStmt, i);
596 int iBlob;
597 unsigned char zChar[2];
598 const unsigned char *aBlob = sqlite3_column_blob(g.pStmt, i);
599 for(iBlob=0; iBlob<nBlob; iBlob++){
600 zChar[0] = "0123456789abcdef"[aBlob[iBlob]>>4];
601 zChar[1] = "0123456789abcdef"[aBlob[iBlob]&15];
602 HashUpdate(zChar,2);
603 }
604 g.nResByte += nBlob*2 + 2;
605 }else{
606 HashUpdate((unsigned char*)z, len);
607 g.nResByte += len + 2;
608 }
609 }
610 #endif
611 if( g.nResult+len<sizeof(g.zResult)-2 ){
612 if( g.nResult>0 ) g.zResult[g.nResult++] = ' ';
613 memcpy(g.zResult + g.nResult, z, len+1);
614 g.nResult += len;
615 }
616 }
617 }
618 #if SQLITE_VERSION_NUMBER>=3006001
619 if( g.bReprepare ){
620 sqlite3_stmt *pNew;
621 sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0);
622 sqlite3_finalize(g.pStmt);
623 g.pStmt = pNew;
624 }else
625 #endif
626 {
627 sqlite3_reset(g.pStmt);
628 }
629 speedtest1_shrink_memory();
630 }
631
632 #ifndef SQLITE_OMIT_DEPRECATED
633 /* The sqlite3_trace() callback function */
traceCallback(void * NotUsed,const char * zSql)634 static void traceCallback(void *NotUsed, const char *zSql){
635 int n = (int)strlen(zSql);
636 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ) n--;
637 fprintf(stderr,"%.*s;\n", n, zSql);
638 }
639 #endif /* SQLITE_OMIT_DEPRECATED */
640
641 /* Substitute random() function that gives the same random
642 ** sequence on each run, for repeatability. */
randomFunc(sqlite3_context * context,int NotUsed,sqlite3_value ** NotUsed2)643 static void randomFunc(
644 sqlite3_context *context,
645 int NotUsed,
646 sqlite3_value **NotUsed2
647 ){
648 sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random());
649 }
650
651 /* Estimate the square root of an integer */
est_square_root(int x)652 static int est_square_root(int x){
653 int y0 = x/2;
654 int y1;
655 int n;
656 for(n=0; y0>0 && n<10; n++){
657 y1 = (y0 + x/y0)/2;
658 if( y1==y0 ) break;
659 y0 = y1;
660 }
661 return y0;
662 }
663
664
665 #if SQLITE_VERSION_NUMBER<3005004
666 /*
667 ** An implementation of group_concat(). Used only when testing older
668 ** versions of SQLite that lack the built-in group_concat().
669 */
670 struct groupConcat {
671 char *z;
672 int nAlloc;
673 int nUsed;
674 };
groupAppend(struct groupConcat * p,const char * z,int n)675 static void groupAppend(struct groupConcat *p, const char *z, int n){
676 if( p->nUsed+n >= p->nAlloc ){
677 int n2 = (p->nAlloc+n+1)*2;
678 char *z2 = sqlite3_realloc(p->z, n2);
679 if( z2==0 ) return;
680 p->z = z2;
681 p->nAlloc = n2;
682 }
683 memcpy(p->z+p->nUsed, z, n);
684 p->nUsed += n;
685 }
groupStep(sqlite3_context * context,int argc,sqlite3_value ** argv)686 static void groupStep(
687 sqlite3_context *context,
688 int argc,
689 sqlite3_value **argv
690 ){
691 const char *zVal;
692 struct groupConcat *p;
693 const char *zSep;
694 int nVal, nSep;
695 assert( argc==1 || argc==2 );
696 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
697 p= (struct groupConcat*)sqlite3_aggregate_context(context, sizeof(*p));
698
699 if( p ){
700 int firstTerm = p->nUsed==0;
701 if( !firstTerm ){
702 if( argc==2 ){
703 zSep = (char*)sqlite3_value_text(argv[1]);
704 nSep = sqlite3_value_bytes(argv[1]);
705 }else{
706 zSep = ",";
707 nSep = 1;
708 }
709 if( nSep ) groupAppend(p, zSep, nSep);
710 }
711 zVal = (char*)sqlite3_value_text(argv[0]);
712 nVal = sqlite3_value_bytes(argv[0]);
713 if( zVal ) groupAppend(p, zVal, nVal);
714 }
715 }
groupFinal(sqlite3_context * context)716 static void groupFinal(sqlite3_context *context){
717 struct groupConcat *p;
718 p = sqlite3_aggregate_context(context, 0);
719 if( p && p->z ){
720 p->z[p->nUsed] = 0;
721 sqlite3_result_text(context, p->z, p->nUsed, sqlite3_free);
722 }
723 }
724 #endif
725
726 /*
727 ** The main and default testset
728 */
testset_main(void)729 void testset_main(void){
730 int i; /* Loop counter */
731 int n; /* iteration count */
732 int sz; /* Size of the tables */
733 int maxb; /* Maximum swizzled value */
734 unsigned x1 = 0, x2 = 0; /* Parameters */
735 int len = 0; /* Length of the zNum[] string */
736 char zNum[2000]; /* A number name */
737
738 sz = n = g.szTest*500;
739 zNum[0] = 0;
740 maxb = roundup_allones(sz);
741 speedtest1_begin_test(100, "%d INSERTs into table with no index", n);
742 speedtest1_exec("BEGIN");
743 speedtest1_exec("CREATE%s TABLE z1(a INTEGER %s, b INTEGER %s, c TEXT %s);",
744 isTemp(9), g.zNN, g.zNN, g.zNN);
745 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2,?3); -- %d times", n);
746 for(i=1; i<=n; i++){
747 x1 = swizzle(i,maxb);
748 speedtest1_numbername(x1, zNum, sizeof(zNum));
749 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
750 sqlite3_bind_int(g.pStmt, 2, i);
751 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
752 speedtest1_run();
753 }
754 speedtest1_exec("COMMIT");
755 speedtest1_end_test();
756
757
758 n = sz;
759 speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n);
760 speedtest1_exec("BEGIN");
761 speedtest1_exec(
762 "CREATE%s TABLE z2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
763 isTemp(5), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
764 speedtest1_prepare("INSERT INTO z2 VALUES(?1,?2,?3); -- %d times", n);
765 for(i=1; i<=n; i++){
766 x1 = swizzle(i,maxb);
767 speedtest1_numbername(x1, zNum, sizeof(zNum));
768 sqlite3_bind_int(g.pStmt, 1, i);
769 sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1);
770 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
771 speedtest1_run();
772 }
773 speedtest1_exec("COMMIT");
774 speedtest1_end_test();
775
776
777 n = sz;
778 speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n);
779 speedtest1_exec("BEGIN");
780 speedtest1_exec(
781 "CREATE%s TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
782 isTemp(3), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
783 speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n);
784 for(i=1; i<=n; i++){
785 x1 = swizzle(i,maxb);
786 speedtest1_numbername(x1, zNum, sizeof(zNum));
787 sqlite3_bind_int(g.pStmt, 2, i);
788 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
789 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
790 speedtest1_run();
791 }
792 speedtest1_exec("COMMIT");
793 speedtest1_end_test();
794
795 #if SQLITE_VERSION_NUMBER<3005004
796 sqlite3_create_function(g.db, "group_concat", 1, SQLITE_UTF8, 0,
797 0, groupStep, groupFinal);
798 #endif
799
800 n = 25;
801 speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
802 speedtest1_exec("BEGIN");
803 speedtest1_prepare(
804 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"
805 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
806 );
807 for(i=1; i<=n; i++){
808 if( (i-1)%g.nRepeat==0 ){
809 x1 = speedtest1_random()%maxb;
810 x2 = speedtest1_random()%10 + sz/5000 + x1;
811 }
812 sqlite3_bind_int(g.pStmt, 1, x1);
813 sqlite3_bind_int(g.pStmt, 2, x2);
814 speedtest1_run();
815 }
816 speedtest1_exec("COMMIT");
817 speedtest1_end_test();
818
819
820 n = 10;
821 speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
822 speedtest1_exec("BEGIN");
823 speedtest1_prepare(
824 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"
825 " WHERE c LIKE ?1; -- %d times", n
826 );
827 for(i=1; i<=n; i++){
828 if( (i-1)%g.nRepeat==0 ){
829 x1 = speedtest1_random()%maxb;
830 zNum[0] = '%';
831 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
832 zNum[len] = '%';
833 zNum[len+1] = 0;
834 }
835 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
836 speedtest1_run();
837 }
838 speedtest1_exec("COMMIT");
839 speedtest1_end_test();
840
841
842 n = 10;
843 speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);
844 speedtest1_exec("BEGIN");
845 speedtest1_prepare(
846 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"
847 " ORDER BY a; -- %d times", n
848 );
849 for(i=1; i<=n; i++){
850 if( (i-1)%g.nRepeat==0 ){
851 x1 = speedtest1_random()%maxb;
852 zNum[0] = '%';
853 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
854 zNum[len] = '%';
855 zNum[len+1] = 0;
856 }
857 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
858 speedtest1_run();
859 }
860 speedtest1_exec("COMMIT");
861 speedtest1_end_test();
862
863 n = 10; /* g.szTest/5; */
864 speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);
865 speedtest1_exec("BEGIN");
866 speedtest1_prepare(
867 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"
868 " ORDER BY a LIMIT 10; -- %d times", n
869 );
870 for(i=1; i<=n; i++){
871 if( (i-1)%g.nRepeat==0 ){
872 x1 = speedtest1_random()%maxb;
873 zNum[0] = '%';
874 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
875 zNum[len] = '%';
876 zNum[len+1] = 0;
877 }
878 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
879 speedtest1_run();
880 }
881 speedtest1_exec("COMMIT");
882 speedtest1_end_test();
883
884
885 speedtest1_begin_test(150, "CREATE INDEX five times");
886 speedtest1_exec("BEGIN;");
887 speedtest1_exec("CREATE UNIQUE INDEX t1b ON z1(b);");
888 speedtest1_exec("CREATE INDEX t1c ON z1(c);");
889 speedtest1_exec("CREATE UNIQUE INDEX t2b ON z2(b);");
890 speedtest1_exec("CREATE INDEX t2c ON z2(c DESC);");
891 speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
892 speedtest1_exec("COMMIT;");
893 speedtest1_end_test();
894
895
896 n = sz/5;
897 speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
898 speedtest1_exec("BEGIN");
899 speedtest1_prepare(
900 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"
901 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
902 );
903 for(i=1; i<=n; i++){
904 if( (i-1)%g.nRepeat==0 ){
905 x1 = speedtest1_random()%maxb;
906 x2 = speedtest1_random()%10 + sz/5000 + x1;
907 }
908 sqlite3_bind_int(g.pStmt, 1, x1);
909 sqlite3_bind_int(g.pStmt, 2, x2);
910 speedtest1_run();
911 }
912 speedtest1_exec("COMMIT");
913 speedtest1_end_test();
914
915
916 n = sz/5;
917 speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
918 speedtest1_exec("BEGIN");
919 speedtest1_prepare(
920 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z2\n"
921 " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
922 );
923 for(i=1; i<=n; i++){
924 if( (i-1)%g.nRepeat==0 ){
925 x1 = speedtest1_random()%maxb;
926 x2 = speedtest1_random()%10 + sz/5000 + x1;
927 }
928 sqlite3_bind_int(g.pStmt, 1, x1);
929 sqlite3_bind_int(g.pStmt, 2, x2);
930 speedtest1_run();
931 }
932 speedtest1_exec("COMMIT");
933 speedtest1_end_test();
934
935
936 n = sz/5;
937 speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
938 speedtest1_exec("BEGIN");
939 speedtest1_prepare(
940 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"
941 " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
942 );
943 for(i=1; i<=n; i++){
944 if( (i-1)%g.nRepeat==0 ){
945 x1 = swizzle(i, maxb);
946 len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
947 }
948 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
949 speedtest1_run();
950 }
951 speedtest1_exec("COMMIT");
952 speedtest1_end_test();
953
954 n = sz;
955 speedtest1_begin_test(180, "%d INSERTS with three indexes", n);
956 speedtest1_exec("BEGIN");
957 speedtest1_exec(
958 "CREATE%s TABLE t4(\n"
959 " a INTEGER %s %s,\n"
960 " b INTEGER %s,\n"
961 " c TEXT %s\n"
962 ") %s",
963 isTemp(1), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
964 speedtest1_exec("CREATE INDEX t4b ON t4(b)");
965 speedtest1_exec("CREATE INDEX t4c ON t4(c)");
966 speedtest1_exec("INSERT INTO t4 SELECT * FROM z1");
967 speedtest1_exec("COMMIT");
968 speedtest1_end_test();
969
970 n = sz;
971 speedtest1_begin_test(190, "DELETE and REFILL one table", n);
972 speedtest1_exec("DELETE FROM z2;");
973 speedtest1_exec("INSERT INTO z2 SELECT * FROM z1;");
974 speedtest1_end_test();
975
976
977 speedtest1_begin_test(200, "VACUUM");
978 speedtest1_exec("VACUUM");
979 speedtest1_end_test();
980
981
982 speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query");
983 speedtest1_exec("ALTER TABLE z2 ADD COLUMN d INT DEFAULT 123");
984 speedtest1_exec("SELECT sum(d) FROM z2");
985 speedtest1_end_test();
986
987
988 n = sz/5;
989 speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n);
990 speedtest1_exec("BEGIN");
991 speedtest1_prepare(
992 "UPDATE z2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
993 );
994 for(i=1; i<=n; i++){
995 x1 = speedtest1_random()%maxb;
996 x2 = speedtest1_random()%10 + sz/5000 + x1;
997 sqlite3_bind_int(g.pStmt, 1, x1);
998 sqlite3_bind_int(g.pStmt, 2, x2);
999 speedtest1_run();
1000 }
1001 speedtest1_exec("COMMIT");
1002 speedtest1_end_test();
1003
1004
1005 n = sz;
1006 speedtest1_begin_test(240, "%d UPDATES of individual rows", n);
1007 speedtest1_exec("BEGIN");
1008 speedtest1_prepare(
1009 "UPDATE z2 SET d=b*3 WHERE a=?1; -- %d times", n
1010 );
1011 for(i=1; i<=n; i++){
1012 x1 = speedtest1_random()%sz + 1;
1013 sqlite3_bind_int(g.pStmt, 1, x1);
1014 speedtest1_run();
1015 }
1016 speedtest1_exec("COMMIT");
1017 speedtest1_end_test();
1018
1019 speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz);
1020 speedtest1_exec("UPDATE z2 SET d=b*4");
1021 speedtest1_end_test();
1022
1023
1024 speedtest1_begin_test(260, "Query added column after filling");
1025 speedtest1_exec("SELECT sum(d) FROM z2");
1026 speedtest1_end_test();
1027
1028
1029
1030 n = sz/5;
1031 speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n);
1032 speedtest1_exec("BEGIN");
1033 speedtest1_prepare(
1034 "DELETE FROM z2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
1035 );
1036 for(i=1; i<=n; i++){
1037 x1 = speedtest1_random()%maxb + 1;
1038 x2 = speedtest1_random()%10 + sz/5000 + x1;
1039 sqlite3_bind_int(g.pStmt, 1, x1);
1040 sqlite3_bind_int(g.pStmt, 2, x2);
1041 speedtest1_run();
1042 }
1043 speedtest1_exec("COMMIT");
1044 speedtest1_end_test();
1045
1046
1047 n = sz;
1048 speedtest1_begin_test(280, "%d DELETEs of individual rows", n);
1049 speedtest1_exec("BEGIN");
1050 speedtest1_prepare(
1051 "DELETE FROM t3 WHERE a=?1; -- %d times", n
1052 );
1053 for(i=1; i<=n; i++){
1054 x1 = speedtest1_random()%sz + 1;
1055 sqlite3_bind_int(g.pStmt, 1, x1);
1056 speedtest1_run();
1057 }
1058 speedtest1_exec("COMMIT");
1059 speedtest1_end_test();
1060
1061
1062 speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
1063 speedtest1_exec("REPLACE INTO z2(a,b,c) SELECT a,b,c FROM z1");
1064 speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM z1");
1065 speedtest1_end_test();
1066
1067 speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
1068 speedtest1_exec("DELETE FROM z2;");
1069 speedtest1_exec("INSERT INTO z2(a,b,c)\n"
1070 " SELECT a,b,c FROM z1 WHERE (b&1)==(a&1);");
1071 speedtest1_exec("INSERT INTO z2(a,b,c)\n"
1072 " SELECT a,b,c FROM z1 WHERE (b&1)<>(a&1);");
1073 speedtest1_end_test();
1074
1075
1076 n = sz/5;
1077 speedtest1_begin_test(310, "%d four-ways joins", n);
1078 speedtest1_exec("BEGIN");
1079 speedtest1_prepare(
1080 "SELECT z1.c FROM z1, z2, t3, t4\n"
1081 " WHERE t4.a BETWEEN ?1 AND ?2\n"
1082 " AND t3.a=t4.b\n"
1083 " AND z2.a=t3.b\n"
1084 " AND z1.c=z2.c;"
1085 );
1086 for(i=1; i<=n; i++){
1087 x1 = speedtest1_random()%sz + 1;
1088 x2 = speedtest1_random()%10 + x1 + 4;
1089 sqlite3_bind_int(g.pStmt, 1, x1);
1090 sqlite3_bind_int(g.pStmt, 2, x2);
1091 speedtest1_run();
1092 }
1093 speedtest1_exec("COMMIT");
1094 speedtest1_end_test();
1095
1096 speedtest1_begin_test(320, "subquery in result set", n);
1097 speedtest1_prepare(
1098 "SELECT sum(a), max(c),\n"
1099 " avg((SELECT a FROM z2 WHERE 5+z2.b=z1.b) AND rowid<?1), max(c)\n"
1100 " FROM z1 WHERE rowid<?1;"
1101 );
1102 sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
1103 speedtest1_run();
1104 speedtest1_end_test();
1105
1106 sz = n = g.szTest*700;
1107 zNum[0] = 0;
1108 maxb = roundup_allones(sz/3);
1109 speedtest1_begin_test(400, "%d REPLACE ops on an IPK", n);
1110 speedtest1_exec("BEGIN");
1111 speedtest1_exec("CREATE%s TABLE t5(a INTEGER PRIMARY KEY, b %s);",
1112 isTemp(9), g.zNN);
1113 speedtest1_prepare("REPLACE INTO t5 VALUES(?1,?2); -- %d times",n);
1114 for(i=1; i<=n; i++){
1115 x1 = swizzle(i,maxb);
1116 speedtest1_numbername(i, zNum, sizeof(zNum));
1117 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
1118 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1119 speedtest1_run();
1120 }
1121 speedtest1_exec("COMMIT");
1122 speedtest1_end_test();
1123 speedtest1_begin_test(410, "%d SELECTS on an IPK", n);
1124 if( g.doBigTransactions ){
1125 /* Historical note: tests 410 and 510 have historically not used
1126 ** explicit transactions. The --big-transactions flag was added
1127 ** 2022-09-08 to support the WASM/OPFS build, as the run-times
1128 ** approach 1 minute for each of these tests if they're not in an
1129 ** explicit transaction. The run-time effect of --big-transaciions
1130 ** on native builds is negligible. */
1131 speedtest1_exec("BEGIN");
1132 }
1133 speedtest1_prepare("SELECT b FROM t5 WHERE a=?1; -- %d times",n);
1134 for(i=1; i<=n; i++){
1135 x1 = swizzle(i,maxb);
1136 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
1137 speedtest1_run();
1138 }
1139 if( g.doBigTransactions ){
1140 speedtest1_exec("COMMIT");
1141 }
1142 speedtest1_end_test();
1143
1144 sz = n = g.szTest*700;
1145 zNum[0] = 0;
1146 maxb = roundup_allones(sz/3);
1147 speedtest1_begin_test(500, "%d REPLACE on TEXT PK", n);
1148 speedtest1_exec("BEGIN");
1149 speedtest1_exec("CREATE%s TABLE t6(a TEXT PRIMARY KEY, b %s)%s;",
1150 isTemp(9), g.zNN,
1151 sqlite3_libversion_number()>=3008002 ? "WITHOUT ROWID" : "");
1152 speedtest1_prepare("REPLACE INTO t6 VALUES(?1,?2); -- %d times",n);
1153 for(i=1; i<=n; i++){
1154 x1 = swizzle(i,maxb);
1155 speedtest1_numbername(x1, zNum, sizeof(zNum));
1156 sqlite3_bind_int(g.pStmt, 2, i);
1157 sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
1158 speedtest1_run();
1159 }
1160 speedtest1_exec("COMMIT");
1161 speedtest1_end_test();
1162 speedtest1_begin_test(510, "%d SELECTS on a TEXT PK", n);
1163 if( g.doBigTransactions ){
1164 /* See notes for test 410. */
1165 speedtest1_exec("BEGIN");
1166 }
1167 speedtest1_prepare("SELECT b FROM t6 WHERE a=?1; -- %d times",n);
1168 for(i=1; i<=n; i++){
1169 x1 = swizzle(i,maxb);
1170 speedtest1_numbername(x1, zNum, sizeof(zNum));
1171 sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
1172 speedtest1_run();
1173 }
1174 if( g.doBigTransactions ){
1175 speedtest1_exec("COMMIT");
1176 }
1177 speedtest1_end_test();
1178 speedtest1_begin_test(520, "%d SELECT DISTINCT", n);
1179 speedtest1_exec("SELECT DISTINCT b FROM t5;");
1180 speedtest1_exec("SELECT DISTINCT b FROM t6;");
1181 speedtest1_end_test();
1182
1183
1184 speedtest1_begin_test(980, "PRAGMA integrity_check");
1185 speedtest1_exec("PRAGMA integrity_check");
1186 speedtest1_end_test();
1187
1188
1189 speedtest1_begin_test(990, "ANALYZE");
1190 speedtest1_exec("ANALYZE");
1191 speedtest1_end_test();
1192 }
1193
1194 /*
1195 ** A testset for common table expressions. This exercises code
1196 ** for views, subqueries, co-routines, etc.
1197 */
testset_cte(void)1198 void testset_cte(void){
1199 static const char *azPuzzle[] = {
1200 /* Easy */
1201 "534...9.."
1202 "67.195..."
1203 ".98....6."
1204 "8...6...3"
1205 "4..8.3..1"
1206 "....2...6"
1207 ".6....28."
1208 "...419..5"
1209 "...28..79",
1210
1211 /* Medium */
1212 "53....9.."
1213 "6..195..."
1214 ".98....6."
1215 "8...6...3"
1216 "4..8.3..1"
1217 "....2...6"
1218 ".6....28."
1219 "...419..5"
1220 "....8..79",
1221
1222 /* Hard */
1223 "53......."
1224 "6..195..."
1225 ".98....6."
1226 "8...6...3"
1227 "4..8.3..1"
1228 "....2...6"
1229 ".6....28."
1230 "...419..5"
1231 "....8..79",
1232 };
1233 const char *zPuz;
1234 double rSpacing;
1235 int nElem;
1236
1237 if( g.szTest<25 ){
1238 zPuz = azPuzzle[0];
1239 }else if( g.szTest<70 ){
1240 zPuz = azPuzzle[1];
1241 }else{
1242 zPuz = azPuzzle[2];
1243 }
1244 speedtest1_begin_test(100, "Sudoku with recursive 'digits'");
1245 speedtest1_prepare(
1246 "WITH RECURSIVE\n"
1247 " input(sud) AS (VALUES(?1)),\n"
1248 " digits(z,lp) AS (\n"
1249 " VALUES('1', 1)\n"
1250 " UNION ALL\n"
1251 " SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9\n"
1252 " ),\n"
1253 " x(s, ind) AS (\n"
1254 " SELECT sud, instr(sud, '.') FROM input\n"
1255 " UNION ALL\n"
1256 " SELECT\n"
1257 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1258 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1259 " FROM x, digits AS z\n"
1260 " WHERE ind>0\n"
1261 " AND NOT EXISTS (\n"
1262 " SELECT 1\n"
1263 " FROM digits AS lp\n"
1264 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1265 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1266 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1267 " + ((ind-1)/27) * 27 + lp\n"
1268 " + ((lp-1) / 3) * 6, 1)\n"
1269 " )\n"
1270 " )\n"
1271 "SELECT s FROM x WHERE ind=0;"
1272 );
1273 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1274 speedtest1_run();
1275 speedtest1_end_test();
1276
1277 speedtest1_begin_test(200, "Sudoku with VALUES 'digits'");
1278 speedtest1_prepare(
1279 "WITH RECURSIVE\n"
1280 " input(sud) AS (VALUES(?1)),\n"
1281 " digits(z,lp) AS (VALUES('1',1),('2',2),('3',3),('4',4),('5',5),\n"
1282 " ('6',6),('7',7),('8',8),('9',9)),\n"
1283 " x(s, ind) AS (\n"
1284 " SELECT sud, instr(sud, '.') FROM input\n"
1285 " UNION ALL\n"
1286 " SELECT\n"
1287 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1288 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1289 " FROM x, digits AS z\n"
1290 " WHERE ind>0\n"
1291 " AND NOT EXISTS (\n"
1292 " SELECT 1\n"
1293 " FROM digits AS lp\n"
1294 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1295 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1296 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1297 " + ((ind-1)/27) * 27 + lp\n"
1298 " + ((lp-1) / 3) * 6, 1)\n"
1299 " )\n"
1300 " )\n"
1301 "SELECT s FROM x WHERE ind=0;"
1302 );
1303 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1304 speedtest1_run();
1305 speedtest1_end_test();
1306
1307 rSpacing = 5.0/g.szTest;
1308 speedtest1_begin_test(300, "Mandelbrot Set with spacing=%f", rSpacing);
1309 speedtest1_prepare(
1310 "WITH RECURSIVE \n"
1311 " xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+?1 FROM xaxis WHERE x<1.2),\n"
1312 " yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+?2 FROM yaxis WHERE y<1.0),\n"
1313 " m(iter, cx, cy, x, y) AS (\n"
1314 " SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis\n"
1315 " UNION ALL\n"
1316 " SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \n"
1317 " WHERE (x*x + y*y) < 4.0 AND iter<28\n"
1318 " ),\n"
1319 " m2(iter, cx, cy) AS (\n"
1320 " SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n"
1321 " ),\n"
1322 " a(t) AS (\n"
1323 " SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n"
1324 " FROM m2 GROUP BY cy\n"
1325 " )\n"
1326 "SELECT group_concat(rtrim(t),x'0a') FROM a;"
1327 );
1328 sqlite3_bind_double(g.pStmt, 1, rSpacing*.05);
1329 sqlite3_bind_double(g.pStmt, 2, rSpacing);
1330 speedtest1_run();
1331 speedtest1_end_test();
1332
1333 nElem = 10000*g.szTest;
1334 speedtest1_begin_test(400, "EXCEPT operator on %d-element tables", nElem);
1335 speedtest1_prepare(
1336 "WITH RECURSIVE \n"
1337 " z1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM z1 WHERE x<%d),\n"
1338 " z2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM z2 WHERE y<%d)\n"
1339 "SELECT count(x), avg(x) FROM (\n"
1340 " SELECT x FROM z1 EXCEPT SELECT y FROM z2 ORDER BY 1\n"
1341 ");",
1342 nElem, nElem
1343 );
1344 speedtest1_run();
1345 speedtest1_end_test();
1346 }
1347
1348 /*
1349 ** Compute a pseudo-random floating point ascii number.
1350 */
speedtest1_random_ascii_fp(char * zFP)1351 void speedtest1_random_ascii_fp(char *zFP){
1352 int x = speedtest1_random();
1353 int y = speedtest1_random();
1354 int z;
1355 z = y%10;
1356 if( z<0 ) z = -z;
1357 y /= 10;
1358 sqlite3_snprintf(100,zFP,"%d.%de%d",y,z,x%200);
1359 }
1360
1361 /*
1362 ** A testset for floating-point numbers.
1363 */
testset_fp(void)1364 void testset_fp(void){
1365 int n;
1366 int i;
1367 char zFP1[100];
1368 char zFP2[100];
1369
1370 n = g.szTest*5000;
1371 speedtest1_begin_test(100, "Fill a table with %d FP values", n*2);
1372 speedtest1_exec("BEGIN");
1373 speedtest1_exec("CREATE%s TABLE z1(a REAL %s, b REAL %s);",
1374 isTemp(1), g.zNN, g.zNN);
1375 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2); -- %d times", n);
1376 for(i=1; i<=n; i++){
1377 speedtest1_random_ascii_fp(zFP1);
1378 speedtest1_random_ascii_fp(zFP2);
1379 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1380 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1381 speedtest1_run();
1382 }
1383 speedtest1_exec("COMMIT");
1384 speedtest1_end_test();
1385
1386 n = g.szTest/25 + 2;
1387 speedtest1_begin_test(110, "%d range queries", n);
1388 speedtest1_prepare("SELECT sum(b) FROM z1 WHERE a BETWEEN ?1 AND ?2");
1389 for(i=1; i<=n; i++){
1390 speedtest1_random_ascii_fp(zFP1);
1391 speedtest1_random_ascii_fp(zFP2);
1392 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1393 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1394 speedtest1_run();
1395 }
1396 speedtest1_end_test();
1397
1398 speedtest1_begin_test(120, "CREATE INDEX three times");
1399 speedtest1_exec("BEGIN;");
1400 speedtest1_exec("CREATE INDEX t1a ON z1(a);");
1401 speedtest1_exec("CREATE INDEX t1b ON z1(b);");
1402 speedtest1_exec("CREATE INDEX t1ab ON z1(a,b);");
1403 speedtest1_exec("COMMIT;");
1404 speedtest1_end_test();
1405
1406 n = g.szTest/3 + 2;
1407 speedtest1_begin_test(130, "%d indexed range queries", n);
1408 speedtest1_prepare("SELECT sum(b) FROM z1 WHERE a BETWEEN ?1 AND ?2");
1409 for(i=1; i<=n; i++){
1410 speedtest1_random_ascii_fp(zFP1);
1411 speedtest1_random_ascii_fp(zFP2);
1412 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1413 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1414 speedtest1_run();
1415 }
1416 speedtest1_end_test();
1417
1418 n = g.szTest*5000;
1419 speedtest1_begin_test(140, "%d calls to round()", n);
1420 speedtest1_exec("SELECT sum(round(a,2)+round(b,4)) FROM z1;");
1421 speedtest1_end_test();
1422
1423
1424 speedtest1_begin_test(150, "%d printf() calls", n*4);
1425 speedtest1_exec(
1426 "WITH c(fmt) AS (VALUES('%%g'),('%%e'),('%%!g'),('%%.20f'))"
1427 "SELECT sum(printf(fmt,a)) FROM z1, c"
1428 );
1429 speedtest1_end_test();
1430 }
1431
1432 #ifdef SQLITE_ENABLE_RTREE
1433 /* Generate two numbers between 1 and mx. The first number is less than
1434 ** the second. Usually the numbers are near each other but can sometimes
1435 ** be far apart.
1436 */
twoCoords(int p1,int p2,unsigned mx,unsigned * pX0,unsigned * pX1)1437 static void twoCoords(
1438 int p1, int p2, /* Parameters adjusting sizes */
1439 unsigned mx, /* Range of 1..mx */
1440 unsigned *pX0, unsigned *pX1 /* OUT: write results here */
1441 ){
1442 unsigned d, x0, x1, span;
1443
1444 span = mx/100 + 1;
1445 if( speedtest1_random()%3==0 ) span *= p1;
1446 if( speedtest1_random()%p2==0 ) span = mx/2;
1447 d = speedtest1_random()%span + 1;
1448 x0 = speedtest1_random()%(mx-d) + 1;
1449 x1 = x0 + d;
1450 *pX0 = x0;
1451 *pX1 = x1;
1452 }
1453 #endif
1454
1455 #ifdef SQLITE_ENABLE_RTREE
1456 /* The following routine is an R-Tree geometry callback. It returns
1457 ** true if the object overlaps a slice on the Y coordinate between the
1458 ** two values given as arguments. In other words
1459 **
1460 ** SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
1461 **
1462 ** Is the same as saying:
1463 **
1464 ** SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
1465 */
xsliceGeometryCallback(sqlite3_rtree_geometry * p,int nCoord,double * aCoord,int * pRes)1466 static int xsliceGeometryCallback(
1467 sqlite3_rtree_geometry *p,
1468 int nCoord,
1469 double *aCoord,
1470 int *pRes
1471 ){
1472 *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
1473 return SQLITE_OK;
1474 }
1475 #endif /* SQLITE_ENABLE_RTREE */
1476
1477 #ifdef SQLITE_ENABLE_RTREE
1478 /*
1479 ** A testset for the R-Tree virtual table
1480 */
testset_rtree(int p1,int p2)1481 void testset_rtree(int p1, int p2){
1482 unsigned i, n;
1483 unsigned mxCoord;
1484 unsigned x0, x1, y0, y1, z0, z1;
1485 unsigned iStep;
1486 unsigned mxRowid;
1487 int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*500 );
1488
1489 mxCoord = 15000;
1490 mxRowid = n = g.szTest*500;
1491 speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
1492 speedtest1_exec("BEGIN");
1493 speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
1494 speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
1495 "VALUES(?1,?2,?3,?4,?5,?6,?7)");
1496 for(i=1; i<=n; i++){
1497 twoCoords(p1, p2, mxCoord, &x0, &x1);
1498 twoCoords(p1, p2, mxCoord, &y0, &y1);
1499 twoCoords(p1, p2, mxCoord, &z0, &z1);
1500 sqlite3_bind_int(g.pStmt, 1, i);
1501 sqlite3_bind_int(g.pStmt, 2, x0);
1502 sqlite3_bind_int(g.pStmt, 3, x1);
1503 sqlite3_bind_int(g.pStmt, 4, y0);
1504 sqlite3_bind_int(g.pStmt, 5, y1);
1505 sqlite3_bind_int(g.pStmt, 6, z0);
1506 sqlite3_bind_int(g.pStmt, 7, z1);
1507 speedtest1_run();
1508 }
1509 speedtest1_exec("COMMIT");
1510 speedtest1_end_test();
1511
1512 speedtest1_begin_test(101, "Copy from rtree to a regular table");
1513 speedtest1_exec("CREATE TABLE z1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
1514 speedtest1_exec("INSERT INTO z1 SELECT * FROM rt1");
1515 speedtest1_end_test();
1516
1517 n = g.szTest*200;
1518 speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
1519 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
1520 iStep = mxCoord/n;
1521 for(i=0; i<n; i++){
1522 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1523 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1524 speedtest1_run();
1525 aCheck[i] = atoi(g.zResult);
1526 }
1527 speedtest1_end_test();
1528
1529 if( g.bVerify ){
1530 n = g.szTest*200;
1531 speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
1532 speedtest1_prepare("SELECT count(*) FROM z1 WHERE x0>=?1 AND x1<=?2");
1533 iStep = mxCoord/n;
1534 for(i=0; i<n; i++){
1535 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1536 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1537 speedtest1_run();
1538 if( aCheck[i]!=atoi(g.zResult) ){
1539 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1540 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1541 }
1542 }
1543 speedtest1_end_test();
1544 }
1545
1546 n = g.szTest*200;
1547 speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
1548 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
1549 iStep = mxCoord/n;
1550 for(i=0; i<n; i++){
1551 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1552 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1553 speedtest1_run();
1554 aCheck[i] = atoi(g.zResult);
1555 }
1556 speedtest1_end_test();
1557
1558 if( g.bVerify ){
1559 n = g.szTest*200;
1560 speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
1561 speedtest1_prepare("SELECT count(*) FROM z1 WHERE y1>=?1 AND y0<=?2");
1562 iStep = mxCoord/n;
1563 for(i=0; i<n; i++){
1564 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1565 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1566 speedtest1_run();
1567 if( aCheck[i]!=atoi(g.zResult) ){
1568 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1569 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1570 }
1571 }
1572 speedtest1_end_test();
1573 }
1574
1575
1576 n = g.szTest*200;
1577 speedtest1_begin_test(125, "%d custom geometry callback queries", n);
1578 sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
1579 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
1580 iStep = mxCoord/n;
1581 for(i=0; i<n; i++){
1582 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1583 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1584 speedtest1_run();
1585 if( aCheck[i]!=atoi(g.zResult) ){
1586 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1587 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1588 }
1589 }
1590 speedtest1_end_test();
1591
1592 n = g.szTest*400;
1593 speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
1594 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
1595 " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
1596 iStep = mxCoord/n;
1597 for(i=0; i<n; i++){
1598 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1599 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1600 speedtest1_run();
1601 aCheck[i] = atoi(g.zResult);
1602 }
1603 speedtest1_end_test();
1604
1605 n = g.szTest*500;
1606 speedtest1_begin_test(140, "%d rowid queries", n);
1607 speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
1608 for(i=1; i<=n; i++){
1609 sqlite3_bind_int(g.pStmt, 1, i);
1610 speedtest1_run();
1611 }
1612 speedtest1_end_test();
1613
1614 n = g.szTest*50;
1615 speedtest1_begin_test(150, "%d UPDATEs using rowid", n);
1616 speedtest1_prepare("UPDATE rt1 SET x0=x0+100, x1=x1+100 WHERE id=?1");
1617 for(i=1; i<=n; i++){
1618 sqlite3_bind_int(g.pStmt, 1, (i*251)%mxRowid + 1);
1619 speedtest1_run();
1620 }
1621 speedtest1_end_test();
1622
1623 n = g.szTest*5;
1624 speedtest1_begin_test(155, "%d UPDATEs using one-dimensional overlap", n);
1625 speedtest1_prepare("UPDATE rt1 SET x0=x0-100, x1=x1-100"
1626 " WHERE y1>=?1 AND y0<=?1+5");
1627 iStep = mxCoord/n;
1628 for(i=0; i<n; i++){
1629 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1630 speedtest1_run();
1631 aCheck[i] = atoi(g.zResult);
1632 }
1633 speedtest1_end_test();
1634
1635 n = g.szTest*50;
1636 speedtest1_begin_test(160, "%d DELETEs using rowid", n);
1637 speedtest1_prepare("DELETE FROM rt1 WHERE id=?1");
1638 for(i=1; i<=n; i++){
1639 sqlite3_bind_int(g.pStmt, 1, (i*257)%mxRowid + 1);
1640 speedtest1_run();
1641 }
1642 speedtest1_end_test();
1643
1644
1645 n = g.szTest*5;
1646 speedtest1_begin_test(165, "%d DELETEs using one-dimensional overlap", n);
1647 speedtest1_prepare("DELETE FROM rt1 WHERE y1>=?1 AND y0<=?1+5");
1648 iStep = mxCoord/n;
1649 for(i=0; i<n; i++){
1650 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1651 speedtest1_run();
1652 aCheck[i] = atoi(g.zResult);
1653 }
1654 speedtest1_end_test();
1655
1656 speedtest1_begin_test(170, "Restore deleted entries using INSERT OR IGNORE");
1657 speedtest1_exec("INSERT OR IGNORE INTO rt1 SELECT * FROM z1");
1658 speedtest1_end_test();
1659 }
1660 #endif /* SQLITE_ENABLE_RTREE */
1661
1662 /*
1663 ** A testset that does key/value storage on tables with many columns.
1664 ** This is the kind of workload generated by ORMs such as CoreData.
1665 */
testset_orm(void)1666 void testset_orm(void){
1667 unsigned i, j, n;
1668 unsigned nRow;
1669 unsigned x1, len;
1670 char zNum[2000]; /* A number name */
1671 static const char zType[] = /* Types for all non-PK columns, in order */
1672 "IBBIIITIVVITBTBFBFITTFBTBVBVIFTBBFITFFVBIFIVBVVVBTVTIBBFFIVIBTB"
1673 "TVTTFTVTVFFIITIFBITFTTFFFVBIIBTTITFTFFVVVFIIITVBBVFFTVVB";
1674
1675 nRow = n = g.szTest*250;
1676 speedtest1_begin_test(100, "Fill %d rows", n);
1677 speedtest1_exec(
1678 "BEGIN;"
1679 "CREATE TABLE ZLOOKSLIKECOREDATA ("
1680 " ZPK INTEGER PRIMARY KEY,"
1681 " ZTERMFITTINGHOUSINGCOMMAND INTEGER,"
1682 " ZBRIEFGOBYDODGERHEIGHT BLOB,"
1683 " ZCAPABLETRIPDOORALMOND BLOB,"
1684 " ZDEPOSITPAIRCOLLEGECOMET INTEGER,"
1685 " ZFRAMEENTERSIMPLEMOUTH INTEGER,"
1686 " ZHOPEFULGATEHOLECHALK INTEGER,"
1687 " ZSLEEPYUSERGRANDBOWL TIMESTAMP,"
1688 " ZDEWPEACHCAREERCELERY INTEGER,"
1689 " ZHANGERLITHIUMDINNERMEET VARCHAR,"
1690 " ZCLUBRELEASELIZARDADVICE VARCHAR,"
1691 " ZCHARGECLICKHUMANEHIRE INTEGER,"
1692 " ZFINGERDUEPIZZAOPTION TIMESTAMP,"
1693 " ZFLYINGDOCTORTABLEMELODY BLOB,"
1694 " ZLONGFINLEAVEIMAGEOIL TIMESTAMP,"
1695 " ZFAMILYVISUALOWNERMATTER BLOB,"
1696 " ZGOLDYOUNGINITIALNOSE FLOAT,"
1697 " ZCAUSESALAMITERMCYAN BLOB,"
1698 " ZSPREADMOTORBISCUITBACON FLOAT,"
1699 " ZGIFTICEFISHGLUEHAIR INTEGER,"
1700 " ZNOTICEPEARPOLICYJUICE TIMESTAMP,"
1701 " ZBANKBUFFALORECOVERORBIT TIMESTAMP,"
1702 " ZLONGDIETESSAYNATURE FLOAT,"
1703 " ZACTIONRANGEELEGANTNEUTRON BLOB,"
1704 " ZCADETBRIGHTPLANETBANK TIMESTAMP,"
1705 " ZAIRFORGIVEHEADFROG BLOB,"
1706 " ZSHARKJUSTFRUITMOVIE VARCHAR,"
1707 " ZFARMERMORNINGMIRRORCONCERN BLOB,"
1708 " ZWOODPOETRYCOBBLERBENCH VARCHAR,"
1709 " ZHAFNIUMSCRIPTSALADMOTOR INTEGER,"
1710 " ZPROBLEMCLUBPOPOVERJELLY FLOAT,"
1711 " ZEIGHTLEADERWORKERMOST TIMESTAMP,"
1712 " ZGLASSRESERVEBARIUMMEAL BLOB,"
1713 " ZCLAMBITARUGULAFAJITA BLOB,"
1714 " ZDECADEJOYOUSWAVEHABIT FLOAT,"
1715 " ZCOMPANYSUMMERFIBERELF INTEGER,"
1716 " ZTREATTESTQUILLCHARGE TIMESTAMP,"
1717 " ZBROWBALANCEKEYCHOWDER FLOAT,"
1718 " ZPEACHCOPPERDINNERLAKE FLOAT,"
1719 " ZDRYWALLBEYONDBROWNBOWL VARCHAR,"
1720 " ZBELLYCRASHITEMLACK BLOB,"
1721 " ZTENNISCYCLEBILLOFFICER INTEGER,"
1722 " ZMALLEQUIPTHANKSGLUE FLOAT,"
1723 " ZMISSREPLYHUMANLIVING INTEGER,"
1724 " ZKIWIVISUALPRIDEAPPLE VARCHAR,"
1725 " ZWISHHITSKINMOTOR BLOB,"
1726 " ZCALMRACCOONPROGRAMDEBIT VARCHAR,"
1727 " ZSHINYASSISTLIVINGCRAB VARCHAR,"
1728 " ZRESOLVEWRISTWRAPAPPLE VARCHAR,"
1729 " ZAPPEALSIMPLESECONDHOUSING BLOB,"
1730 " ZCORNERANCHORTAPEDIVER TIMESTAMP,"
1731 " ZMEMORYREQUESTSOURCEBIG VARCHAR,"
1732 " ZTRYFACTKEEPMILK TIMESTAMP,"
1733 " ZDIVERPAINTLEATHEREASY INTEGER,"
1734 " ZSORTMISTYQUOTECABBAGE BLOB,"
1735 " ZTUNEGASBUFFALOCAPITAL BLOB,"
1736 " ZFILLSTOPLAWJOYFUL FLOAT,"
1737 " ZSTEELCAREFULPLATENUMBER FLOAT,"
1738 " ZGIVEVIVIDDIVINEMEANING INTEGER,"
1739 " ZTREATPACKFUTURECONVERT VARCHAR,"
1740 " ZCALMLYGEMFINISHEFFECT INTEGER,"
1741 " ZCABBAGESOCKEASEMINUTE BLOB,"
1742 " ZPLANETFAMILYPUREMEMORY TIMESTAMP,"
1743 " ZMERRYCRACKTRAINLEADER BLOB,"
1744 " ZMINORWAYPAPERCLASSY TIMESTAMP,"
1745 " ZEAGLELINEMINEMAIL VARCHAR,"
1746 " ZRESORTYARDGREENLET TIMESTAMP,"
1747 " ZYARDOREGANOVIVIDJEWEL TIMESTAMP,"
1748 " ZPURECAKEVIVIDNEATLY FLOAT,"
1749 " ZASKCONTACTMONITORFUN TIMESTAMP,"
1750 " ZMOVEWHOGAMMAINCH VARCHAR,"
1751 " ZLETTUCEBIRDMEETDEBATE TIMESTAMP,"
1752 " ZGENENATURALHEARINGKITE VARCHAR,"
1753 " ZMUFFINDRYERDRAWFORTUNE FLOAT,"
1754 " ZGRAYSURVEYWIRELOVE FLOAT,"
1755 " ZPLIERSPRINTASKOREGANO INTEGER,"
1756 " ZTRAVELDRIVERCONTESTLILY INTEGER,"
1757 " ZHUMORSPICESANDKIDNEY TIMESTAMP,"
1758 " ZARSENICSAMPLEWAITMUON INTEGER,"
1759 " ZLACEADDRESSGROUNDCAREFUL FLOAT,"
1760 " ZBAMBOOMESSWASABIEVENING BLOB,"
1761 " ZONERELEASEAVERAGENURSE INTEGER,"
1762 " ZRADIANTWHENTRYCARD TIMESTAMP,"
1763 " ZREWARDINSIDEMANGOINTENSE FLOAT,"
1764 " ZNEATSTEWPARTIRON TIMESTAMP,"
1765 " ZOUTSIDEPEAHENCOUNTICE TIMESTAMP,"
1766 " ZCREAMEVENINGLIPBRANCH FLOAT,"
1767 " ZWHALEMATHAVOCADOCOPPER FLOAT,"
1768 " ZLIFEUSELEAFYBELL FLOAT,"
1769 " ZWEALTHLINENGLEEFULDAY VARCHAR,"
1770 " ZFACEINVITETALKGOLD BLOB,"
1771 " ZWESTAMOUNTAFFECTHEARING INTEGER,"
1772 " ZDELAYOUTCOMEHORNAGENCY INTEGER,"
1773 " ZBIGTHINKCONVERTECONOMY BLOB,"
1774 " ZBASEGOUDAREGULARFORGIVE TIMESTAMP,"
1775 " ZPATTERNCLORINEGRANDCOLBY TIMESTAMP,"
1776 " ZCYANBASEFEEDADROIT INTEGER,"
1777 " ZCARRYFLOORMINNOWDRAGON TIMESTAMP,"
1778 " ZIMAGEPENCILOTHERBOTTOM FLOAT,"
1779 " ZXENONFLIGHTPALEAPPLE TIMESTAMP,"
1780 " ZHERRINGJOKEFEATUREHOPEFUL FLOAT,"
1781 " ZCAPYEARLYRIVETBRUSH FLOAT,"
1782 " ZAGEREEDFROGBASKET VARCHAR,"
1783 " ZUSUALBODYHALIBUTDIAMOND VARCHAR,"
1784 " ZFOOTTAPWORDENTRY VARCHAR,"
1785 " ZDISHKEEPBLESTMONITOR FLOAT,"
1786 " ZBROADABLESOLIDCASUAL INTEGER,"
1787 " ZSQUAREGLEEFULCHILDLIGHT INTEGER,"
1788 " ZHOLIDAYHEADPONYDETAIL INTEGER,"
1789 " ZGENERALRESORTSKYOPEN TIMESTAMP,"
1790 " ZGLADSPRAYKIDNEYGUPPY VARCHAR,"
1791 " ZSWIMHEAVYMENTIONKIND BLOB,"
1792 " ZMESSYSULFURDREAMFESTIVE BLOB,"
1793 " ZSKYSKYCLASSICBRIEF VARCHAR,"
1794 " ZDILLASKHOKILEMON FLOAT,"
1795 " ZJUNIORSHOWPRESSNOVA FLOAT,"
1796 " ZSIZETOEAWARDFRESH TIMESTAMP,"
1797 " ZKEYFAILAPRICOTMETAL VARCHAR,"
1798 " ZHANDYREPAIRPROTONAIRPORT VARCHAR,"
1799 " ZPOSTPROTEINHANDLEACTOR BLOB"
1800 ");"
1801 );
1802 speedtest1_prepare(
1803 "INSERT INTO ZLOOKSLIKECOREDATA(ZPK,ZAIRFORGIVEHEADFROG,"
1804 "ZGIFTICEFISHGLUEHAIR,ZDELAYOUTCOMEHORNAGENCY,ZSLEEPYUSERGRANDBOWL,"
1805 "ZGLASSRESERVEBARIUMMEAL,ZBRIEFGOBYDODGERHEIGHT,"
1806 "ZBAMBOOMESSWASABIEVENING,ZFARMERMORNINGMIRRORCONCERN,"
1807 "ZTREATPACKFUTURECONVERT,ZCAUSESALAMITERMCYAN,ZCALMRACCOONPROGRAMDEBIT,"
1808 "ZHOLIDAYHEADPONYDETAIL,ZWOODPOETRYCOBBLERBENCH,ZHAFNIUMSCRIPTSALADMOTOR,"
1809 "ZUSUALBODYHALIBUTDIAMOND,ZOUTSIDEPEAHENCOUNTICE,ZDIVERPAINTLEATHEREASY,"
1810 "ZWESTAMOUNTAFFECTHEARING,ZSIZETOEAWARDFRESH,ZDEWPEACHCAREERCELERY,"
1811 "ZSTEELCAREFULPLATENUMBER,ZCYANBASEFEEDADROIT,ZCALMLYGEMFINISHEFFECT,"
1812 "ZHANDYREPAIRPROTONAIRPORT,ZGENENATURALHEARINGKITE,ZBROADABLESOLIDCASUAL,"
1813 "ZPOSTPROTEINHANDLEACTOR,ZLACEADDRESSGROUNDCAREFUL,ZIMAGEPENCILOTHERBOTTOM,"
1814 "ZPROBLEMCLUBPOPOVERJELLY,ZPATTERNCLORINEGRANDCOLBY,ZNEATSTEWPARTIRON,"
1815 "ZAPPEALSIMPLESECONDHOUSING,ZMOVEWHOGAMMAINCH,ZTENNISCYCLEBILLOFFICER,"
1816 "ZSHARKJUSTFRUITMOVIE,ZKEYFAILAPRICOTMETAL,ZCOMPANYSUMMERFIBERELF,"
1817 "ZTERMFITTINGHOUSINGCOMMAND,ZRESORTYARDGREENLET,ZCABBAGESOCKEASEMINUTE,"
1818 "ZSQUAREGLEEFULCHILDLIGHT,ZONERELEASEAVERAGENURSE,ZBIGTHINKCONVERTECONOMY,"
1819 "ZPLIERSPRINTASKOREGANO,ZDECADEJOYOUSWAVEHABIT,ZDRYWALLBEYONDBROWNBOWL,"
1820 "ZCLUBRELEASELIZARDADVICE,ZWHALEMATHAVOCADOCOPPER,ZBELLYCRASHITEMLACK,"
1821 "ZLETTUCEBIRDMEETDEBATE,ZCAPABLETRIPDOORALMOND,ZRADIANTWHENTRYCARD,"
1822 "ZCAPYEARLYRIVETBRUSH,ZAGEREEDFROGBASKET,ZSWIMHEAVYMENTIONKIND,"
1823 "ZTRAVELDRIVERCONTESTLILY,ZGLADSPRAYKIDNEYGUPPY,ZBANKBUFFALORECOVERORBIT,"
1824 "ZFINGERDUEPIZZAOPTION,ZCLAMBITARUGULAFAJITA,ZLONGFINLEAVEIMAGEOIL,"
1825 "ZLONGDIETESSAYNATURE,ZJUNIORSHOWPRESSNOVA,ZHOPEFULGATEHOLECHALK,"
1826 "ZDEPOSITPAIRCOLLEGECOMET,ZWEALTHLINENGLEEFULDAY,ZFILLSTOPLAWJOYFUL,"
1827 "ZTUNEGASBUFFALOCAPITAL,ZGRAYSURVEYWIRELOVE,ZCORNERANCHORTAPEDIVER,"
1828 "ZREWARDINSIDEMANGOINTENSE,ZCADETBRIGHTPLANETBANK,ZPLANETFAMILYPUREMEMORY,"
1829 "ZTREATTESTQUILLCHARGE,ZCREAMEVENINGLIPBRANCH,ZSKYSKYCLASSICBRIEF,"
1830 "ZARSENICSAMPLEWAITMUON,ZBROWBALANCEKEYCHOWDER,ZFLYINGDOCTORTABLEMELODY,"
1831 "ZHANGERLITHIUMDINNERMEET,ZNOTICEPEARPOLICYJUICE,ZSHINYASSISTLIVINGCRAB,"
1832 "ZLIFEUSELEAFYBELL,ZFACEINVITETALKGOLD,ZGENERALRESORTSKYOPEN,"
1833 "ZPURECAKEVIVIDNEATLY,ZKIWIVISUALPRIDEAPPLE,ZMESSYSULFURDREAMFESTIVE,"
1834 "ZCHARGECLICKHUMANEHIRE,ZHERRINGJOKEFEATUREHOPEFUL,ZYARDOREGANOVIVIDJEWEL,"
1835 "ZFOOTTAPWORDENTRY,ZWISHHITSKINMOTOR,ZBASEGOUDAREGULARFORGIVE,"
1836 "ZMUFFINDRYERDRAWFORTUNE,ZACTIONRANGEELEGANTNEUTRON,ZTRYFACTKEEPMILK,"
1837 "ZPEACHCOPPERDINNERLAKE,ZFRAMEENTERSIMPLEMOUTH,ZMERRYCRACKTRAINLEADER,"
1838 "ZMEMORYREQUESTSOURCEBIG,ZCARRYFLOORMINNOWDRAGON,ZMINORWAYPAPERCLASSY,"
1839 "ZDILLASKHOKILEMON,ZRESOLVEWRISTWRAPAPPLE,ZASKCONTACTMONITORFUN,"
1840 "ZGIVEVIVIDDIVINEMEANING,ZEIGHTLEADERWORKERMOST,ZMISSREPLYHUMANLIVING,"
1841 "ZXENONFLIGHTPALEAPPLE,ZSORTMISTYQUOTECABBAGE,ZEAGLELINEMINEMAIL,"
1842 "ZFAMILYVISUALOWNERMATTER,ZSPREADMOTORBISCUITBACON,ZDISHKEEPBLESTMONITOR,"
1843 "ZMALLEQUIPTHANKSGLUE,ZGOLDYOUNGINITIALNOSE,ZHUMORSPICESANDKIDNEY)"
1844 "VALUES(?1,?26,?20,?93,?8,?33,?3,?81,?28,?60,?18,?47,?109,?29,?30,?104,?86,"
1845 "?54,?92,?117,?9,?58,?97,?61,?119,?73,?107,?120,?80,?99,?31,?96,?85,?50,?71,"
1846 "?42,?27,?118,?36,?2,?67,?62,?108,?82,?94,?76,?35,?40,?11,?88,?41,?72,?4,"
1847 "?83,?102,?103,?112,?77,?111,?22,?13,?34,?15,?23,?116,?7,?5,?90,?57,?56,"
1848 "?75,?51,?84,?25,?63,?37,?87,?114,?79,?38,?14,?10,?21,?48,?89,?91,?110,"
1849 "?69,?45,?113,?12,?101,?68,?105,?46,?95,?74,?24,?53,?39,?6,?64,?52,?98,"
1850 "?65,?115,?49,?70,?59,?32,?44,?100,?55,?66,?16,?19,?106,?43,?17,?78);"
1851 );
1852 for(i=0; i<n; i++){
1853 x1 = speedtest1_random();
1854 speedtest1_numbername(x1%1000, zNum, sizeof(zNum));
1855 len = (int)strlen(zNum);
1856 sqlite3_bind_int(g.pStmt, 1, i^0xf);
1857 for(j=0; zType[j]; j++){
1858 switch( zType[j] ){
1859 case 'I':
1860 case 'T':
1861 sqlite3_bind_int64(g.pStmt, j+2, x1);
1862 break;
1863 case 'F':
1864 sqlite3_bind_double(g.pStmt, j+2, (double)x1);
1865 break;
1866 case 'V':
1867 case 'B':
1868 sqlite3_bind_text64(g.pStmt, j+2, zNum, len,
1869 SQLITE_STATIC, SQLITE_UTF8);
1870 break;
1871 }
1872 }
1873 speedtest1_run();
1874 }
1875 speedtest1_exec("COMMIT;");
1876 speedtest1_end_test();
1877
1878 n = g.szTest*250;
1879 speedtest1_begin_test(110, "Query %d rows by rowid", n);
1880 speedtest1_prepare(
1881 "SELECT ZCYANBASEFEEDADROIT,ZJUNIORSHOWPRESSNOVA,ZCAUSESALAMITERMCYAN,"
1882 "ZHOPEFULGATEHOLECHALK,ZHUMORSPICESANDKIDNEY,ZSWIMHEAVYMENTIONKIND,"
1883 "ZMOVEWHOGAMMAINCH,ZAPPEALSIMPLESECONDHOUSING,ZHAFNIUMSCRIPTSALADMOTOR,"
1884 "ZNEATSTEWPARTIRON,ZLONGFINLEAVEIMAGEOIL,ZDEWPEACHCAREERCELERY,"
1885 "ZXENONFLIGHTPALEAPPLE,ZCALMRACCOONPROGRAMDEBIT,ZUSUALBODYHALIBUTDIAMOND,"
1886 "ZTRYFACTKEEPMILK,ZWEALTHLINENGLEEFULDAY,ZLONGDIETESSAYNATURE,"
1887 "ZLIFEUSELEAFYBELL,ZTREATPACKFUTURECONVERT,ZMEMORYREQUESTSOURCEBIG,"
1888 "ZYARDOREGANOVIVIDJEWEL,ZDEPOSITPAIRCOLLEGECOMET,ZSLEEPYUSERGRANDBOWL,"
1889 "ZBRIEFGOBYDODGERHEIGHT,ZCLUBRELEASELIZARDADVICE,ZCAPABLETRIPDOORALMOND,"
1890 "ZDRYWALLBEYONDBROWNBOWL,ZASKCONTACTMONITORFUN,ZKIWIVISUALPRIDEAPPLE,"
1891 "ZNOTICEPEARPOLICYJUICE,ZPEACHCOPPERDINNERLAKE,ZSTEELCAREFULPLATENUMBER,"
1892 "ZGLADSPRAYKIDNEYGUPPY,ZCOMPANYSUMMERFIBERELF,ZTENNISCYCLEBILLOFFICER,"
1893 "ZIMAGEPENCILOTHERBOTTOM,ZWESTAMOUNTAFFECTHEARING,ZDIVERPAINTLEATHEREASY,"
1894 "ZSKYSKYCLASSICBRIEF,ZMESSYSULFURDREAMFESTIVE,ZMERRYCRACKTRAINLEADER,"
1895 "ZBROADABLESOLIDCASUAL,ZGLASSRESERVEBARIUMMEAL,ZTUNEGASBUFFALOCAPITAL,"
1896 "ZBANKBUFFALORECOVERORBIT,ZTREATTESTQUILLCHARGE,ZBAMBOOMESSWASABIEVENING,"
1897 "ZREWARDINSIDEMANGOINTENSE,ZEAGLELINEMINEMAIL,ZCALMLYGEMFINISHEFFECT,"
1898 "ZKEYFAILAPRICOTMETAL,ZFINGERDUEPIZZAOPTION,ZCADETBRIGHTPLANETBANK,"
1899 "ZGOLDYOUNGINITIALNOSE,ZMISSREPLYHUMANLIVING,ZEIGHTLEADERWORKERMOST,"
1900 "ZFRAMEENTERSIMPLEMOUTH,ZBIGTHINKCONVERTECONOMY,ZFACEINVITETALKGOLD,"
1901 "ZPOSTPROTEINHANDLEACTOR,ZHERRINGJOKEFEATUREHOPEFUL,ZCABBAGESOCKEASEMINUTE,"
1902 "ZMUFFINDRYERDRAWFORTUNE,ZPROBLEMCLUBPOPOVERJELLY,ZGIVEVIVIDDIVINEMEANING,"
1903 "ZGENENATURALHEARINGKITE,ZGENERALRESORTSKYOPEN,ZLETTUCEBIRDMEETDEBATE,"
1904 "ZBASEGOUDAREGULARFORGIVE,ZCHARGECLICKHUMANEHIRE,ZPLANETFAMILYPUREMEMORY,"
1905 "ZMINORWAYPAPERCLASSY,ZCAPYEARLYRIVETBRUSH,ZSIZETOEAWARDFRESH,"
1906 "ZARSENICSAMPLEWAITMUON,ZSQUAREGLEEFULCHILDLIGHT,ZSHINYASSISTLIVINGCRAB,"
1907 "ZCORNERANCHORTAPEDIVER,ZDECADEJOYOUSWAVEHABIT,ZTRAVELDRIVERCONTESTLILY,"
1908 "ZFLYINGDOCTORTABLEMELODY,ZSHARKJUSTFRUITMOVIE,ZFAMILYVISUALOWNERMATTER,"
1909 "ZFARMERMORNINGMIRRORCONCERN,ZGIFTICEFISHGLUEHAIR,ZOUTSIDEPEAHENCOUNTICE,"
1910 "ZSPREADMOTORBISCUITBACON,ZWISHHITSKINMOTOR,ZHOLIDAYHEADPONYDETAIL,"
1911 "ZWOODPOETRYCOBBLERBENCH,ZAIRFORGIVEHEADFROG,ZBROWBALANCEKEYCHOWDER,"
1912 "ZDISHKEEPBLESTMONITOR,ZCLAMBITARUGULAFAJITA,ZPLIERSPRINTASKOREGANO,"
1913 "ZRADIANTWHENTRYCARD,ZDELAYOUTCOMEHORNAGENCY,ZPURECAKEVIVIDNEATLY,"
1914 "ZPATTERNCLORINEGRANDCOLBY,ZHANDYREPAIRPROTONAIRPORT,ZAGEREEDFROGBASKET,"
1915 "ZSORTMISTYQUOTECABBAGE,ZFOOTTAPWORDENTRY,ZRESOLVEWRISTWRAPAPPLE,"
1916 "ZDILLASKHOKILEMON,ZFILLSTOPLAWJOYFUL,ZACTIONRANGEELEGANTNEUTRON,"
1917 "ZRESORTYARDGREENLET,ZCREAMEVENINGLIPBRANCH,ZWHALEMATHAVOCADOCOPPER,"
1918 "ZGRAYSURVEYWIRELOVE,ZBELLYCRASHITEMLACK,ZHANGERLITHIUMDINNERMEET,"
1919 "ZCARRYFLOORMINNOWDRAGON,ZMALLEQUIPTHANKSGLUE,ZTERMFITTINGHOUSINGCOMMAND,"
1920 "ZONERELEASEAVERAGENURSE,ZLACEADDRESSGROUNDCAREFUL"
1921 " FROM ZLOOKSLIKECOREDATA WHERE ZPK=?1;"
1922 );
1923 for(i=0; i<n; i++){
1924 x1 = speedtest1_random()%nRow;
1925 sqlite3_bind_int(g.pStmt, 1, x1);
1926 speedtest1_run();
1927 }
1928 speedtest1_end_test();
1929 }
1930
1931 /*
1932 */
testset_trigger(void)1933 void testset_trigger(void){
1934 int jj, ii;
1935 char zNum[2000]; /* A number name */
1936
1937 const int NROW = 500*g.szTest;
1938 const int NROW2 = 100*g.szTest;
1939
1940 speedtest1_exec(
1941 "BEGIN;"
1942 "CREATE TABLE z1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1943 "CREATE TABLE z2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1944 "CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1945 "CREATE VIEW v1 AS SELECT rowid, i, t FROM z1;"
1946 "CREATE VIEW v2 AS SELECT rowid, i, t FROM z2;"
1947 "CREATE VIEW v3 AS SELECT rowid, i, t FROM t3;"
1948 );
1949 for(jj=1; jj<=3; jj++){
1950 speedtest1_prepare("INSERT INTO t%d VALUES(NULL,?1,?2)", jj);
1951 for(ii=0; ii<NROW; ii++){
1952 int x1 = speedtest1_random() % NROW;
1953 speedtest1_numbername(x1, zNum, sizeof(zNum));
1954 sqlite3_bind_int(g.pStmt, 1, x1);
1955 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1956 speedtest1_run();
1957 }
1958 }
1959 speedtest1_exec(
1960 "CREATE INDEX i1 ON z1(t);"
1961 "CREATE INDEX i2 ON z2(t);"
1962 "CREATE INDEX i3 ON t3(t);"
1963 "COMMIT;"
1964 );
1965
1966 speedtest1_begin_test(100, "speed4p-join1");
1967 speedtest1_prepare(
1968 "SELECT * FROM z1, z2, t3 WHERE z1.oid = z2.oid AND z2.oid = t3.oid"
1969 );
1970 speedtest1_run();
1971 speedtest1_end_test();
1972
1973 speedtest1_begin_test(110, "speed4p-join2");
1974 speedtest1_prepare(
1975 "SELECT * FROM z1, z2, t3 WHERE z1.t = z2.t AND z2.t = t3.t"
1976 );
1977 speedtest1_run();
1978 speedtest1_end_test();
1979
1980 speedtest1_begin_test(120, "speed4p-view1");
1981 for(jj=1; jj<=3; jj++){
1982 speedtest1_prepare("SELECT * FROM v%d WHERE rowid = ?", jj);
1983 for(ii=0; ii<NROW2; ii+=3){
1984 sqlite3_bind_int(g.pStmt, 1, ii*3);
1985 speedtest1_run();
1986 }
1987 }
1988 speedtest1_end_test();
1989
1990 speedtest1_begin_test(130, "speed4p-table1");
1991 for(jj=1; jj<=3; jj++){
1992 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
1993 for(ii=0; ii<NROW2; ii+=3){
1994 sqlite3_bind_int(g.pStmt, 1, ii*3);
1995 speedtest1_run();
1996 }
1997 }
1998 speedtest1_end_test();
1999
2000 speedtest1_begin_test(140, "speed4p-table1");
2001 for(jj=1; jj<=3; jj++){
2002 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
2003 for(ii=0; ii<NROW2; ii+=3){
2004 sqlite3_bind_int(g.pStmt, 1, ii*3);
2005 speedtest1_run();
2006 }
2007 }
2008 speedtest1_end_test();
2009
2010 speedtest1_begin_test(150, "speed4p-subselect1");
2011 speedtest1_prepare("SELECT "
2012 "(SELECT t FROM z1 WHERE rowid = ?1),"
2013 "(SELECT t FROM z2 WHERE rowid = ?1),"
2014 "(SELECT t FROM t3 WHERE rowid = ?1)"
2015 );
2016 for(jj=0; jj<NROW2; jj++){
2017 sqlite3_bind_int(g.pStmt, 1, jj*3);
2018 speedtest1_run();
2019 }
2020 speedtest1_end_test();
2021
2022 speedtest1_begin_test(160, "speed4p-rowid-update");
2023 speedtest1_exec("BEGIN");
2024 speedtest1_prepare("UPDATE z1 SET i=i+1 WHERE rowid=?1");
2025 for(jj=0; jj<NROW2; jj++){
2026 sqlite3_bind_int(g.pStmt, 1, jj);
2027 speedtest1_run();
2028 }
2029 speedtest1_exec("COMMIT");
2030 speedtest1_end_test();
2031
2032 speedtest1_exec("CREATE TABLE t5(t TEXT PRIMARY KEY, i INTEGER);");
2033 speedtest1_begin_test(170, "speed4p-insert-ignore");
2034 speedtest1_exec("INSERT OR IGNORE INTO t5 SELECT t, i FROM z1");
2035 speedtest1_end_test();
2036
2037 speedtest1_exec(
2038 "CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);"
2039 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2040 "CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN"
2041 " INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);"
2042 "END;"
2043 "CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN"
2044 " INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);"
2045 "END;"
2046 "CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN"
2047 " INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);"
2048 "END;"
2049 "BEGIN;"
2050 );
2051
2052 speedtest1_begin_test(180, "speed4p-trigger1");
2053 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
2054 for(jj=0; jj<NROW2; jj++){
2055 speedtest1_numbername(jj, zNum, sizeof(zNum));
2056 sqlite3_bind_int(g.pStmt, 1, jj);
2057 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2058 speedtest1_run();
2059 }
2060 speedtest1_end_test();
2061
2062 /*
2063 ** Note: Of the queries, only half actually update a row. This property
2064 ** was copied over from speed4p.test, where it was probably introduced
2065 ** inadvertantly.
2066 */
2067 speedtest1_begin_test(190, "speed4p-trigger2");
2068 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
2069 for(jj=1; jj<=NROW2*2; jj+=2){
2070 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
2071 sqlite3_bind_int(g.pStmt, 1, jj*2);
2072 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2073 sqlite3_bind_int(g.pStmt, 3, jj);
2074 speedtest1_run();
2075 }
2076 speedtest1_end_test();
2077
2078 /*
2079 ** Note: Same again.
2080 */
2081 speedtest1_begin_test(200, "speed4p-trigger3");
2082 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
2083 for(jj=1; jj<=NROW2*2; jj+=2){
2084 sqlite3_bind_int(g.pStmt, 1, jj*2);
2085 speedtest1_run();
2086 }
2087 speedtest1_end_test();
2088 speedtest1_exec("COMMIT");
2089
2090 /*
2091 ** The following block contains the same tests as the above block that
2092 ** tests triggers, with one crucial difference: no triggers are defined.
2093 ** So the difference in speed between these tests and the preceding ones
2094 ** is the amount of time taken to compile and execute the trigger programs.
2095 */
2096 speedtest1_exec(
2097 "DROP TABLE t4;"
2098 "DROP TABLE log;"
2099 "VACUUM;"
2100 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2101 "BEGIN;"
2102 );
2103 speedtest1_begin_test(210, "speed4p-notrigger1");
2104 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
2105 for(jj=0; jj<NROW2; jj++){
2106 speedtest1_numbername(jj, zNum, sizeof(zNum));
2107 sqlite3_bind_int(g.pStmt, 1, jj);
2108 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2109 speedtest1_run();
2110 }
2111 speedtest1_end_test();
2112 speedtest1_begin_test(210, "speed4p-notrigger2");
2113 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
2114 for(jj=1; jj<=NROW2*2; jj+=2){
2115 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
2116 sqlite3_bind_int(g.pStmt, 1, jj*2);
2117 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2118 sqlite3_bind_int(g.pStmt, 3, jj);
2119 speedtest1_run();
2120 }
2121 speedtest1_end_test();
2122 speedtest1_begin_test(220, "speed4p-notrigger3");
2123 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
2124 for(jj=1; jj<=NROW2*2; jj+=2){
2125 sqlite3_bind_int(g.pStmt, 1, jj*2);
2126 speedtest1_run();
2127 }
2128 speedtest1_end_test();
2129 speedtest1_exec("COMMIT");
2130 }
2131
2132 /*
2133 ** A testset used for debugging speedtest1 itself.
2134 */
testset_debug1(void)2135 void testset_debug1(void){
2136 unsigned i, n;
2137 unsigned x1, x2;
2138 char zNum[2000]; /* A number name */
2139
2140 n = g.szTest;
2141 for(i=1; i<=n; i++){
2142 x1 = swizzle(i, n);
2143 x2 = swizzle(x1, n);
2144 speedtest1_numbername(x1, zNum, sizeof(zNum));
2145 printf("%5d %5d %5d %s\n", i, x1, x2, zNum);
2146 }
2147 }
2148
2149 #ifdef __linux__
2150 #include <sys/types.h>
2151 #include <unistd.h>
2152
2153 /*
2154 ** Attempt to display I/O stats on Linux using /proc/PID/io
2155 */
displayLinuxIoStats(FILE * out)2156 static void displayLinuxIoStats(FILE *out){
2157 FILE *in;
2158 char z[200];
2159 sqlite3_snprintf(sizeof(z), z, "/proc/%d/io", getpid());
2160 in = fopen(z, "rb");
2161 if( in==0 ) return;
2162 while( fgets(z, sizeof(z), in)!=0 ){
2163 static const struct {
2164 const char *zPattern;
2165 const char *zDesc;
2166 } aTrans[] = {
2167 { "rchar: ", "Bytes received by read():" },
2168 { "wchar: ", "Bytes sent to write():" },
2169 { "syscr: ", "Read() system calls:" },
2170 { "syscw: ", "Write() system calls:" },
2171 { "read_bytes: ", "Bytes rcvd from storage:" },
2172 { "write_bytes: ", "Bytes sent to storage:" },
2173 { "cancelled_write_bytes: ", "Cancelled write bytes:" },
2174 };
2175 int i;
2176 for(i=0; i<sizeof(aTrans)/sizeof(aTrans[0]); i++){
2177 int n = (int)strlen(aTrans[i].zPattern);
2178 if( strncmp(aTrans[i].zPattern, z, n)==0 ){
2179 fprintf(out, "-- %-28s %s", aTrans[i].zDesc, &z[n]);
2180 break;
2181 }
2182 }
2183 }
2184 fclose(in);
2185 }
2186 #endif
2187
2188 #if SQLITE_VERSION_NUMBER<3006018
2189 # define sqlite3_sourceid(X) "(before 3.6.18)"
2190 #endif
2191
2192 #if SQLITE_CKSUMVFS_STATIC
2193 int sqlite3_register_cksumvfs(const char*);
2194 #endif
2195
xCompileOptions(void * pCtx,int nVal,char ** azVal,char ** azCol)2196 static int xCompileOptions(void *pCtx, int nVal, char **azVal, char **azCol){
2197 printf("-- Compile option: %s\n", azVal[0]);
2198 return SQLITE_OK;
2199 }
main(int argc,char ** argv)2200 int main(int argc, char **argv){
2201 int doAutovac = 0; /* True for --autovacuum */
2202 int cacheSize = 0; /* Desired cache size. 0 means default */
2203 int doExclusive = 0; /* True for --exclusive */
2204 int nHeap = 0, mnHeap = 0; /* Heap size from --heap */
2205 int doIncrvac = 0; /* True for --incrvacuum */
2206 const char *zJMode = 0; /* Journal mode */
2207 const char *zKey = 0; /* Encryption key */
2208 int nLook = -1, szLook = 0; /* --lookaside configuration */
2209 int noSync = 0; /* True for --nosync */
2210 int pageSize = 0; /* Desired page size. 0 means default */
2211 int nPCache = 0, szPCache = 0;/* --pcache configuration */
2212 int doPCache = 0; /* True if --pcache is seen */
2213 int showStats = 0; /* True for --stats */
2214 int nThread = 0; /* --threads value */
2215 int mmapSize = 0; /* How big of a memory map to use */
2216 int memDb = 0; /* --memdb. Use an in-memory database */
2217 int openFlags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
2218 ; /* SQLITE_OPEN_xxx flags. */
2219 char *zTSet = "main"; /* Which --testset torun */
2220 const char * zVfs = 0; /* --vfs NAME */
2221 int doTrace = 0; /* True for --trace */
2222 const char *zEncoding = 0; /* --utf16be or --utf16le */
2223 const char *zDbName = 0; /* Name of the test database */
2224
2225 void *pHeap = 0; /* Allocated heap space */
2226 void *pLook = 0; /* Allocated lookaside space */
2227 void *pPCache = 0; /* Allocated storage for pcache */
2228 int iCur, iHi; /* Stats values, current and "highwater" */
2229 int i; /* Loop counter */
2230 int rc; /* API return code */
2231
2232 #ifdef SQLITE_SPEEDTEST1_WASM
2233 /* Resetting all state is important for the WASM build, which may
2234 ** call main() multiple times. */
2235 memset(&g, 0, sizeof(g));
2236 iTestNumber = 0;
2237 #endif
2238 #ifdef SQLITE_CKSUMVFS_STATIC
2239 sqlite3_register_cksumvfs(0);
2240 #endif
2241 /*
2242 ** Confirms that argc has at least N arguments following argv[i]. */
2243 #define ARGC_VALUE_CHECK(N) \
2244 if( i>=argc-(N) ) fatal_error("missing argument on %s\n", argv[i])
2245 /* Display the version of SQLite being tested */
2246 printf("-- Speedtest1 for SQLite %s %.48s\n",
2247 sqlite3_libversion(), sqlite3_sourceid());
2248
2249 /* Process command-line arguments */
2250 g.zWR = "";
2251 g.zNN = "";
2252 g.zPK = "UNIQUE";
2253 g.szTest = 100;
2254 g.nRepeat = 1;
2255 for(i=1; i<argc; i++){
2256 const char *z = argv[i];
2257 if( z[0]=='-' ){
2258 do{ z++; }while( z[0]=='-' );
2259 if( strcmp(z,"autovacuum")==0 ){
2260 doAutovac = 1;
2261 }else if( strcmp(z,"big-transactions")==0 ){
2262 g.doBigTransactions = 1;
2263 }else if( strcmp(z,"cachesize")==0 ){
2264 ARGC_VALUE_CHECK(1);
2265 cacheSize = integerValue(argv[++i]);
2266 }else if( strcmp(z,"exclusive")==0 ){
2267 doExclusive = 1;
2268 }else if( strcmp(z,"checkpoint")==0 ){
2269 g.doCheckpoint = 1;
2270 }else if( strcmp(z,"explain")==0 ){
2271 g.bSqlOnly = 1;
2272 g.bExplain = 1;
2273 }else if( strcmp(z,"heap")==0 ){
2274 ARGC_VALUE_CHECK(2);
2275 nHeap = integerValue(argv[i+1]);
2276 mnHeap = integerValue(argv[i+2]);
2277 i += 2;
2278 }else if( strcmp(z,"incrvacuum")==0 ){
2279 doIncrvac = 1;
2280 }else if( strcmp(z,"journal")==0 ){
2281 ARGC_VALUE_CHECK(1);
2282 zJMode = argv[++i];
2283 }else if( strcmp(z,"key")==0 ){
2284 ARGC_VALUE_CHECK(1);
2285 zKey = argv[++i];
2286 }else if( strcmp(z,"lookaside")==0 ){
2287 ARGC_VALUE_CHECK(2);
2288 nLook = integerValue(argv[i+1]);
2289 szLook = integerValue(argv[i+2]);
2290 i += 2;
2291 }else if( strcmp(z,"memdb")==0 ){
2292 memDb = 1;
2293 #if SQLITE_VERSION_NUMBER>=3006000
2294 }else if( strcmp(z,"multithread")==0 ){
2295 sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
2296 }else if( strcmp(z,"nomemstat")==0 ){
2297 sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
2298 #endif
2299 #if SQLITE_VERSION_NUMBER>=3007017
2300 }else if( strcmp(z, "mmap")==0 ){
2301 ARGC_VALUE_CHECK(1);
2302 mmapSize = integerValue(argv[++i]);
2303 #endif
2304 }else if( strcmp(z,"nomutex")==0 ){
2305 openFlags |= SQLITE_OPEN_NOMUTEX;
2306 }else if( strcmp(z,"nosync")==0 ){
2307 noSync = 1;
2308 }else if( strcmp(z,"notnull")==0 ){
2309 g.zNN = "NOT NULL";
2310 }else if( strcmp(z,"output")==0 ){
2311 #ifdef SPEEDTEST_OMIT_HASH
2312 fatal_error("The --output option is not supported with"
2313 " -DSPEEDTEST_OMIT_HASH\n");
2314 #else
2315 ARGC_VALUE_CHECK(1);
2316 i++;
2317 if( strcmp(argv[i],"-")==0 ){
2318 g.hashFile = stdout;
2319 }else{
2320 g.hashFile = fopen(argv[i], "wb");
2321 if( g.hashFile==0 ){
2322 fatal_error("cannot open \"%s\" for writing\n", argv[i]);
2323 }
2324 }
2325 #endif
2326 }else if( strcmp(z,"pagesize")==0 ){
2327 ARGC_VALUE_CHECK(1);
2328 pageSize = integerValue(argv[++i]);
2329 }else if( strcmp(z,"pcache")==0 ){
2330 ARGC_VALUE_CHECK(2);
2331 nPCache = integerValue(argv[i+1]);
2332 szPCache = integerValue(argv[i+2]);
2333 doPCache = 1;
2334 i += 2;
2335 }else if( strcmp(z,"primarykey")==0 ){
2336 g.zPK = "PRIMARY KEY";
2337 }else if( strcmp(z,"repeat")==0 ){
2338 ARGC_VALUE_CHECK(1);
2339 g.nRepeat = integerValue(argv[++i]);
2340 }else if( strcmp(z,"reprepare")==0 ){
2341 g.bReprepare = 1;
2342 #if SQLITE_VERSION_NUMBER>=3006000
2343 }else if( strcmp(z,"serialized")==0 ){
2344 sqlite3_config(SQLITE_CONFIG_SERIALIZED);
2345 }else if( strcmp(z,"singlethread")==0 ){
2346 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
2347 #endif
2348 }else if( strcmp(z,"script")==0 ){
2349 ARGC_VALUE_CHECK(1);
2350 if( g.pScript ) fclose(g.pScript);
2351 g.pScript = fopen(argv[++i], "wb");
2352 if( g.pScript==0 ){
2353 fatal_error("unable to open output file \"%s\"\n", argv[i]);
2354 }
2355 }else if( strcmp(z,"sqlonly")==0 ){
2356 g.bSqlOnly = 1;
2357 }else if( strcmp(z,"shrink-memory")==0 ){
2358 g.bMemShrink = 1;
2359 }else if( strcmp(z,"size")==0 ){
2360 ARGC_VALUE_CHECK(1);
2361 g.szTest = integerValue(argv[++i]);
2362 }else if( strcmp(z,"stats")==0 ){
2363 showStats = 1;
2364 }else if( strcmp(z,"temp")==0 ){
2365 ARGC_VALUE_CHECK(1);
2366 i++;
2367 if( argv[i][0]<'0' || argv[i][0]>'9' || argv[i][1]!=0 ){
2368 fatal_error("argument to --temp should be integer between 0 and 9");
2369 }
2370 g.eTemp = argv[i][0] - '0';
2371 }else if( strcmp(z,"testset")==0 ){
2372 ARGC_VALUE_CHECK(1);
2373 zTSet = argv[++i];
2374 }else if( strcmp(z,"trace")==0 ){
2375 doTrace = 1;
2376 }else if( strcmp(z,"threads")==0 ){
2377 ARGC_VALUE_CHECK(1);
2378 nThread = integerValue(argv[++i]);
2379 }else if( strcmp(z,"utf16le")==0 ){
2380 zEncoding = "utf16le";
2381 }else if( strcmp(z,"utf16be")==0 ){
2382 zEncoding = "utf16be";
2383 }else if( strcmp(z,"verify")==0 ){
2384 g.bVerify = 1;
2385 #ifndef SPEEDTEST_OMIT_HASH
2386 HashInit();
2387 #endif
2388 }else if( strcmp(z,"vfs")==0 ){
2389 ARGC_VALUE_CHECK(1);
2390 zVfs = argv[++i];
2391 }else if( strcmp(z,"reserve")==0 ){
2392 ARGC_VALUE_CHECK(1);
2393 g.nReserve = atoi(argv[++i]);
2394 }else if( strcmp(z,"without-rowid")==0 ){
2395 if( strstr(g.zWR,"WITHOUT")!=0 ){
2396 /* no-op */
2397 }else if( strstr(g.zWR,"STRICT")!=0 ){
2398 g.zWR = "WITHOUT ROWID,STRICT";
2399 }else{
2400 g.zWR = "WITHOUT ROWID";
2401 }
2402 g.zPK = "PRIMARY KEY";
2403 }else if( strcmp(z,"strict")==0 ){
2404 if( strstr(g.zWR,"STRICT")!=0 ){
2405 /* no-op */
2406 }else if( strstr(g.zWR,"WITHOUT")!=0 ){
2407 g.zWR = "WITHOUT ROWID,STRICT";
2408 }else{
2409 g.zWR = "STRICT";
2410 }
2411 }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
2412 printf(zHelp, argv[0]);
2413 exit(0);
2414 }else{
2415 fatal_error("unknown option: %s\nUse \"%s -?\" for help\n",
2416 argv[i], argv[0]);
2417 }
2418 }else if( zDbName==0 ){
2419 zDbName = argv[i];
2420 }else{
2421 fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n",
2422 argv[i], argv[0]);
2423 }
2424 }
2425 #undef ARGC_VALUE_CHECK
2426 #if SQLITE_VERSION_NUMBER>=3006001
2427 if( nHeap>0 ){
2428 pHeap = malloc( nHeap );
2429 if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap);
2430 rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap);
2431 if( rc ) fatal_error("heap configuration failed: %d\n", rc);
2432 }
2433 if( doPCache ){
2434 if( nPCache>0 && szPCache>0 ){
2435 pPCache = malloc( nPCache*(sqlite3_int64)szPCache );
2436 if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n",
2437 nPCache*(sqlite3_int64)szPCache);
2438 }
2439 rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache);
2440 if( rc ) fatal_error("pcache configuration failed: %d\n", rc);
2441 }
2442 if( nLook>=0 ){
2443 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0);
2444 }
2445 #endif
2446 sqlite3_initialize();
2447
2448 if( zDbName!=0 ){
2449 sqlite3_vfs *pVfs = sqlite3_vfs_find(zVfs);
2450 /* For some VFSes, e.g. opfs, unlink() is not sufficient. Use the
2451 ** selected (or default) VFS's xDelete method to delete the
2452 ** database. This is specifically important for the "opfs" VFS
2453 ** when running from a WASM build of speedtest1, so that the db
2454 ** can be cleaned up properly. For historical compatibility, we'll
2455 ** also simply unlink(). */
2456 if( pVfs!=0 ){
2457 pVfs->xDelete(pVfs, zDbName, 1);
2458 }
2459 unlink(zDbName);
2460 }
2461
2462 /* Open the database and the input file */
2463 if( sqlite3_open_v2(memDb ? ":memory:" : zDbName, &g.db,
2464 openFlags, zVfs) ){
2465 fatal_error("Cannot open database file: %s\n", zDbName);
2466 }
2467 #if SQLITE_VERSION_NUMBER>=3006001
2468 if( nLook>0 && szLook>0 ){
2469 pLook = malloc( nLook*szLook );
2470 rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE,pLook,szLook,nLook);
2471 if( rc ) fatal_error("lookaside configuration failed: %d\n", rc);
2472 }
2473 #endif
2474 if( g.nReserve>0 ){
2475 sqlite3_file_control(g.db, 0, SQLITE_FCNTL_RESERVE_BYTES, &g.nReserve);
2476 }
2477
2478 /* Set database connection options */
2479 sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc, 0, 0);
2480 #ifndef SQLITE_OMIT_DEPRECATED
2481 if( doTrace ) sqlite3_trace(g.db, traceCallback, 0);
2482 #endif
2483 if( memDb>0 ){
2484 speedtest1_exec("PRAGMA temp_store=memory");
2485 }
2486 if( mmapSize>0 ){
2487 speedtest1_exec("PRAGMA mmap_size=%d", mmapSize);
2488 }
2489 speedtest1_exec("PRAGMA threads=%d", nThread);
2490 if( zKey ){
2491 speedtest1_exec("PRAGMA key('%s')", zKey);
2492 }
2493 if( zEncoding ){
2494 speedtest1_exec("PRAGMA encoding=%s", zEncoding);
2495 }
2496 if( doAutovac ){
2497 speedtest1_exec("PRAGMA auto_vacuum=FULL");
2498 }else if( doIncrvac ){
2499 speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL");
2500 }
2501 if( pageSize ){
2502 speedtest1_exec("PRAGMA page_size=%d", pageSize);
2503 }
2504 if( cacheSize ){
2505 speedtest1_exec("PRAGMA cache_size=%d", cacheSize);
2506 }
2507 if( noSync ) speedtest1_exec("PRAGMA synchronous=OFF");
2508 if( doExclusive ){
2509 speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
2510 }
2511 if( zJMode ){
2512 speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
2513 }
2514
2515 if( g.bExplain ) printf(".explain\n.echo on\n");
2516 do{
2517 char *zThisTest = zTSet;
2518 char *zComma = strchr(zThisTest,',');
2519 if( zComma ){
2520 *zComma = 0;
2521 zTSet = zComma+1;
2522 }else{
2523 zTSet = "";
2524 }
2525 if( g.iTotal>0 || zComma!=0 ){
2526 printf(" Begin testset \"%s\"\n", zThisTest);
2527 }
2528 if( strcmp(zThisTest,"main")==0 ){
2529 testset_main();
2530 }else if( strcmp(zThisTest,"debug1")==0 ){
2531 testset_debug1();
2532 }else if( strcmp(zThisTest,"orm")==0 ){
2533 testset_orm();
2534 }else if( strcmp(zThisTest,"cte")==0 ){
2535 testset_cte();
2536 }else if( strcmp(zThisTest,"fp")==0 ){
2537 testset_fp();
2538 }else if( strcmp(zThisTest,"trigger")==0 ){
2539 testset_trigger();
2540 }else if( strcmp(zThisTest,"rtree")==0 ){
2541 #ifdef SQLITE_ENABLE_RTREE
2542 testset_rtree(6, 147);
2543 #else
2544 fatal_error("compile with -DSQLITE_ENABLE_RTREE to enable "
2545 "the R-Tree tests\n");
2546 #endif
2547 }else{
2548 fatal_error("unknown testset: \"%s\"\n"
2549 "Choices: cte debug1 fp main orm rtree trigger\n",
2550 zThisTest);
2551 }
2552 if( zTSet[0] ){
2553 char *zSql, *zObj;
2554 speedtest1_begin_test(999, "Reset the database");
2555 while( 1 ){
2556 zObj = speedtest1_once(
2557 "SELECT name FROM main.sqlite_master"
2558 " WHERE sql LIKE 'CREATE %%TABLE%%'");
2559 if( zObj==0 ) break;
2560 zSql = sqlite3_mprintf("DROP TABLE main.\"%w\"", zObj);
2561 speedtest1_exec(zSql);
2562 sqlite3_free(zSql);
2563 sqlite3_free(zObj);
2564 }
2565 while( 1 ){
2566 zObj = speedtest1_once(
2567 "SELECT name FROM temp.sqlite_master"
2568 " WHERE sql LIKE 'CREATE %%TABLE%%'");
2569 if( zObj==0 ) break;
2570 zSql = sqlite3_mprintf("DROP TABLE main.\"%w\"", zObj);
2571 speedtest1_exec(zSql);
2572 sqlite3_free(zSql);
2573 sqlite3_free(zObj);
2574 }
2575 speedtest1_end_test();
2576 }
2577 }while( zTSet[0] );
2578 speedtest1_final();
2579
2580 if( showStats ){
2581 sqlite3_exec(g.db, "PRAGMA compile_options", xCompileOptions, 0, 0);
2582 }
2583
2584 /* Database connection statistics printed after both prepared statements
2585 ** have been finalized */
2586 #if SQLITE_VERSION_NUMBER>=3007009
2587 if( showStats ){
2588 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0);
2589 printf("-- Lookaside Slots Used: %d (max %d)\n", iCur,iHi);
2590 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0);
2591 printf("-- Successful lookasides: %d\n", iHi);
2592 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0);
2593 printf("-- Lookaside size faults: %d\n", iHi);
2594 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0);
2595 printf("-- Lookaside OOM faults: %d\n", iHi);
2596 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0);
2597 printf("-- Pager Heap Usage: %d bytes\n", iCur);
2598 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1);
2599 printf("-- Page cache hits: %d\n", iCur);
2600 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1);
2601 printf("-- Page cache misses: %d\n", iCur);
2602 #if SQLITE_VERSION_NUMBER>=3007012
2603 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1);
2604 printf("-- Page cache writes: %d\n", iCur);
2605 #endif
2606 sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0);
2607 printf("-- Schema Heap Usage: %d bytes\n", iCur);
2608 sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0);
2609 printf("-- Statement Heap Usage: %d bytes\n", iCur);
2610 }
2611 #endif
2612
2613 sqlite3_close(g.db);
2614
2615 #if SQLITE_VERSION_NUMBER>=3006001
2616 /* Global memory usage statistics printed after the database connection
2617 ** has closed. Memory usage should be zero at this point. */
2618 if( showStats ){
2619 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0);
2620 printf("-- Memory Used (bytes): %d (max %d)\n", iCur,iHi);
2621 #if SQLITE_VERSION_NUMBER>=3007000
2622 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0);
2623 printf("-- Outstanding Allocations: %d (max %d)\n", iCur,iHi);
2624 #endif
2625 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0);
2626 printf("-- Pcache Overflow Bytes: %d (max %d)\n", iCur,iHi);
2627 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0);
2628 printf("-- Largest Allocation: %d bytes\n",iHi);
2629 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0);
2630 printf("-- Largest Pcache Allocation: %d bytes\n",iHi);
2631 }
2632 #endif
2633
2634 #ifdef __linux__
2635 if( showStats ){
2636 displayLinuxIoStats(stdout);
2637 }
2638 #endif
2639 if( g.pScript ){
2640 fclose(g.pScript);
2641 }
2642
2643 /* Release memory */
2644 free( pLook );
2645 free( pPCache );
2646 free( pHeap );
2647 return 0;
2648 }
2649
2650 #ifdef SQLITE_SPEEDTEST1_WASM
2651 /*
2652 ** A workaround for some inconsistent behaviour with how
2653 ** main() does (or does not) get exported to WASM.
2654 */
wasm_main(int argc,char ** argv)2655 int wasm_main(int argc, char **argv){
2656 return main(argc, argv);
2657 }
2658 #endif
2659