?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Snapshot of upstream SQLite check-in 5a877221
[sqlcipher.git] / test / speedtest1.c
blob68ca5788cc4c46b67dd390a328c5aab1acf3efed
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 n = g.szTest*5000;
1196 speedtest1_begin_test(140, "%d calls to round()", n);
1197 speedtest1_exec("SELECT sum(round(a,2)+round(b,4)) FROM t1;");
1198 speedtest1_end_test();
1199
1200
1201 speedtest1_begin_test(150, "%d printf() calls", n*4);
1202 speedtest1_exec(
1203 "WITH c(fmt) AS (VALUES('%%g'),('%%e'),('%%!g'),('%%.20f'))"
1204 "SELECT sum(printf(fmt,a)) FROM t1, c"
1205 );
1206 speedtest1_end_test();
1207 }
1208
1209 #ifdef SQLITE_ENABLE_RTREE
1210 /* Generate two numbers between 1 and mx. The first number is less than
1211 ** the second. Usually the numbers are near each other but can sometimes
1212 ** be far apart.
1213 */
1214 static void twoCoords(
1215 int p1, int p2, /* Parameters adjusting sizes */
1216 unsigned mx, /* Range of 1..mx */
1217 unsigned *pX0, unsigned *pX1 /* OUT: write results here */
1218 ){
1219 unsigned d, x0, x1, span;
1220
1221 span = mx/100 + 1;
1222 if( speedtest1_random()%3==0 ) span *= p1;
1223 if( speedtest1_random()%p2==0 ) span = mx/2;
1224 d = speedtest1_random()%span + 1;
1225 x0 = speedtest1_random()%(mx-d) + 1;
1226 x1 = x0 + d;
1227 *pX0 = x0;
1228 *pX1 = x1;
1229 }
1230 #endif
1231
1232 #ifdef SQLITE_ENABLE_RTREE
1233 /* The following routine is an R-Tree geometry callback. It returns
1234 ** true if the object overlaps a slice on the Y coordinate between the
1235 ** two values given as arguments. In other words
1236 **
1237 ** SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
1238 **
1239 ** Is the same as saying:
1240 **
1241 ** SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
1242 */
1243 static int xsliceGeometryCallback(
1244 sqlite3_rtree_geometry *p,
1245 int nCoord,
1246 double *aCoord,
1247 int *pRes
1248 ){
1249 *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
1250 return SQLITE_OK;
1251 }
1252 #endif /* SQLITE_ENABLE_RTREE */
1253
1254 #ifdef SQLITE_ENABLE_RTREE
1255 /*
1256 ** A testset for the R-Tree virtual table
1257 */
1258 void testset_rtree(int p1, int p2){
1259 unsigned i, n;
1260 unsigned mxCoord;
1261 unsigned x0, x1, y0, y1, z0, z1;
1262 unsigned iStep;
1263 unsigned mxRowid;
1264 int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*500 );
1265
1266 mxCoord = 15000;
1267 mxRowid = n = g.szTest*500;
1268 speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
1269 speedtest1_exec("BEGIN");
1270 speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
1271 speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
1272 "VALUES(?1,?2,?3,?4,?5,?6,?7)");
1273 for(i=1; i<=n; i++){
1274 twoCoords(p1, p2, mxCoord, &x0, &x1);
1275 twoCoords(p1, p2, mxCoord, &y0, &y1);
1276 twoCoords(p1, p2, mxCoord, &z0, &z1);
1277 sqlite3_bind_int(g.pStmt, 1, i);
1278 sqlite3_bind_int(g.pStmt, 2, x0);
1279 sqlite3_bind_int(g.pStmt, 3, x1);
1280 sqlite3_bind_int(g.pStmt, 4, y0);
1281 sqlite3_bind_int(g.pStmt, 5, y1);
1282 sqlite3_bind_int(g.pStmt, 6, z0);
1283 sqlite3_bind_int(g.pStmt, 7, z1);
1284 speedtest1_run();
1285 }
1286 speedtest1_exec("COMMIT");
1287 speedtest1_end_test();
1288
1289 speedtest1_begin_test(101, "Copy from rtree to a regular table");
1290 speedtest1_exec("CREATE TABLE t1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
1291 speedtest1_exec("INSERT INTO t1 SELECT * FROM rt1");
1292 speedtest1_end_test();
1293
1294 n = g.szTest*200;
1295 speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
1296 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
1297 iStep = mxCoord/n;
1298 for(i=0; i<n; i++){
1299 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1300 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1301 speedtest1_run();
1302 aCheck[i] = atoi(g.zResult);
1303 }
1304 speedtest1_end_test();
1305
1306 if( g.bVerify ){
1307 n = g.szTest*200;
1308 speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
1309 speedtest1_prepare("SELECT count(*) FROM t1 WHERE x0>=?1 AND x1<=?2");
1310 iStep = mxCoord/n;
1311 for(i=0; i<n; i++){
1312 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1313 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1314 speedtest1_run();
1315 if( aCheck[i]!=atoi(g.zResult) ){
1316 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1317 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1318 }
1319 }
1320 speedtest1_end_test();
1321 }
1322
1323 n = g.szTest*200;
1324 speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
1325 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
1326 iStep = mxCoord/n;
1327 for(i=0; i<n; i++){
1328 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1329 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1330 speedtest1_run();
1331 aCheck[i] = atoi(g.zResult);
1332 }
1333 speedtest1_end_test();
1334
1335 if( g.bVerify ){
1336 n = g.szTest*200;
1337 speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
1338 speedtest1_prepare("SELECT count(*) FROM t1 WHERE y1>=?1 AND y0<=?2");
1339 iStep = mxCoord/n;
1340 for(i=0; i<n; i++){
1341 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1342 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1343 speedtest1_run();
1344 if( aCheck[i]!=atoi(g.zResult) ){
1345 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1346 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1347 }
1348 }
1349 speedtest1_end_test();
1350 }
1351
1352
1353 n = g.szTest*200;
1354 speedtest1_begin_test(125, "%d custom geometry callback queries", n);
1355 sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
1356 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
1357 iStep = mxCoord/n;
1358 for(i=0; i<n; i++){
1359 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1360 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1361 speedtest1_run();
1362 if( aCheck[i]!=atoi(g.zResult) ){
1363 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1364 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1365 }
1366 }
1367 speedtest1_end_test();
1368
1369 n = g.szTest*400;
1370 speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
1371 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
1372 " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
1373 iStep = mxCoord/n;
1374 for(i=0; i<n; i++){
1375 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1376 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1377 speedtest1_run();
1378 aCheck[i] = atoi(g.zResult);
1379 }
1380 speedtest1_end_test();
1381
1382 n = g.szTest*500;
1383 speedtest1_begin_test(140, "%d rowid queries", n);
1384 speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
1385 for(i=1; i<=n; i++){
1386 sqlite3_bind_int(g.pStmt, 1, i);
1387 speedtest1_run();
1388 }
1389 speedtest1_end_test();
1390
1391 n = g.szTest*50;
1392 speedtest1_begin_test(150, "%d UPDATEs using rowid", n);
1393 speedtest1_prepare("UPDATE rt1 SET x0=x0+100, x1=x1+100 WHERE id=?1");
1394 for(i=1; i<=n; i++){
1395 sqlite3_bind_int(g.pStmt, 1, (i*251)%mxRowid + 1);
1396 speedtest1_run();
1397 }
1398 speedtest1_end_test();
1399
1400 n = g.szTest*5;
1401 speedtest1_begin_test(155, "%d UPDATEs using one-dimensional overlap", n);
1402 speedtest1_prepare("UPDATE rt1 SET x0=x0-100, x1=x1-100"
1403 " WHERE y1>=?1 AND y0<=?1+5");
1404 iStep = mxCoord/n;
1405 for(i=0; i<n; i++){
1406 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1407 speedtest1_run();
1408 aCheck[i] = atoi(g.zResult);
1409 }
1410 speedtest1_end_test();
1411
1412 n = g.szTest*50;
1413 speedtest1_begin_test(160, "%d DELETEs using rowid", n);
1414 speedtest1_prepare("DELETE FROM rt1 WHERE id=?1");
1415 for(i=1; i<=n; i++){
1416 sqlite3_bind_int(g.pStmt, 1, (i*257)%mxRowid + 1);
1417 speedtest1_run();
1418 }
1419 speedtest1_end_test();
1420
1421
1422 n = g.szTest*5;
1423 speedtest1_begin_test(165, "%d DELETEs using one-dimensional overlap", n);
1424 speedtest1_prepare("DELETE FROM rt1 WHERE y1>=?1 AND y0<=?1+5");
1425 iStep = mxCoord/n;
1426 for(i=0; i<n; i++){
1427 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1428 speedtest1_run();
1429 aCheck[i] = atoi(g.zResult);
1430 }
1431 speedtest1_end_test();
1432
1433 speedtest1_begin_test(170, "Restore deleted entries using INSERT OR IGNORE");
1434 speedtest1_exec("INSERT OR IGNORE INTO rt1 SELECT * FROM t1");
1435 speedtest1_end_test();
1436 }
1437 #endif /* SQLITE_ENABLE_RTREE */
1438
1439 /*
1440 ** A testset that does key/value storage on tables with many columns.
1441 ** This is the kind of workload generated by ORMs such as CoreData.
1442 */
1443 void testset_orm(void){
1444 unsigned i, j, n;
1445 unsigned nRow;
1446 unsigned x1, len;
1447 char zNum[2000]; /* A number name */
1448 static const char zType[] = /* Types for all non-PK columns, in order */
1449 "IBBIIITIVVITBTBFBFITTFBTBVBVIFTBBFITFFVBIFIVBVVVBTVTIBBFFIVIBTB"
1450 "TVTTFTVTVFFIITIFBITFTTFFFVBIIBTTITFTFFVVVFIIITVBBVFFTVVB";
1451
1452 nRow = n = g.szTest*250;
1453 speedtest1_begin_test(100, "Fill %d rows", n);
1454 speedtest1_exec(
1455 "BEGIN;"
1456 "CREATE TABLE ZLOOKSLIKECOREDATA ("
1457 " ZPK INTEGER PRIMARY KEY,"
1458 " ZTERMFITTINGHOUSINGCOMMAND INTEGER,"
1459 " ZBRIEFGOBYDODGERHEIGHT BLOB,"
1460 " ZCAPABLETRIPDOORALMOND BLOB,"
1461 " ZDEPOSITPAIRCOLLEGECOMET INTEGER,"
1462 " ZFRAMEENTERSIMPLEMOUTH INTEGER,"
1463 " ZHOPEFULGATEHOLECHALK INTEGER,"
1464 " ZSLEEPYUSERGRANDBOWL TIMESTAMP,"
1465 " ZDEWPEACHCAREERCELERY INTEGER,"
1466 " ZHANGERLITHIUMDINNERMEET VARCHAR,"
1467 " ZCLUBRELEASELIZARDADVICE VARCHAR,"
1468 " ZCHARGECLICKHUMANEHIRE INTEGER,"
1469 " ZFINGERDUEPIZZAOPTION TIMESTAMP,"
1470 " ZFLYINGDOCTORTABLEMELODY BLOB,"
1471 " ZLONGFINLEAVEIMAGEOIL TIMESTAMP,"
1472 " ZFAMILYVISUALOWNERMATTER BLOB,"
1473 " ZGOLDYOUNGINITIALNOSE FLOAT,"
1474 " ZCAUSESALAMITERMCYAN BLOB,"
1475 " ZSPREADMOTORBISCUITBACON FLOAT,"
1476 " ZGIFTICEFISHGLUEHAIR INTEGER,"
1477 " ZNOTICEPEARPOLICYJUICE TIMESTAMP,"
1478 " ZBANKBUFFALORECOVERORBIT TIMESTAMP,"
1479 " ZLONGDIETESSAYNATURE FLOAT,"
1480 " ZACTIONRANGEELEGANTNEUTRON BLOB,"
1481 " ZCADETBRIGHTPLANETBANK TIMESTAMP,"
1482 " ZAIRFORGIVEHEADFROG BLOB,"
1483 " ZSHARKJUSTFRUITMOVIE VARCHAR,"
1484 " ZFARMERMORNINGMIRRORCONCERN BLOB,"
1485 " ZWOODPOETRYCOBBLERBENCH VARCHAR,"
1486 " ZHAFNIUMSCRIPTSALADMOTOR INTEGER,"
1487 " ZPROBLEMCLUBPOPOVERJELLY FLOAT,"
1488 " ZEIGHTLEADERWORKERMOST TIMESTAMP,"
1489 " ZGLASSRESERVEBARIUMMEAL BLOB,"
1490 " ZCLAMBITARUGULAFAJITA BLOB,"
1491 " ZDECADEJOYOUSWAVEHABIT FLOAT,"
1492 " ZCOMPANYSUMMERFIBERELF INTEGER,"
1493 " ZTREATTESTQUILLCHARGE TIMESTAMP,"
1494 " ZBROWBALANCEKEYCHOWDER FLOAT,"
1495 " ZPEACHCOPPERDINNERLAKE FLOAT,"
1496 " ZDRYWALLBEYONDBROWNBOWL VARCHAR,"
1497 " ZBELLYCRASHITEMLACK BLOB,"
1498 " ZTENNISCYCLEBILLOFFICER INTEGER,"
1499 " ZMALLEQUIPTHANKSGLUE FLOAT,"
1500 " ZMISSREPLYHUMANLIVING INTEGER,"
1501 " ZKIWIVISUALPRIDEAPPLE VARCHAR,"
1502 " ZWISHHITSKINMOTOR BLOB,"
1503 " ZCALMRACCOONPROGRAMDEBIT VARCHAR,"
1504 " ZSHINYASSISTLIVINGCRAB VARCHAR,"
1505 " ZRESOLVEWRISTWRAPAPPLE VARCHAR,"
1506 " ZAPPEALSIMPLESECONDHOUSING BLOB,"
1507 " ZCORNERANCHORTAPEDIVER TIMESTAMP,"
1508 " ZMEMORYREQUESTSOURCEBIG VARCHAR,"
1509 " ZTRYFACTKEEPMILK TIMESTAMP,"
1510 " ZDIVERPAINTLEATHEREASY INTEGER,"
1511 " ZSORTMISTYQUOTECABBAGE BLOB,"
1512 " ZTUNEGASBUFFALOCAPITAL BLOB,"
1513 " ZFILLSTOPLAWJOYFUL FLOAT,"
1514 " ZSTEELCAREFULPLATENUMBER FLOAT,"
1515 " ZGIVEVIVIDDIVINEMEANING INTEGER,"
1516 " ZTREATPACKFUTURECONVERT VARCHAR,"
1517 " ZCALMLYGEMFINISHEFFECT INTEGER,"
1518 " ZCABBAGESOCKEASEMINUTE BLOB,"
1519 " ZPLANETFAMILYPUREMEMORY TIMESTAMP,"
1520 " ZMERRYCRACKTRAINLEADER BLOB,"
1521 " ZMINORWAYPAPERCLASSY TIMESTAMP,"
1522 " ZEAGLELINEMINEMAIL VARCHAR,"
1523 " ZRESORTYARDGREENLET TIMESTAMP,"
1524 " ZYARDOREGANOVIVIDJEWEL TIMESTAMP,"
1525 " ZPURECAKEVIVIDNEATLY FLOAT,"
1526 " ZASKCONTACTMONITORFUN TIMESTAMP,"
1527 " ZMOVEWHOGAMMAINCH VARCHAR,"
1528 " ZLETTUCEBIRDMEETDEBATE TIMESTAMP,"
1529 " ZGENENATURALHEARINGKITE VARCHAR,"
1530 " ZMUFFINDRYERDRAWFORTUNE FLOAT,"
1531 " ZGRAYSURVEYWIRELOVE FLOAT,"
1532 " ZPLIERSPRINTASKOREGANO INTEGER,"
1533 " ZTRAVELDRIVERCONTESTLILY INTEGER,"
1534 " ZHUMORSPICESANDKIDNEY TIMESTAMP,"
1535 " ZARSENICSAMPLEWAITMUON INTEGER,"
1536 " ZLACEADDRESSGROUNDCAREFUL FLOAT,"
1537 " ZBAMBOOMESSWASABIEVENING BLOB,"
1538 " ZONERELEASEAVERAGENURSE INTEGER,"
1539 " ZRADIANTWHENTRYCARD TIMESTAMP,"
1540 " ZREWARDINSIDEMANGOINTENSE FLOAT,"
1541 " ZNEATSTEWPARTIRON TIMESTAMP,"
1542 " ZOUTSIDEPEAHENCOUNTICE TIMESTAMP,"
1543 " ZCREAMEVENINGLIPBRANCH FLOAT,"
1544 " ZWHALEMATHAVOCADOCOPPER FLOAT,"
1545 " ZLIFEUSELEAFYBELL FLOAT,"
1546 " ZWEALTHLINENGLEEFULDAY VARCHAR,"
1547 " ZFACEINVITETALKGOLD BLOB,"
1548 " ZWESTAMOUNTAFFECTHEARING INTEGER,"
1549 " ZDELAYOUTCOMEHORNAGENCY INTEGER,"
1550 " ZBIGTHINKCONVERTECONOMY BLOB,"
1551 " ZBASEGOUDAREGULARFORGIVE TIMESTAMP,"
1552 " ZPATTERNCLORINEGRANDCOLBY TIMESTAMP,"
1553 " ZCYANBASEFEEDADROIT INTEGER,"
1554 " ZCARRYFLOORMINNOWDRAGON TIMESTAMP,"
1555 " ZIMAGEPENCILOTHERBOTTOM FLOAT,"
1556 " ZXENONFLIGHTPALEAPPLE TIMESTAMP,"
1557 " ZHERRINGJOKEFEATUREHOPEFUL FLOAT,"
1558 " ZCAPYEARLYRIVETBRUSH FLOAT,"
1559 " ZAGEREEDFROGBASKET VARCHAR,"
1560 " ZUSUALBODYHALIBUTDIAMOND VARCHAR,"
1561 " ZFOOTTAPWORDENTRY VARCHAR,"
1562 " ZDISHKEEPBLESTMONITOR FLOAT,"
1563 " ZBROADABLESOLIDCASUAL INTEGER,"
1564 " ZSQUAREGLEEFULCHILDLIGHT INTEGER,"
1565 " ZHOLIDAYHEADPONYDETAIL INTEGER,"
1566 " ZGENERALRESORTSKYOPEN TIMESTAMP,"
1567 " ZGLADSPRAYKIDNEYGUPPY VARCHAR,"
1568 " ZSWIMHEAVYMENTIONKIND BLOB,"
1569 " ZMESSYSULFURDREAMFESTIVE BLOB,"
1570 " ZSKYSKYCLASSICBRIEF VARCHAR,"
1571 " ZDILLASKHOKILEMON FLOAT,"
1572 " ZJUNIORSHOWPRESSNOVA FLOAT,"
1573 " ZSIZETOEAWARDFRESH TIMESTAMP,"
1574 " ZKEYFAILAPRICOTMETAL VARCHAR,"
1575 " ZHANDYREPAIRPROTONAIRPORT VARCHAR,"
1576 " ZPOSTPROTEINHANDLEACTOR BLOB"
1577 ");"
1578 );
1579 speedtest1_prepare(
1580 "INSERT INTO ZLOOKSLIKECOREDATA(ZPK,ZAIRFORGIVEHEADFROG,"
1581 "ZGIFTICEFISHGLUEHAIR,ZDELAYOUTCOMEHORNAGENCY,ZSLEEPYUSERGRANDBOWL,"
1582 "ZGLASSRESERVEBARIUMMEAL,ZBRIEFGOBYDODGERHEIGHT,"
1583 "ZBAMBOOMESSWASABIEVENING,ZFARMERMORNINGMIRRORCONCERN,"
1584 "ZTREATPACKFUTURECONVERT,ZCAUSESALAMITERMCYAN,ZCALMRACCOONPROGRAMDEBIT,"
1585 "ZHOLIDAYHEADPONYDETAIL,ZWOODPOETRYCOBBLERBENCH,ZHAFNIUMSCRIPTSALADMOTOR,"
1586 "ZUSUALBODYHALIBUTDIAMOND,ZOUTSIDEPEAHENCOUNTICE,ZDIVERPAINTLEATHEREASY,"
1587 "ZWESTAMOUNTAFFECTHEARING,ZSIZETOEAWARDFRESH,ZDEWPEACHCAREERCELERY,"
1588 "ZSTEELCAREFULPLATENUMBER,ZCYANBASEFEEDADROIT,ZCALMLYGEMFINISHEFFECT,"
1589 "ZHANDYREPAIRPROTONAIRPORT,ZGENENATURALHEARINGKITE,ZBROADABLESOLIDCASUAL,"
1590 "ZPOSTPROTEINHANDLEACTOR,ZLACEADDRESSGROUNDCAREFUL,ZIMAGEPENCILOTHERBOTTOM,"
1591 "ZPROBLEMCLUBPOPOVERJELLY,ZPATTERNCLORINEGRANDCOLBY,ZNEATSTEWPARTIRON,"
1592 "ZAPPEALSIMPLESECONDHOUSING,ZMOVEWHOGAMMAINCH,ZTENNISCYCLEBILLOFFICER,"
1593 "ZSHARKJUSTFRUITMOVIE,ZKEYFAILAPRICOTMETAL,ZCOMPANYSUMMERFIBERELF,"
1594 "ZTERMFITTINGHOUSINGCOMMAND,ZRESORTYARDGREENLET,ZCABBAGESOCKEASEMINUTE,"
1595 "ZSQUAREGLEEFULCHILDLIGHT,ZONERELEASEAVERAGENURSE,ZBIGTHINKCONVERTECONOMY,"
1596 "ZPLIERSPRINTASKOREGANO,ZDECADEJOYOUSWAVEHABIT,ZDRYWALLBEYONDBROWNBOWL,"
1597 "ZCLUBRELEASELIZARDADVICE,ZWHALEMATHAVOCADOCOPPER,ZBELLYCRASHITEMLACK,"
1598 "ZLETTUCEBIRDMEETDEBATE,ZCAPABLETRIPDOORALMOND,ZRADIANTWHENTRYCARD,"
1599 "ZCAPYEARLYRIVETBRUSH,ZAGEREEDFROGBASKET,ZSWIMHEAVYMENTIONKIND,"
1600 "ZTRAVELDRIVERCONTESTLILY,ZGLADSPRAYKIDNEYGUPPY,ZBANKBUFFALORECOVERORBIT,"
1601 "ZFINGERDUEPIZZAOPTION,ZCLAMBITARUGULAFAJITA,ZLONGFINLEAVEIMAGEOIL,"
1602 "ZLONGDIETESSAYNATURE,ZJUNIORSHOWPRESSNOVA,ZHOPEFULGATEHOLECHALK,"
1603 "ZDEPOSITPAIRCOLLEGECOMET,ZWEALTHLINENGLEEFULDAY,ZFILLSTOPLAWJOYFUL,"
1604 "ZTUNEGASBUFFALOCAPITAL,ZGRAYSURVEYWIRELOVE,ZCORNERANCHORTAPEDIVER,"
1605 "ZREWARDINSIDEMANGOINTENSE,ZCADETBRIGHTPLANETBANK,ZPLANETFAMILYPUREMEMORY,"
1606 "ZTREATTESTQUILLCHARGE,ZCREAMEVENINGLIPBRANCH,ZSKYSKYCLASSICBRIEF,"
1607 "ZARSENICSAMPLEWAITMUON,ZBROWBALANCEKEYCHOWDER,ZFLYINGDOCTORTABLEMELODY,"
1608 "ZHANGERLITHIUMDINNERMEET,ZNOTICEPEARPOLICYJUICE,ZSHINYASSISTLIVINGCRAB,"
1609 "ZLIFEUSELEAFYBELL,ZFACEINVITETALKGOLD,ZGENERALRESORTSKYOPEN,"
1610 "ZPURECAKEVIVIDNEATLY,ZKIWIVISUALPRIDEAPPLE,ZMESSYSULFURDREAMFESTIVE,"
1611 "ZCHARGECLICKHUMANEHIRE,ZHERRINGJOKEFEATUREHOPEFUL,ZYARDOREGANOVIVIDJEWEL,"
1612 "ZFOOTTAPWORDENTRY,ZWISHHITSKINMOTOR,ZBASEGOUDAREGULARFORGIVE,"
1613 "ZMUFFINDRYERDRAWFORTUNE,ZACTIONRANGEELEGANTNEUTRON,ZTRYFACTKEEPMILK,"
1614 "ZPEACHCOPPERDINNERLAKE,ZFRAMEENTERSIMPLEMOUTH,ZMERRYCRACKTRAINLEADER,"
1615 "ZMEMORYREQUESTSOURCEBIG,ZCARRYFLOORMINNOWDRAGON,ZMINORWAYPAPERCLASSY,"
1616 "ZDILLASKHOKILEMON,ZRESOLVEWRISTWRAPAPPLE,ZASKCONTACTMONITORFUN,"
1617 "ZGIVEVIVIDDIVINEMEANING,ZEIGHTLEADERWORKERMOST,ZMISSREPLYHUMANLIVING,"
1618 "ZXENONFLIGHTPALEAPPLE,ZSORTMISTYQUOTECABBAGE,ZEAGLELINEMINEMAIL,"
1619 "ZFAMILYVISUALOWNERMATTER,ZSPREADMOTORBISCUITBACON,ZDISHKEEPBLESTMONITOR,"
1620 "ZMALLEQUIPTHANKSGLUE,ZGOLDYOUNGINITIALNOSE,ZHUMORSPICESANDKIDNEY)"
1621 "VALUES(?1,?26,?20,?93,?8,?33,?3,?81,?28,?60,?18,?47,?109,?29,?30,?104,?86,"
1622 "?54,?92,?117,?9,?58,?97,?61,?119,?73,?107,?120,?80,?99,?31,?96,?85,?50,?71,"
1623 "?42,?27,?118,?36,?2,?67,?62,?108,?82,?94,?76,?35,?40,?11,?88,?41,?72,?4,"
1624 "?83,?102,?103,?112,?77,?111,?22,?13,?34,?15,?23,?116,?7,?5,?90,?57,?56,"
1625 "?75,?51,?84,?25,?63,?37,?87,?114,?79,?38,?14,?10,?21,?48,?89,?91,?110,"
1626 "?69,?45,?113,?12,?101,?68,?105,?46,?95,?74,?24,?53,?39,?6,?64,?52,?98,"
1627 "?65,?115,?49,?70,?59,?32,?44,?100,?55,?66,?16,?19,?106,?43,?17,?78);"
1628 );
1629 for(i=0; i<n; i++){
1630 x1 = speedtest1_random();
1631 speedtest1_numbername(x1%1000, zNum, sizeof(zNum));
1632 len = (int)strlen(zNum);
1633 sqlite3_bind_int(g.pStmt, 1, i^0xf);
1634 for(j=0; zType[j]; j++){
1635 switch( zType[j] ){
1636 case 'I':
1637 case 'T':
1638 sqlite3_bind_int64(g.pStmt, j+2, x1);
1639 break;
1640 case 'F':
1641 sqlite3_bind_double(g.pStmt, j+2, (double)x1);
1642 break;
1643 case 'V':
1644 case 'B':
1645 sqlite3_bind_text64(g.pStmt, j+2, zNum, len,
1646 SQLITE_STATIC, SQLITE_UTF8);
1647 break;
1648 }
1649 }
1650 speedtest1_run();
1651 }
1652 speedtest1_exec("COMMIT;");
1653 speedtest1_end_test();
1654
1655 n = g.szTest*250;
1656 speedtest1_begin_test(110, "Query %d rows by rowid", n);
1657 speedtest1_prepare(
1658 "SELECT ZCYANBASEFEEDADROIT,ZJUNIORSHOWPRESSNOVA,ZCAUSESALAMITERMCYAN,"
1659 "ZHOPEFULGATEHOLECHALK,ZHUMORSPICESANDKIDNEY,ZSWIMHEAVYMENTIONKIND,"
1660 "ZMOVEWHOGAMMAINCH,ZAPPEALSIMPLESECONDHOUSING,ZHAFNIUMSCRIPTSALADMOTOR,"
1661 "ZNEATSTEWPARTIRON,ZLONGFINLEAVEIMAGEOIL,ZDEWPEACHCAREERCELERY,"
1662 "ZXENONFLIGHTPALEAPPLE,ZCALMRACCOONPROGRAMDEBIT,ZUSUALBODYHALIBUTDIAMOND,"
1663 "ZTRYFACTKEEPMILK,ZWEALTHLINENGLEEFULDAY,ZLONGDIETESSAYNATURE,"
1664 "ZLIFEUSELEAFYBELL,ZTREATPACKFUTURECONVERT,ZMEMORYREQUESTSOURCEBIG,"
1665 "ZYARDOREGANOVIVIDJEWEL,ZDEPOSITPAIRCOLLEGECOMET,ZSLEEPYUSERGRANDBOWL,"
1666 "ZBRIEFGOBYDODGERHEIGHT,ZCLUBRELEASELIZARDADVICE,ZCAPABLETRIPDOORALMOND,"
1667 "ZDRYWALLBEYONDBROWNBOWL,ZASKCONTACTMONITORFUN,ZKIWIVISUALPRIDEAPPLE,"
1668 "ZNOTICEPEARPOLICYJUICE,ZPEACHCOPPERDINNERLAKE,ZSTEELCAREFULPLATENUMBER,"
1669 "ZGLADSPRAYKIDNEYGUPPY,ZCOMPANYSUMMERFIBERELF,ZTENNISCYCLEBILLOFFICER,"
1670 "ZIMAGEPENCILOTHERBOTTOM,ZWESTAMOUNTAFFECTHEARING,ZDIVERPAINTLEATHEREASY,"
1671 "ZSKYSKYCLASSICBRIEF,ZMESSYSULFURDREAMFESTIVE,ZMERRYCRACKTRAINLEADER,"
1672 "ZBROADABLESOLIDCASUAL,ZGLASSRESERVEBARIUMMEAL,ZTUNEGASBUFFALOCAPITAL,"
1673 "ZBANKBUFFALORECOVERORBIT,ZTREATTESTQUILLCHARGE,ZBAMBOOMESSWASABIEVENING,"
1674 "ZREWARDINSIDEMANGOINTENSE,ZEAGLELINEMINEMAIL,ZCALMLYGEMFINISHEFFECT,"
1675 "ZKEYFAILAPRICOTMETAL,ZFINGERDUEPIZZAOPTION,ZCADETBRIGHTPLANETBANK,"
1676 "ZGOLDYOUNGINITIALNOSE,ZMISSREPLYHUMANLIVING,ZEIGHTLEADERWORKERMOST,"
1677 "ZFRAMEENTERSIMPLEMOUTH,ZBIGTHINKCONVERTECONOMY,ZFACEINVITETALKGOLD,"
1678 "ZPOSTPROTEINHANDLEACTOR,ZHERRINGJOKEFEATUREHOPEFUL,ZCABBAGESOCKEASEMINUTE,"
1679 "ZMUFFINDRYERDRAWFORTUNE,ZPROBLEMCLUBPOPOVERJELLY,ZGIVEVIVIDDIVINEMEANING,"
1680 "ZGENENATURALHEARINGKITE,ZGENERALRESORTSKYOPEN,ZLETTUCEBIRDMEETDEBATE,"
1681 "ZBASEGOUDAREGULARFORGIVE,ZCHARGECLICKHUMANEHIRE,ZPLANETFAMILYPUREMEMORY,"
1682 "ZMINORWAYPAPERCLASSY,ZCAPYEARLYRIVETBRUSH,ZSIZETOEAWARDFRESH,"
1683 "ZARSENICSAMPLEWAITMUON,ZSQUAREGLEEFULCHILDLIGHT,ZSHINYASSISTLIVINGCRAB,"
1684 "ZCORNERANCHORTAPEDIVER,ZDECADEJOYOUSWAVEHABIT,ZTRAVELDRIVERCONTESTLILY,"
1685 "ZFLYINGDOCTORTABLEMELODY,ZSHARKJUSTFRUITMOVIE,ZFAMILYVISUALOWNERMATTER,"
1686 "ZFARMERMORNINGMIRRORCONCERN,ZGIFTICEFISHGLUEHAIR,ZOUTSIDEPEAHENCOUNTICE,"
1687 "ZSPREADMOTORBISCUITBACON,ZWISHHITSKINMOTOR,ZHOLIDAYHEADPONYDETAIL,"
1688 "ZWOODPOETRYCOBBLERBENCH,ZAIRFORGIVEHEADFROG,ZBROWBALANCEKEYCHOWDER,"
1689 "ZDISHKEEPBLESTMONITOR,ZCLAMBITARUGULAFAJITA,ZPLIERSPRINTASKOREGANO,"
1690 "ZRADIANTWHENTRYCARD,ZDELAYOUTCOMEHORNAGENCY,ZPURECAKEVIVIDNEATLY,"
1691 "ZPATTERNCLORINEGRANDCOLBY,ZHANDYREPAIRPROTONAIRPORT,ZAGEREEDFROGBASKET,"
1692 "ZSORTMISTYQUOTECABBAGE,ZFOOTTAPWORDENTRY,ZRESOLVEWRISTWRAPAPPLE,"
1693 "ZDILLASKHOKILEMON,ZFILLSTOPLAWJOYFUL,ZACTIONRANGEELEGANTNEUTRON,"
1694 "ZRESORTYARDGREENLET,ZCREAMEVENINGLIPBRANCH,ZWHALEMATHAVOCADOCOPPER,"
1695 "ZGRAYSURVEYWIRELOVE,ZBELLYCRASHITEMLACK,ZHANGERLITHIUMDINNERMEET,"
1696 "ZCARRYFLOORMINNOWDRAGON,ZMALLEQUIPTHANKSGLUE,ZTERMFITTINGHOUSINGCOMMAND,"
1697 "ZONERELEASEAVERAGENURSE,ZLACEADDRESSGROUNDCAREFUL"
1698 " FROM ZLOOKSLIKECOREDATA WHERE ZPK=?1;"
1699 );
1700 for(i=0; i<n; i++){
1701 x1 = speedtest1_random()%nRow;
1702 sqlite3_bind_int(g.pStmt, 1, x1);
1703 speedtest1_run();
1704 }
1705 speedtest1_end_test();
1706 }
1707
1708 /*
1709 */
1710 void testset_trigger(void){
1711 int jj, ii;
1712 char zNum[2000]; /* A number name */
1713
1714 const int NROW = 500*g.szTest;
1715 const int NROW2 = 100*g.szTest;
1716
1717 speedtest1_exec(
1718 "BEGIN;"
1719 "CREATE TABLE t1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1720 "CREATE TABLE t2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1721 "CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1722 "CREATE VIEW v1 AS SELECT rowid, i, t FROM t1;"
1723 "CREATE VIEW v2 AS SELECT rowid, i, t FROM t2;"
1724 "CREATE VIEW v3 AS SELECT rowid, i, t FROM t3;"
1725 );
1726 for(jj=1; jj<=3; jj++){
1727 speedtest1_prepare("INSERT INTO t%d VALUES(NULL,?1,?2)", jj);
1728 for(ii=0; ii<NROW; ii++){
1729 int x1 = speedtest1_random() % NROW;
1730 speedtest1_numbername(x1, zNum, sizeof(zNum));
1731 sqlite3_bind_int(g.pStmt, 1, x1);
1732 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1733 speedtest1_run();
1734 }
1735 }
1736 speedtest1_exec(
1737 "CREATE INDEX i1 ON t1(t);"
1738 "CREATE INDEX i2 ON t2(t);"
1739 "CREATE INDEX i3 ON t3(t);"
1740 "COMMIT;"
1741 );
1742
1743 speedtest1_begin_test(100, "speed4p-join1");
1744 speedtest1_prepare(
1745 "SELECT * FROM t1, t2, t3 WHERE t1.oid = t2.oid AND t2.oid = t3.oid"
1746 );
1747 speedtest1_run();
1748 speedtest1_end_test();
1749
1750 speedtest1_begin_test(110, "speed4p-join2");
1751 speedtest1_prepare(
1752 "SELECT * FROM t1, t2, t3 WHERE t1.t = t2.t AND t2.t = t3.t"
1753 );
1754 speedtest1_run();
1755 speedtest1_end_test();
1756
1757 speedtest1_begin_test(120, "speed4p-view1");
1758 for(jj=1; jj<=3; jj++){
1759 speedtest1_prepare("SELECT * FROM v%d WHERE rowid = ?", jj);
1760 for(ii=0; ii<NROW2; ii+=3){
1761 sqlite3_bind_int(g.pStmt, 1, ii*3);
1762 speedtest1_run();
1763 }
1764 }
1765 speedtest1_end_test();
1766
1767 speedtest1_begin_test(130, "speed4p-table1");
1768 for(jj=1; jj<=3; jj++){
1769 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
1770 for(ii=0; ii<NROW2; ii+=3){
1771 sqlite3_bind_int(g.pStmt, 1, ii*3);
1772 speedtest1_run();
1773 }
1774 }
1775 speedtest1_end_test();
1776
1777 speedtest1_begin_test(140, "speed4p-table1");
1778 for(jj=1; jj<=3; jj++){
1779 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
1780 for(ii=0; ii<NROW2; ii+=3){
1781 sqlite3_bind_int(g.pStmt, 1, ii*3);
1782 speedtest1_run();
1783 }
1784 }
1785 speedtest1_end_test();
1786
1787 speedtest1_begin_test(150, "speed4p-subselect1");
1788 speedtest1_prepare("SELECT "
1789 "(SELECT t FROM t1 WHERE rowid = ?1),"
1790 "(SELECT t FROM t2 WHERE rowid = ?1),"
1791 "(SELECT t FROM t3 WHERE rowid = ?1)"
1792 );
1793 for(jj=0; jj<NROW2; jj++){
1794 sqlite3_bind_int(g.pStmt, 1, jj*3);
1795 speedtest1_run();
1796 }
1797 speedtest1_end_test();
1798
1799 speedtest1_begin_test(160, "speed4p-rowid-update");
1800 speedtest1_exec("BEGIN");
1801 speedtest1_prepare("UPDATE t1 SET i=i+1 WHERE rowid=?1");
1802 for(jj=0; jj<NROW2; jj++){
1803 sqlite3_bind_int(g.pStmt, 1, jj);
1804 speedtest1_run();
1805 }
1806 speedtest1_exec("COMMIT");
1807 speedtest1_end_test();
1808
1809 speedtest1_exec("CREATE TABLE t5(t TEXT PRIMARY KEY, i INTEGER);");
1810 speedtest1_begin_test(170, "speed4p-insert-ignore");
1811 speedtest1_exec("INSERT OR IGNORE INTO t5 SELECT t, i FROM t1");
1812 speedtest1_end_test();
1813
1814 speedtest1_exec(
1815 "CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);"
1816 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1817 "CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN"
1818 " INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);"
1819 "END;"
1820 "CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN"
1821 " INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);"
1822 "END;"
1823 "CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN"
1824 " INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);"
1825 "END;"
1826 "BEGIN;"
1827 );
1828
1829 speedtest1_begin_test(180, "speed4p-trigger1");
1830 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
1831 for(jj=0; jj<NROW2; jj++){
1832 speedtest1_numbername(jj, zNum, sizeof(zNum));
1833 sqlite3_bind_int(g.pStmt, 1, jj);
1834 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1835 speedtest1_run();
1836 }
1837 speedtest1_end_test();
1838
1839 /*
1840 ** Note: Of the queries, only half actually update a row. This property
1841 ** was copied over from speed4p.test, where it was probably introduced
1842 ** inadvertantly.
1843 */
1844 speedtest1_begin_test(190, "speed4p-trigger2");
1845 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
1846 for(jj=1; jj<=NROW2*2; jj+=2){
1847 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
1848 sqlite3_bind_int(g.pStmt, 1, jj*2);
1849 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1850 sqlite3_bind_int(g.pStmt, 3, jj);
1851 speedtest1_run();
1852 }
1853 speedtest1_end_test();
1854
1855 /*
1856 ** Note: Same again.
1857 */
1858 speedtest1_begin_test(200, "speed4p-trigger3");
1859 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
1860 for(jj=1; jj<=NROW2*2; jj+=2){
1861 sqlite3_bind_int(g.pStmt, 1, jj*2);
1862 speedtest1_run();
1863 }
1864 speedtest1_end_test();
1865 speedtest1_exec("COMMIT");
1866
1867 /*
1868 ** The following block contains the same tests as the above block that
1869 ** tests triggers, with one crucial difference: no triggers are defined.
1870 ** So the difference in speed between these tests and the preceding ones
1871 ** is the amount of time taken to compile and execute the trigger programs.
1872 */
1873 speedtest1_exec(
1874 "DROP TABLE t4;"
1875 "DROP TABLE log;"
1876 "VACUUM;"
1877 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1878 "BEGIN;"
1879 );
1880 speedtest1_begin_test(210, "speed4p-notrigger1");
1881 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
1882 for(jj=0; jj<NROW2; jj++){
1883 speedtest1_numbername(jj, zNum, sizeof(zNum));
1884 sqlite3_bind_int(g.pStmt, 1, jj);
1885 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1886 speedtest1_run();
1887 }
1888 speedtest1_end_test();
1889 speedtest1_begin_test(210, "speed4p-notrigger2");
1890 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
1891 for(jj=1; jj<=NROW2*2; jj+=2){
1892 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
1893 sqlite3_bind_int(g.pStmt, 1, jj*2);
1894 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1895 sqlite3_bind_int(g.pStmt, 3, jj);
1896 speedtest1_run();
1897 }
1898 speedtest1_end_test();
1899 speedtest1_begin_test(220, "speed4p-notrigger3");
1900 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
1901 for(jj=1; jj<=NROW2*2; jj+=2){
1902 sqlite3_bind_int(g.pStmt, 1, jj*2);
1903 speedtest1_run();
1904 }
1905 speedtest1_end_test();
1906 speedtest1_exec("COMMIT");
1907 }
1908
1909 /*
1910 ** A testset used for debugging speedtest1 itself.
1911 */
1912 void testset_debug1(void){
1913 unsigned i, n;
1914 unsigned x1, x2;
1915 char zNum[2000]; /* A number name */
1916
1917 n = g.szTest;
1918 for(i=1; i<=n; i++){
1919 x1 = swizzle(i, n);
1920 x2 = swizzle(x1, n);
1921 speedtest1_numbername(x1, zNum, sizeof(zNum));
1922 printf("%5d %5d %5d %s\n", i, x1, x2, zNum);
1923 }
1924 }
1925
1926 #ifdef __linux__
1927 #include <sys/types.h>
1928 #include <unistd.h>
1929
1930 /*
1931 ** Attempt to display I/O stats on Linux using /proc/PID/io
1932 */
1933 static void displayLinuxIoStats(FILE *out){
1934 FILE *in;
1935 char z[200];
1936 sqlite3_snprintf(sizeof(z), z, "/proc/%d/io", getpid());
1937 in = fopen(z, "rb");
1938 if( in==0 ) return;
1939 while( fgets(z, sizeof(z), in)!=0 ){
1940 static const struct {
1941 const char *zPattern;
1942 const char *zDesc;
1943 } aTrans[] = {
1944 { "rchar: ", "Bytes received by read():" },
1945 { "wchar: ", "Bytes sent to write():" },
1946 { "syscr: ", "Read() system calls:" },
1947 { "syscw: ", "Write() system calls:" },
1948 { "read_bytes: ", "Bytes rcvd from storage:" },
1949 { "write_bytes: ", "Bytes sent to storage:" },
1950 { "cancelled_write_bytes: ", "Cancelled write bytes:" },
1951 };
1952 int i;
1953 for(i=0; i<sizeof(aTrans)/sizeof(aTrans[0]); i++){
1954 int n = (int)strlen(aTrans[i].zPattern);
1955 if( strncmp(aTrans[i].zPattern, z, n)==0 ){
1956 fprintf(out, "-- %-28s %s", aTrans[i].zDesc, &z[n]);
1957 break;
1958 }
1959 }
1960 }
1961 fclose(in);
1962 }
1963 #endif
1964
1965 #if SQLITE_VERSION_NUMBER<3006018
1966 # define sqlite3_sourceid(X) "(before 3.6.18)"
1967 #endif
1968
1969 static int xCompileOptions(void *pCtx, int nVal, char **azVal, char **azCol){
1970 printf("-- Compile option: %s\n", azVal[0]);
1971 return SQLITE_OK;
1972 }
1973
1974 int main(int argc, char **argv){
1975 int doAutovac = 0; /* True for --autovacuum */
1976 int cacheSize = 0; /* Desired cache size. 0 means default */
1977 int doExclusive = 0; /* True for --exclusive */
1978 int nHeap = 0, mnHeap = 0; /* Heap size from --heap */
1979 int doIncrvac = 0; /* True for --incrvacuum */
1980 const char *zJMode = 0; /* Journal mode */
1981 const char *zKey = 0; /* Encryption key */
1982 int nLook = -1, szLook = 0; /* --lookaside configuration */
1983 int noSync = 0; /* True for --nosync */
1984 int pageSize = 0; /* Desired page size. 0 means default */
1985 int nPCache = 0, szPCache = 0;/* --pcache configuration */
1986 int doPCache = 0; /* True if --pcache is seen */
1987 int showStats = 0; /* True for --stats */
1988 int nThread = 0; /* --threads value */
1989 int mmapSize = 0; /* How big of a memory map to use */
1990 const char *zTSet = "main"; /* Which --testset torun */
1991 int doTrace = 0; /* True for --trace */
1992 const char *zEncoding = 0; /* --utf16be or --utf16le */
1993 const char *zDbName = 0; /* Name of the test database */
1994
1995 void *pHeap = 0; /* Allocated heap space */
1996 void *pLook = 0; /* Allocated lookaside space */
1997 void *pPCache = 0; /* Allocated storage for pcache */
1998 int iCur, iHi; /* Stats values, current and "highwater" */
1999 int i; /* Loop counter */
2000 int rc; /* API return code */
2001
2002 /* Display the version of SQLite being tested */
2003 printf("-- Speedtest1 for SQLite %s %.50s\n",
2004 sqlite3_libversion(), sqlite3_sourceid());
2005
2006 /* Process command-line arguments */
2007 g.zWR = "";
2008 g.zNN = "";
2009 g.zPK = "UNIQUE";
2010 g.szTest = 100;
2011 g.nRepeat = 1;
2012 for(i=1; i<argc; i++){
2013 const char *z = argv[i];
2014 if( z[0]=='-' ){
2015 do{ z++; }while( z[0]=='-' );
2016 if( strcmp(z,"autovacuum")==0 ){
2017 doAutovac = 1;
2018 }else if( strcmp(z,"cachesize")==0 ){
2019 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2020 i++;
2021 cacheSize = integerValue(argv[i]);
2022 }else if( strcmp(z,"exclusive")==0 ){
2023 doExclusive = 1;
2024 }else if( strcmp(z,"explain")==0 ){
2025 g.bSqlOnly = 1;
2026 g.bExplain = 1;
2027 }else if( strcmp(z,"heap")==0 ){
2028 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
2029 nHeap = integerValue(argv[i+1]);
2030 mnHeap = integerValue(argv[i+2]);
2031 i += 2;
2032 }else if( strcmp(z,"incrvacuum")==0 ){
2033 doIncrvac = 1;
2034 }else if( strcmp(z,"journal")==0 ){
2035 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2036 zJMode = argv[++i];
2037 }else if( strcmp(z,"key")==0 ){
2038 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2039 zKey = argv[++i];
2040 }else if( strcmp(z,"lookaside")==0 ){
2041 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
2042 nLook = integerValue(argv[i+1]);
2043 szLook = integerValue(argv[i+2]);
2044 i += 2;
2045 #if SQLITE_VERSION_NUMBER>=3006000
2046 }else if( strcmp(z,"multithread")==0 ){
2047 sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
2048 }else if( strcmp(z,"nomemstat")==0 ){
2049 sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
2050 #endif
2051 #if SQLITE_VERSION_NUMBER>=3007017
2052 }else if( strcmp(z, "mmap")==0 ){
2053 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2054 mmapSize = integerValue(argv[++i]);
2055 #endif
2056 }else if( strcmp(z,"nosync")==0 ){
2057 noSync = 1;
2058 }else if( strcmp(z,"notnull")==0 ){
2059 g.zNN = "NOT NULL";
2060 }else if( strcmp(z,"pagesize")==0 ){
2061 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2062 pageSize = integerValue(argv[++i]);
2063 }else if( strcmp(z,"pcache")==0 ){
2064 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
2065 nPCache = integerValue(argv[i+1]);
2066 szPCache = integerValue(argv[i+2]);
2067 doPCache = 1;
2068 i += 2;
2069 }else if( strcmp(z,"primarykey")==0 ){
2070 g.zPK = "PRIMARY KEY";
2071 }else if( strcmp(z,"repeat")==0 ){
2072 if( i>=argc-1 ) fatal_error("missing arguments on %s\n", argv[i]);
2073 g.nRepeat = integerValue(argv[i+1]);
2074 i += 1;
2075 }else if( strcmp(z,"reprepare")==0 ){
2076 g.bReprepare = 1;
2077 #if SQLITE_VERSION_NUMBER>=3006000
2078 }else if( strcmp(z,"serialized")==0 ){
2079 sqlite3_config(SQLITE_CONFIG_SERIALIZED);
2080 }else if( strcmp(z,"singlethread")==0 ){
2081 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
2082 #endif
2083 }else if( strcmp(z,"sqlonly")==0 ){
2084 g.bSqlOnly = 1;
2085 }else if( strcmp(z,"shrink-memory")==0 ){
2086 g.bMemShrink = 1;
2087 }else if( strcmp(z,"size")==0 ){
2088 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2089 g.szTest = integerValue(argv[++i]);
2090 }else if( strcmp(z,"stats")==0 ){
2091 showStats = 1;
2092 }else if( strcmp(z,"temp")==0 ){
2093 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2094 i++;
2095 if( argv[i][0]<'0' || argv[i][0]>'9' || argv[i][1]!=0 ){
2096 fatal_error("argument to --temp should be integer between 0 and 9");
2097 }
2098 g.eTemp = argv[i][0] - '0';
2099 }else if( strcmp(z,"testset")==0 ){
2100 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2101 zTSet = argv[++i];
2102 }else if( strcmp(z,"trace")==0 ){
2103 doTrace = 1;
2104 }else if( strcmp(z,"threads")==0 ){
2105 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2106 nThread = integerValue(argv[++i]);
2107 }else if( strcmp(z,"utf16le")==0 ){
2108 zEncoding = "utf16le";
2109 }else if( strcmp(z,"utf16be")==0 ){
2110 zEncoding = "utf16be";
2111 }else if( strcmp(z,"verify")==0 ){
2112 g.bVerify = 1;
2113 }else if( strcmp(z,"without-rowid")==0 ){
2114 g.zWR = "WITHOUT ROWID";
2115 g.zPK = "PRIMARY KEY";
2116 }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
2117 printf(zHelp, argv[0]);
2118 exit(0);
2119 }else{
2120 fatal_error("unknown option: %s\nUse \"%s -?\" for help\n",
2121 argv[i], argv[0]);
2122 }
2123 }else if( zDbName==0 ){
2124 zDbName = argv[i];
2125 }else{
2126 fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n",
2127 argv[i], argv[0]);
2128 }
2129 }
2130 if( zDbName!=0 ) unlink(zDbName);
2131 #if SQLITE_VERSION_NUMBER>=3006001
2132 if( nHeap>0 ){
2133 pHeap = malloc( nHeap );
2134 if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap);
2135 rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap);
2136 if( rc ) fatal_error("heap configuration failed: %d\n", rc);
2137 }
2138 if( doPCache ){
2139 if( nPCache>0 && szPCache>0 ){
2140 pPCache = malloc( nPCache*(sqlite3_int64)szPCache );
2141 if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n",
2142 nPCache*(sqlite3_int64)szPCache);
2143 }
2144 rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache);
2145 if( rc ) fatal_error("pcache configuration failed: %d\n", rc);
2146 }
2147 if( nLook>=0 ){
2148 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0);
2149 }
2150 #endif
2151 sqlite3_initialize();
2152
2153 /* Open the database and the input file */
2154 if( sqlite3_open(zDbName, &g.db) ){
2155 fatal_error("Cannot open database file: %s\n", zDbName);
2156 }
2157 #if SQLITE_VERSION_NUMBER>=3006001
2158 if( nLook>0 && szLook>0 ){
2159 pLook = malloc( nLook*szLook );
2160 rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE, pLook, szLook,nLook);
2161 if( rc ) fatal_error("lookaside configuration failed: %d\n", rc);
2162 }
2163 #endif
2164
2165 /* Set database connection options */
2166 sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc, 0, 0);
2167 #ifndef SQLITE_OMIT_DEPRECATED
2168 if( doTrace ) sqlite3_trace(g.db, traceCallback, 0);
2169 #endif
2170 if( mmapSize>0 ){
2171 speedtest1_exec("PRAGMA mmap_size=%d", mmapSize);
2172 }
2173 speedtest1_exec("PRAGMA threads=%d", nThread);
2174 if( zKey ){
2175 speedtest1_exec("PRAGMA key('%s')", zKey);
2176 }
2177 if( zEncoding ){
2178 speedtest1_exec("PRAGMA encoding=%s", zEncoding);
2179 }
2180 if( doAutovac ){
2181 speedtest1_exec("PRAGMA auto_vacuum=FULL");
2182 }else if( doIncrvac ){
2183 speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL");
2184 }
2185 if( pageSize ){
2186 speedtest1_exec("PRAGMA page_size=%d", pageSize);
2187 }
2188 if( cacheSize ){
2189 speedtest1_exec("PRAGMA cache_size=%d", cacheSize);
2190 }
2191 if( noSync ) speedtest1_exec("PRAGMA synchronous=OFF");
2192 if( doExclusive ){
2193 speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
2194 }
2195 if( zJMode ){
2196 speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
2197 }
2198
2199 if( g.bExplain ) printf(".explain\n.echo on\n");
2200 if( strcmp(zTSet,"main")==0 ){
2201 testset_main();
2202 }else if( strcmp(zTSet,"debug1")==0 ){
2203 testset_debug1();
2204 }else if( strcmp(zTSet,"orm")==0 ){
2205 testset_orm();
2206 }else if( strcmp(zTSet,"cte")==0 ){
2207 testset_cte();
2208 }else if( strcmp(zTSet,"fp")==0 ){
2209 testset_fp();
2210 }else if( strcmp(zTSet,"trigger")==0 ){
2211 testset_trigger();
2212 }else if( strcmp(zTSet,"rtree")==0 ){
2213 #ifdef SQLITE_ENABLE_RTREE
2214 testset_rtree(6, 147);
2215 #else
2216 fatal_error("compile with -DSQLITE_ENABLE_RTREE to enable "
2217 "the R-Tree tests\n");
2218 #endif
2219 }else{
2220 fatal_error("unknown testset: \"%s\"\n"
2221 "Choices: cte debug1 fp main orm rtree trigger\n",
2222 zTSet);
2223 }
2224 speedtest1_final();
2225
2226 if( showStats ){
2227 sqlite3_exec(g.db, "PRAGMA compile_options", xCompileOptions, 0, 0);
2228 }
2229
2230 /* Database connection statistics printed after both prepared statements
2231 ** have been finalized */
2232 #if SQLITE_VERSION_NUMBER>=3007009
2233 if( showStats ){
2234 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0);
2235 printf("-- Lookaside Slots Used: %d (max %d)\n", iCur,iHi);
2236 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0);
2237 printf("-- Successful lookasides: %d\n", iHi);
2238 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0);
2239 printf("-- Lookaside size faults: %d\n", iHi);
2240 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0);
2241 printf("-- Lookaside OOM faults: %d\n", iHi);
2242 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0);
2243 printf("-- Pager Heap Usage: %d bytes\n", iCur);
2244 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1);
2245 printf("-- Page cache hits: %d\n", iCur);
2246 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1);
2247 printf("-- Page cache misses: %d\n", iCur);
2248 #if SQLITE_VERSION_NUMBER>=3007012
2249 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1);
2250 printf("-- Page cache writes: %d\n", iCur);
2251 #endif
2252 sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0);
2253 printf("-- Schema Heap Usage: %d bytes\n", iCur);
2254 sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0);
2255 printf("-- Statement Heap Usage: %d bytes\n", iCur);
2256 }
2257 #endif
2258
2259 sqlite3_close(g.db);
2260
2261 #if SQLITE_VERSION_NUMBER>=3006001
2262 /* Global memory usage statistics printed after the database connection
2263 ** has closed. Memory usage should be zero at this point. */
2264 if( showStats ){
2265 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0);
2266 printf("-- Memory Used (bytes): %d (max %d)\n", iCur,iHi);
2267 #if SQLITE_VERSION_NUMBER>=3007000
2268 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0);
2269 printf("-- Outstanding Allocations: %d (max %d)\n", iCur,iHi);
2270 #endif
2271 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0);
2272 printf("-- Pcache Overflow Bytes: %d (max %d)\n", iCur,iHi);
2273 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0);
2274 printf("-- Largest Allocation: %d bytes\n",iHi);
2275 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0);
2276 printf("-- Largest Pcache Allocation: %d bytes\n",iHi);
2277 }
2278 #endif
2279
2280 #ifdef __linux__
2281 if( showStats ){
2282 displayLinuxIoStats(stdout);
2283 }
2284 #endif
2285
2286 /* Release memory */
2287 free( pLook );
2288 free( pPCache );
2289 free( pHeap );
2290 return 0;
2291 }
SQLCipher is an SQLite extension that provides 256 bit AES encryption of database files.