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