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