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