1 /*
2 * Copyright 2014 The Emscripten Authors. All rights reserved.
3 * Emscripten is available under two separate licenses, the MIT license and the
4 * University of Illinois/NCSA Open Source License. Both these licenses can be
5 * found in the LICENSE file.
6 */
7
8 /*
9 ** A program for performance testing.
10 **
11 ** The available command-line options are described below:
12 */
13 static const char zHelp[] =
14 "Usage: %s [--options] DATABASE\n"
15 "Options:\n"
16 " --autovacuum Enable AUTOVACUUM mode\n"
17 " --cachesize N Set the cache size to N\n"
18 " --exclusive Enable locking_mode=EXCLUSIVE\n"
19 " --explain Like --sqlonly but with added EXPLAIN keywords\n"
20 " --heap SZ MIN Memory allocator uses SZ bytes & min allocation MIN\n"
21 " --incrvacuum Enable incremenatal vacuum mode\n"
22 " --journalmode M Set the journal_mode to MODE\n"
23 " --key KEY Set the encryption key to KEY\n"
24 " --lookaside N SZ Configure lookaside for N slots of SZ bytes each\n"
25 " --nosync Set PRAGMA synchronous=OFF\n"
26 " --notnull Add NOT NULL constraints to table columns\n"
27 " --pagesize N Set the page size to N\n"
28 " --pcache N SZ Configure N pages of pagecache each of size SZ bytes\n"
29 " --primarykey Use PRIMARY KEY instead of UNIQUE where appropriate\n"
30 " --reprepare Reprepare each statement upon every invocation\n"
31 " --scratch N SZ Configure scratch memory for N slots of SZ bytes each\n"
32 " --sqlonly No-op. Only show the SQL that would have been run.\n"
33 " --size N Relative test size. Default=100\n"
34 " --stats Show statistics at the end\n"
35 " --testset T Run test-set T\n"
36 " --trace Turn on SQL tracing\n"
37 " --utf16be Set text encoding to UTF-16BE\n"
38 " --utf16le Set text encoding to UTF-16LE\n"
39 " --verify Run additional verification steps.\n"
40 " --without-rowid Use WITHOUT ROWID where appropriate\n"
41 ;
42
43
44 #include "sqlite3.h"
45 #include <assert.h>
46 #include <stdio.h>
47 #include <stdlib.h>
48 #include <stdarg.h>
49 #include <string.h>
50 #include <ctype.h>
51
52 /* All global state is held in this structure */
53 static struct Global {
54 sqlite3 *db; /* The open database connection */
55 sqlite3_stmt *pStmt; /* Current SQL statement */
56 sqlite3_int64 iStart; /* Start-time for the current test */
57 sqlite3_int64 iTotal; /* Total time */
58 int bWithoutRowid; /* True for --without-rowid */
59 int bReprepare; /* True to reprepare the SQL on each rerun */
60 int bSqlOnly; /* True to print the SQL once only */
61 int bExplain; /* Print SQL with EXPLAIN prefix */
62 int bVerify; /* Try to verify that results are correct */
63 int szTest; /* Scale factor for test iterations */
64 const char *zWR; /* Might be WITHOUT ROWID */
65 const char *zNN; /* Might be NOT NULL */
66 const char *zPK; /* Might be UNIQUE or PRIMARY KEY */
67 unsigned int x, y; /* Pseudo-random number generator state */
68 int nResult; /* Size of the current result */
69 char zResult[3000]; /* Text of the current result */
70 } g;
71
72
73 /* Print an error message and exit */
fatal_error(const char * zMsg,...)74 static void fatal_error(const char *zMsg, ...){
75 va_list ap;
76 va_start(ap, zMsg);
77 vfprintf(stderr, zMsg, ap);
78 va_end(ap);
79 exit(1);
80 }
81
82 /*
83 ** Return the value of a hexadecimal digit. Return -1 if the input
84 ** is not a hex digit.
85 */
hexDigitValue(char c)86 static int hexDigitValue(char c){
87 if( c>='0' && c<='9' ) return c - '0';
88 if( c>='a' && c<='f' ) return c - 'a' + 10;
89 if( c>='A' && c<='F' ) return c - 'A' + 10;
90 return -1;
91 }
92
93 /* Provide an alternative to sqlite3_stricmp() in older versions of
94 ** SQLite */
95 #if SQLITE_VERSION_NUMBER<3007011
96 # define sqlite3_stricmp strcmp
97 #endif
98
99 /*
100 ** Interpret zArg as an integer value, possibly with suffixes.
101 */
integerValue(const char * zArg)102 static int integerValue(const char *zArg){
103 sqlite3_int64 v = 0;
104 static const struct { char *zSuffix; int iMult; } aMult[] = {
105 { "KiB", 1024 },
106 { "MiB", 1024*1024 },
107 { "GiB", 1024*1024*1024 },
108 { "KB", 1000 },
109 { "MB", 1000000 },
110 { "GB", 1000000000 },
111 { "K", 1000 },
112 { "M", 1000000 },
113 { "G", 1000000000 },
114 };
115 int i;
116 int isNeg = 0;
117 if( zArg[0]=='-' ){
118 isNeg = 1;
119 zArg++;
120 }else if( zArg[0]=='+' ){
121 zArg++;
122 }
123 if( zArg[0]=='0' && zArg[1]=='x' ){
124 int x;
125 zArg += 2;
126 while( (x = hexDigitValue(zArg[0]))>=0 ){
127 v = (v<<4) + x;
128 zArg++;
129 }
130 }else{
131 while( isdigit(zArg[0]) ){
132 v = v*10 + zArg[0] - '0';
133 zArg++;
134 }
135 }
136 for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){
137 if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){
138 v *= aMult[i].iMult;
139 break;
140 }
141 }
142 if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");
143 return (int)(isNeg? -v : v);
144 }
145
146 /* Return the current wall-clock time, in milliseconds */
speedtest1_timestamp(void)147 sqlite3_int64 speedtest1_timestamp(void){
148 static sqlite3_vfs *clockVfs = 0;
149 sqlite3_int64 t;
150 if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
151 #if SQLITE_VERSION_NUMBER>=3007000
152 if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){
153 clockVfs->xCurrentTimeInt64(clockVfs, &t);
154 }else
155 #endif
156 {
157 double r;
158 clockVfs->xCurrentTime(clockVfs, &r);
159 t = (sqlite3_int64)(r*86400000.0);
160 }
161 return t;
162 }
163
164 /* Return a pseudo-random unsigned integer */
speedtest1_random(void)165 unsigned int speedtest1_random(void){
166 g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001);
167 g.y = g.y*1103515245 + 12345;
168 return g.x ^ g.y;
169 }
170
171 /* Map the value in within the range of 1...limit into another
172 ** number in a way that is chatic and invertable.
173 */
swizzle(unsigned in,unsigned limit)174 unsigned swizzle(unsigned in, unsigned limit){
175 unsigned out = 0;
176 while( limit ){
177 out = (out<<1) | (in&1);
178 in >>= 1;
179 limit >>= 1;
180 }
181 return out;
182 }
183
184 /* Round up a number so that it is a power of two minus one
185 */
roundup_allones(unsigned limit)186 unsigned roundup_allones(unsigned limit){
187 unsigned m = 1;
188 while( m<limit ) m = (m<<1)+1;
189 return m;
190 }
191
192 /* The speedtest1_numbername procedure below converts its argment (an integer)
193 ** into a string which is the English-language name for that number.
194 ** The returned string should be freed with sqlite3_free().
195 **
196 ** Example:
197 **
198 ** speedtest1_numbername(123) -> "one hundred twenty three"
199 */
speedtest1_numbername(unsigned int n,char * zOut,int nOut)200 int speedtest1_numbername(unsigned int n, char *zOut, int nOut){
201 static const char *ones[] = { "zero", "one", "two", "three", "four", "five",
202 "six", "seven", "eight", "nine", "ten", "eleven", "twelve",
203 "thirteen", "fourteen", "fifteen", "sixteen", "seventeen",
204 "eighteen", "nineteen" };
205 static const char *tens[] = { "", "ten", "twenty", "thirty", "forty",
206 "fifty", "sixty", "seventy", "eighty", "ninety" };
207 int i = 0;
208
209 if( n>=1000000000 ){
210 i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i);
211 sqlite3_snprintf(nOut-i, zOut+i, " billion");
212 i += (int)strlen(zOut+i);
213 n = n % 1000000000;
214 }
215 if( n>=1000000 ){
216 if( i && i<nOut-1 ) zOut[i++] = ' ';
217 i += speedtest1_numbername(n/1000000, zOut+i, nOut-i);
218 sqlite3_snprintf(nOut-i, zOut+i, " million");
219 i += (int)strlen(zOut+i);
220 n = n % 1000000;
221 }
222 if( n>=1000 ){
223 if( i && i<nOut-1 ) zOut[i++] = ' ';
224 i += speedtest1_numbername(n/1000, zOut+i, nOut-i);
225 sqlite3_snprintf(nOut-i, zOut+i, " thousand");
226 i += (int)strlen(zOut+i);
227 n = n % 1000;
228 }
229 if( n>=100 ){
230 if( i && i<nOut-1 ) zOut[i++] = ' ';
231 sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]);
232 i += (int)strlen(zOut+i);
233 n = n % 100;
234 }
235 if( n>=20 ){
236 if( i && i<nOut-1 ) zOut[i++] = ' ';
237 sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]);
238 i += (int)strlen(zOut+i);
239 n = n % 10;
240 }
241 if( n>0 ){
242 if( i && i<nOut-1 ) zOut[i++] = ' ';
243 sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]);
244 i += (int)strlen(zOut+i);
245 }
246 if( i==0 ){
247 sqlite3_snprintf(nOut-i, zOut+i, "zero");
248 i += (int)strlen(zOut+i);
249 }
250 return i;
251 }
252
253
254 /* Start a new test case */
255 #define NAMEWIDTH 60
256 static const char zDots[] =
257 ".......................................................................";
speedtest1_begin_test(int iTestNum,const char * zTestName,...)258 void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){
259 int n = (int)strlen(zTestName);
260 char *zName;
261 va_list ap;
262 va_start(ap, zTestName);
263 zName = sqlite3_vmprintf(zTestName, ap);
264 va_end(ap);
265 n = (int)strlen(zName);
266 if( n>NAMEWIDTH ){
267 zName[NAMEWIDTH] = 0;
268 n = NAMEWIDTH;
269 }
270 if( g.bSqlOnly ){
271 printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots);
272 }else{
273 printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);
274 fflush(stdout);
275 }
276 sqlite3_free(zName);
277 g.nResult = 0;
278 g.iStart = speedtest1_timestamp();
279 g.x = 0xad131d0b;
280 g.y = 0x44f9eac8;
281 }
282
283 /* Complete a test case */
speedtest1_end_test(void)284 void speedtest1_end_test(void){
285 sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;
286 if( !g.bSqlOnly ){
287 g.iTotal += iElapseTime;
288 printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000));
289 }
290 if( g.pStmt ){
291 sqlite3_finalize(g.pStmt);
292 g.pStmt = 0;
293 }
294 }
295
296 /* Report end of testing */
speedtest1_final(void)297 void speedtest1_final(void){
298 if( !g.bSqlOnly ){
299 printf(" TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
300 (int)(g.iTotal/1000), (int)(g.iTotal%1000));
301 }
302 }
303
304 /* Print an SQL statement to standard output */
printSql(const char * zSql)305 static void printSql(const char *zSql){
306 int n = (int)strlen(zSql);
307 while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ){ n--; }
308 if( g.bExplain ) printf("EXPLAIN ");
309 printf("%.*s;\n", n, zSql);
310 if( g.bExplain
311 #if SQLITE_VERSION_NUMBER>=3007010
312 && ( sqlite3_strglob("CREATE *", zSql)==0
313 || sqlite3_strglob("DROP *", zSql)==0
314 || sqlite3_strglob("ALTER *", zSql)==0
315 )
316 #endif
317 ){
318 printf("%.*s;\n", n, zSql);
319 }
320 }
321
322 /* Run SQL */
speedtest1_exec(const char * zFormat,...)323 void speedtest1_exec(const char *zFormat, ...){
324 va_list ap;
325 char *zSql;
326 va_start(ap, zFormat);
327 zSql = sqlite3_vmprintf(zFormat, ap);
328 va_end(ap);
329 if( g.bSqlOnly ){
330 printSql(zSql);
331 }else{
332 char *zErrMsg = 0;
333 int rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
334 if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
335 if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
336 }
337 sqlite3_free(zSql);
338 }
339
340 /* Prepare an SQL statement */
speedtest1_prepare(const char * zFormat,...)341 void speedtest1_prepare(const char *zFormat, ...){
342 va_list ap;
343 char *zSql;
344 va_start(ap, zFormat);
345 zSql = sqlite3_vmprintf(zFormat, ap);
346 va_end(ap);
347 if( g.bSqlOnly ){
348 printSql(zSql);
349 }else{
350 int rc;
351 if( g.pStmt ) sqlite3_finalize(g.pStmt);
352 rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
353 if( rc ){
354 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
355 }
356 }
357 sqlite3_free(zSql);
358 }
359
360 /* Run an SQL statement previously prepared */
speedtest1_execute(void)361 void speedtest1_execute(void){
362 int i, n, len;
363 if( g.bSqlOnly ) return;
364 assert( g.pStmt );
365 g.nResult = 0;
366 while( sqlite3_step(g.pStmt)==SQLITE_ROW ){
367 n = sqlite3_column_count(g.pStmt);
368 for(i=0; i<n; i++){
369 const char *z = (const char*)sqlite3_column_text(g.pStmt, i);
370 if( z==0 ) z = "nil";
371 len = (int)strlen(z);
372 if( g.nResult+len<sizeof(g.zResult)-2 ){
373 if( g.nResult>0 ) g.zResult[g.nResult++] = ' ';
374 memcpy(g.zResult + g.nResult, z, len+1);
375 g.nResult += len;
376 }
377 }
378 }
379 if( g.bReprepare ){
380 sqlite3_stmt *pNew;
381 sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0);
382 sqlite3_finalize(g.pStmt);
383 g.pStmt = pNew;
384 }else{
385 sqlite3_reset(g.pStmt);
386 }
387 }
388
389 /* The sqlite3_trace() callback function */
traceCallback(void * NotUsed,const char * zSql)390 static void traceCallback(void *NotUsed, const char *zSql){
391 int n = (int)strlen(zSql);
392 while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ) n--;
393 fprintf(stderr,"%.*s;\n", n, zSql);
394 }
395
396 /* Substitute random() function that gives the same random
397 ** sequence on each run, for repeatability. */
randomFunc1(sqlite3_context * context,int NotUsed,sqlite3_value ** NotUsed2)398 static void randomFunc1(
399 sqlite3_context *context,
400 int NotUsed,
401 sqlite3_value **NotUsed2
402 ){
403 sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random());
404 }
405
406 /* Estimate the square root of an integer */
est_square_root(int x)407 static int est_square_root(int x){
408 int y0 = x/2;
409 int y1;
410 int n;
411 for(n=0; y0>0 && n<10; n++){
412 y1 = (y0 + x/y0)/2;
413 if( y1==y0 ) break;
414 y0 = y1;
415 }
416 return y0;
417 }
418
419 /*
420 ** The main and default testset
421 */
testset_main(void)422 void testset_main(void){
423 int i; /* Loop counter */
424 int n; /* iteration count */
425 int sz; /* Size of the tables */
426 int maxb; /* Maximum swizzled value */
427 unsigned x1, x2; /* Parameters */
428 int len; /* Length of the zNum[] string */
429 char zNum[2000]; /* A number name */
430
431 sz = n = g.szTest*500;
432 maxb = roundup_allones(sz);
433 speedtest1_begin_test(100, "%d INSERTs into table with no index", n);
434 speedtest1_exec("BEGIN");
435 speedtest1_exec("CREATE TABLE t1(a INTEGER %s, b INTEGER %s, c TEXT %s);",
436 g.zNN, g.zNN, g.zNN);
437 speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); -- %d times", n);
438 for(i=1; i<=n; i++){
439 x1 = swizzle(i,maxb);
440 speedtest1_numbername(x1, zNum, sizeof(zNum));
441 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
442 sqlite3_bind_int(g.pStmt, 2, i);
443 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
444 speedtest1_execute();
445 }
446 speedtest1_exec("COMMIT");
447 speedtest1_end_test();
448
449
450 n = sz;
451 speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n);
452 speedtest1_exec("BEGIN");
453 speedtest1_exec("CREATE TABLE t2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
454 g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
455 speedtest1_prepare("INSERT INTO t2 VALUES(?1,?2,?3); -- %d times", n);
456 for(i=1; i<=n; i++){
457 x1 = swizzle(i,maxb);
458 speedtest1_numbername(x1, zNum, sizeof(zNum));
459 sqlite3_bind_int(g.pStmt, 1, i);
460 sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1);
461 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
462 speedtest1_execute();
463 }
464 speedtest1_exec("COMMIT");
465 speedtest1_end_test();
466
467
468 n = sz;
469 speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n);
470 speedtest1_exec("BEGIN");
471 speedtest1_exec("CREATE TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
472 g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
473 speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n);
474 for(i=1; i<=n; i++){
475 x1 = swizzle(i,maxb);
476 speedtest1_numbername(x1, zNum, sizeof(zNum));
477 sqlite3_bind_int(g.pStmt, 2, i);
478 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
479 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
480 speedtest1_execute();
481 }
482 speedtest1_exec("COMMIT");
483 speedtest1_end_test();
484
485
486 n = 25;
487 speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
488 speedtest1_exec("BEGIN");
489 speedtest1_prepare(
490 "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
491 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
492 );
493 for(i=1; i<=n; i++){
494 x1 = speedtest1_random()%maxb;
495 x2 = speedtest1_random()%10 + sz/5000 + x1;
496 sqlite3_bind_int(g.pStmt, 1, x1);
497 sqlite3_bind_int(g.pStmt, 2, x2);
498 speedtest1_execute();
499 }
500 speedtest1_exec("COMMIT");
501 speedtest1_end_test();
502
503
504 n = 10;
505 speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
506 speedtest1_exec("BEGIN");
507 speedtest1_prepare(
508 "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
509 " WHERE c LIKE ?1; -- %d times", n
510 );
511 for(i=1; i<=n; i++){
512 x1 = speedtest1_random()%maxb;
513 zNum[0] = '%';
514 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
515 zNum[len] = '%';
516 zNum[len+1] = 0;
517 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
518 speedtest1_execute();
519 }
520 speedtest1_exec("COMMIT");
521 speedtest1_end_test();
522
523
524 n = 10;
525 speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);
526 speedtest1_exec("BEGIN");
527 speedtest1_prepare(
528 "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n"
529 " ORDER BY a; -- %d times", n
530 );
531 for(i=1; i<=n; i++){
532 x1 = speedtest1_random()%maxb;
533 zNum[0] = '%';
534 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
535 zNum[len] = '%';
536 zNum[len+1] = 0;
537 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
538 speedtest1_execute();
539 }
540 speedtest1_exec("COMMIT");
541 speedtest1_end_test();
542
543 n = 10; //g.szTest/5;
544 speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);
545 speedtest1_exec("BEGIN");
546 speedtest1_prepare(
547 "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n"
548 " ORDER BY a LIMIT 10; -- %d times", n
549 );
550 for(i=1; i<=n; i++){
551 x1 = speedtest1_random()%maxb;
552 zNum[0] = '%';
553 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
554 zNum[len] = '%';
555 zNum[len+1] = 0;
556 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
557 speedtest1_execute();
558 }
559 speedtest1_exec("COMMIT");
560 speedtest1_end_test();
561
562
563 speedtest1_begin_test(150, "CREATE INDEX five times");
564 speedtest1_exec("BEGIN;");
565 speedtest1_exec("CREATE UNIQUE INDEX t1b ON t1(b);");
566 speedtest1_exec("CREATE INDEX t1c ON t1(c);");
567 speedtest1_exec("CREATE UNIQUE INDEX t2b ON t2(b);");
568 speedtest1_exec("CREATE INDEX t2c ON t2(c DESC);");
569 speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
570 speedtest1_exec("COMMIT;");
571 speedtest1_end_test();
572
573
574 n = sz/5;
575 speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
576 speedtest1_exec("BEGIN");
577 speedtest1_prepare(
578 "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
579 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
580 );
581 for(i=1; i<=n; i++){
582 x1 = speedtest1_random()%maxb;
583 x2 = speedtest1_random()%10 + sz/5000 + x1;
584 sqlite3_bind_int(g.pStmt, 1, x1);
585 sqlite3_bind_int(g.pStmt, 2, x2);
586 speedtest1_execute();
587 }
588 speedtest1_exec("COMMIT");
589 speedtest1_end_test();
590
591
592 n = sz/5;
593 speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
594 speedtest1_exec("BEGIN");
595 speedtest1_prepare(
596 "SELECT count(*), avg(b), sum(length(c)) FROM t2\n"
597 " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
598 );
599 for(i=1; i<=n; i++){
600 x1 = speedtest1_random()%maxb;
601 x2 = speedtest1_random()%10 + sz/5000 + x1;
602 sqlite3_bind_int(g.pStmt, 1, x1);
603 sqlite3_bind_int(g.pStmt, 2, x2);
604 speedtest1_execute();
605 }
606 speedtest1_exec("COMMIT");
607 speedtest1_end_test();
608
609
610 n = sz/5;
611 speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
612 speedtest1_exec("BEGIN");
613 speedtest1_prepare(
614 "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
615 " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
616 );
617 for(i=1; i<=n; i++){
618 x1 = swizzle(i, maxb);
619 len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
620 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
621 speedtest1_execute();
622 }
623 speedtest1_exec("COMMIT");
624 speedtest1_end_test();
625
626 n = sz;
627 speedtest1_begin_test(180, "%d INSERTS with three indexes", n);
628 speedtest1_exec("BEGIN");
629 speedtest1_exec(
630 "CREATE TABLE t4(\n"
631 " a INTEGER %s %s,\n"
632 " b INTEGER %s,\n"
633 " c TEXT %s\n"
634 ") %s",
635 g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
636 speedtest1_exec("CREATE INDEX t4b ON t4(b)");
637 speedtest1_exec("CREATE INDEX t4c ON t4(c)");
638 speedtest1_exec("INSERT INTO t4 SELECT * FROM t1");
639 speedtest1_exec("COMMIT");
640 speedtest1_end_test();
641
642 n = sz;
643 speedtest1_begin_test(190, "DELETE and REFILL one table", n);
644 speedtest1_exec("DELETE FROM t2;");
645 speedtest1_exec("INSERT INTO t2 SELECT * FROM t1;");
646 speedtest1_end_test();
647
648 // #include <emscripten.h>
649 speedtest1_begin_test(200, "VACUUM");
650 // EM_ASM( alert('pre') );
651 speedtest1_exec("VACUUM");
652 // EM_ASM( alert('post') );
653 speedtest1_end_test();
654
655
656 speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query");
657 speedtest1_exec("ALTER TABLE t2 ADD COLUMN d DEFAULT 123");
658 speedtest1_exec("SELECT sum(d) FROM t2");
659 speedtest1_end_test();
660
661
662 n = sz/5;
663 speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n);
664 speedtest1_exec("BEGIN");
665 speedtest1_prepare(
666 "UPDATE t2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
667 );
668 for(i=1; i<=n; i++){
669 x1 = speedtest1_random()%maxb;
670 x2 = speedtest1_random()%10 + sz/5000 + x1;
671 sqlite3_bind_int(g.pStmt, 1, x1);
672 sqlite3_bind_int(g.pStmt, 2, x2);
673 speedtest1_execute();
674 }
675 speedtest1_exec("COMMIT");
676 speedtest1_end_test();
677
678
679 n = sz;
680 speedtest1_begin_test(240, "%d UPDATES of individual rows", n);
681 speedtest1_exec("BEGIN");
682 speedtest1_prepare(
683 "UPDATE t2 SET d=b*3 WHERE a=?1; -- %d times", n
684 );
685 for(i=1; i<=n; i++){
686 x1 = speedtest1_random()%sz + 1;
687 sqlite3_bind_int(g.pStmt, 1, x1);
688 speedtest1_execute();
689 }
690 speedtest1_exec("COMMIT");
691 speedtest1_end_test();
692
693 speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz);
694 speedtest1_exec("UPDATE t2 SET d=b*4");
695 speedtest1_end_test();
696
697
698 speedtest1_begin_test(260, "Query added column after filling");
699 speedtest1_exec("SELECT sum(d) FROM t2");
700 speedtest1_end_test();
701
702
703
704 n = sz/5;
705 speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n);
706 speedtest1_exec("BEGIN");
707 speedtest1_prepare(
708 "DELETE FROM t2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
709 );
710 for(i=1; i<=n; i++){
711 x1 = speedtest1_random()%maxb + 1;
712 x2 = speedtest1_random()%10 + sz/5000 + x1;
713 sqlite3_bind_int(g.pStmt, 1, x1);
714 sqlite3_bind_int(g.pStmt, 2, x2);
715 speedtest1_execute();
716 }
717 speedtest1_exec("COMMIT");
718 speedtest1_end_test();
719
720
721 n = sz;
722 speedtest1_begin_test(280, "%d DELETEs of individual rows", n);
723 speedtest1_exec("BEGIN");
724 speedtest1_prepare(
725 "DELETE FROM t3 WHERE a=?1; -- %d times", n
726 );
727 for(i=1; i<=n; i++){
728 x1 = speedtest1_random()%sz + 1;
729 sqlite3_bind_int(g.pStmt, 1, x1);
730 speedtest1_execute();
731 }
732 speedtest1_exec("COMMIT");
733 speedtest1_end_test();
734
735
736 speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
737 speedtest1_exec("REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1");
738 speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1");
739 speedtest1_end_test();
740
741 speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
742 speedtest1_exec("DELETE FROM t2;");
743 speedtest1_exec("INSERT INTO t2(a,b,c)\n"
744 " SELECT a,b,c FROM t1 WHERE (b&1)==(a&1);");
745 speedtest1_exec("INSERT INTO t2(a,b,c)\n"
746 " SELECT a,b,c FROM t1 WHERE (b&1)<>(a&1);");
747 speedtest1_end_test();
748
749
750 n = sz/5;
751 speedtest1_begin_test(310, "%d four-ways joins", n);
752 speedtest1_exec("BEGIN");
753 speedtest1_prepare(
754 "SELECT t1.c FROM t1, t2, t3, t4\n"
755 " WHERE t4.a BETWEEN ?1 AND ?2\n"
756 " AND t3.a=t4.b\n"
757 " AND t2.a=t3.b\n"
758 " AND t1.c=t2.c"
759 );
760 for(i=1; i<=n; i++){
761 x1 = speedtest1_random()%sz + 1;
762 x2 = speedtest1_random()%10 + x1 + 4;
763 sqlite3_bind_int(g.pStmt, 1, x1);
764 sqlite3_bind_int(g.pStmt, 2, x2);
765 speedtest1_execute();
766 }
767 speedtest1_exec("COMMIT");
768 speedtest1_end_test();
769
770 speedtest1_begin_test(320, "subquery in result set", n);
771 speedtest1_prepare(
772 "SELECT sum(a), max(c),\n"
773 " avg((SELECT a FROM t2 WHERE 5+t2.b=t1.b) AND rowid<?1), max(c)\n"
774 " FROM t1 WHERE rowid<?1;"
775 );
776 sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
777 speedtest1_execute();
778 speedtest1_end_test();
779
780 speedtest1_begin_test(980, "PRAGMA integrity_check");
781 speedtest1_exec("PRAGMA integrity_check");
782 speedtest1_end_test();
783
784
785 speedtest1_begin_test(990, "ANALYZE");
786 speedtest1_exec("ANALYZE");
787 speedtest1_end_test();
788 }
789
790 /*
791 ** A testset for common table expressions. This exercises code
792 ** for views, subqueries, co-routines, etc.
793 */
testset_cte(void)794 void testset_cte(void){
795 static const char *azPuzzle[] = {
796 /* Easy */
797 "534...9.."
798 "67.195..."
799 ".98....6."
800 "8...6...3"
801 "4..8.3..1"
802 "....2...6"
803 ".6....28."
804 "...419..5"
805 "...28..79",
806
807 /* Medium */
808 "53....9.."
809 "6..195..."
810 ".98....6."
811 "8...6...3"
812 "4..8.3..1"
813 "....2...6"
814 ".6....28."
815 "...419..5"
816 "....8..79",
817
818 /* Hard */
819 "53......."
820 "6..195..."
821 ".98....6."
822 "8...6...3"
823 "4..8.3..1"
824 "....2...6"
825 ".6....28."
826 "...419..5"
827 "....8..79",
828 };
829 const char *zPuz;
830 double rSpacing;
831 int nElem;
832
833 if( g.szTest<25 ){
834 zPuz = azPuzzle[0];
835 }else if( g.szTest<70 ){
836 zPuz = azPuzzle[1];
837 }else{
838 zPuz = azPuzzle[2];
839 }
840 speedtest1_begin_test(100, "Sudoku with recursive 'digits'");
841 speedtest1_prepare(
842 "WITH RECURSIVE\n"
843 " input(sud) AS (VALUES(?1)),\n"
844 " digits(z,lp) AS (\n"
845 " VALUES('1', 1)\n"
846 " UNION ALL\n"
847 " SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9\n"
848 " ),\n"
849 " x(s, ind) AS (\n"
850 " SELECT sud, instr(sud, '.') FROM input\n"
851 " UNION ALL\n"
852 " SELECT\n"
853 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
854 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
855 " FROM x, digits AS z\n"
856 " WHERE ind>0\n"
857 " AND NOT EXISTS (\n"
858 " SELECT 1\n"
859 " FROM digits AS lp\n"
860 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
861 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
862 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
863 " + ((ind-1)/27) * 27 + lp\n"
864 " + ((lp-1) / 3) * 6, 1)\n"
865 " )\n"
866 " )\n"
867 "SELECT s FROM x WHERE ind=0;"
868 );
869 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
870 speedtest1_execute();
871 speedtest1_end_test();
872
873 speedtest1_begin_test(200, "Sudoku with VALUES 'digits'");
874 speedtest1_prepare(
875 "WITH RECURSIVE\n"
876 " input(sud) AS (VALUES(?1)),\n"
877 " digits(z,lp) AS (VALUES('1',1),('2',2),('3',3),('4',4),('5',5),\n"
878 " ('6',6),('7',7),('8',8),('9',9)),\n"
879 " x(s, ind) AS (\n"
880 " SELECT sud, instr(sud, '.') FROM input\n"
881 " UNION ALL\n"
882 " SELECT\n"
883 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
884 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
885 " FROM x, digits AS z\n"
886 " WHERE ind>0\n"
887 " AND NOT EXISTS (\n"
888 " SELECT 1\n"
889 " FROM digits AS lp\n"
890 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
891 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
892 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
893 " + ((ind-1)/27) * 27 + lp\n"
894 " + ((lp-1) / 3) * 6, 1)\n"
895 " )\n"
896 " )\n"
897 "SELECT s FROM x WHERE ind=0;"
898 );
899 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
900 speedtest1_execute();
901 speedtest1_end_test();
902
903 rSpacing = 5.0/g.szTest;
904 speedtest1_begin_test(300, "Mandelbrot Set with spacing=%f", rSpacing);
905 speedtest1_prepare(
906 "WITH RECURSIVE \n"
907 " xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+?1 FROM xaxis WHERE x<1.2),\n"
908 " yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+?2 FROM yaxis WHERE y<1.0),\n"
909 " m(iter, cx, cy, x, y) AS (\n"
910 " SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis\n"
911 " UNION ALL\n"
912 " SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \n"
913 " WHERE (x*x + y*y) < 4.0 AND iter<28\n"
914 " ),\n"
915 " m2(iter, cx, cy) AS (\n"
916 " SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n"
917 " ),\n"
918 " a(t) AS (\n"
919 " SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n"
920 " FROM m2 GROUP BY cy\n"
921 " )\n"
922 "SELECT group_concat(rtrim(t),x'0a') FROM a;"
923 );
924 sqlite3_bind_double(g.pStmt, 1, rSpacing*.05);
925 sqlite3_bind_double(g.pStmt, 2, rSpacing);
926 speedtest1_execute();
927 speedtest1_end_test();
928
929 nElem = 10000*g.szTest;
930 speedtest1_begin_test(400, "EXCEPT operator on %d-element tables", nElem);
931 speedtest1_prepare(
932 "WITH RECURSIVE \n"
933 " t1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM t1 WHERE x<%d),\n"
934 " t2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM t2 WHERE y<%d)\n"
935 "SELECT count(x), avg(x) FROM (\n"
936 " SELECT x FROM t1 EXCEPT SELECT y FROM t2 ORDER BY 1\n"
937 ");",
938 nElem, nElem
939 );
940 speedtest1_execute();
941 speedtest1_end_test();
942
943 }
944
945 /* Generate two numbers between 1 and mx. The first number is less than
946 ** the second. Usually the numbers are near each other but can sometimes
947 ** be far apart.
948 */
twoCoords(int p1,int p2,unsigned mx,unsigned * pX0,unsigned * pX1)949 static void twoCoords(
950 int p1, int p2, /* Parameters adjusting sizes */
951 unsigned mx, /* Range of 1..mx */
952 unsigned *pX0, unsigned *pX1 /* OUT: write results here */
953 ){
954 unsigned d, x0, x1, span;
955
956 span = mx/100 + 1;
957 if( speedtest1_random()%3==0 ) span *= p1;
958 if( speedtest1_random()%p2==0 ) span = mx/2;
959 d = speedtest1_random()%span + 1;
960 x0 = speedtest1_random()%(mx-d) + 1;
961 x1 = x0 + d;
962 *pX0 = x0;
963 *pX1 = x1;
964 }
965
966 /* The following routine is an R-Tree geometry callback. It returns
967 ** true if the object overlaps a slice on the Y coordinate between the
968 ** two values given as arguments. In other words
969 **
970 ** SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
971 **
972 ** Is the same as saying:
973 **
974 ** SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
975 */
xsliceGeometryCallback(sqlite3_rtree_geometry * p,int nCoord,double * aCoord,int * pRes)976 static int xsliceGeometryCallback(
977 sqlite3_rtree_geometry *p,
978 int nCoord,
979 double *aCoord,
980 int *pRes
981 ){
982 *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
983 return SQLITE_OK;
984 }
985
986 /*
987 ** A testset for the R-Tree virtual table
988 */
testset_rtree(int p1,int p2)989 void testset_rtree(int p1, int p2){
990 unsigned i, n;
991 unsigned mxCoord;
992 unsigned x0, x1, y0, y1, z0, z1;
993 unsigned iStep;
994 int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*100 );
995
996 mxCoord = 15000;
997 n = g.szTest*100;
998 speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
999 speedtest1_exec("BEGIN");
1000 speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
1001 speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
1002 "VALUES(?1,?2,?3,?4,?5,?6,?7)");
1003 for(i=1; i<=n; i++){
1004 twoCoords(p1, p2, mxCoord, &x0, &x1);
1005 twoCoords(p1, p2, mxCoord, &y0, &y1);
1006 twoCoords(p1, p2, mxCoord, &z0, &z1);
1007 sqlite3_bind_int(g.pStmt, 1, i);
1008 sqlite3_bind_int(g.pStmt, 2, x0);
1009 sqlite3_bind_int(g.pStmt, 3, x1);
1010 sqlite3_bind_int(g.pStmt, 4, y0);
1011 sqlite3_bind_int(g.pStmt, 5, y1);
1012 sqlite3_bind_int(g.pStmt, 6, z0);
1013 sqlite3_bind_int(g.pStmt, 7, z1);
1014 speedtest1_execute();
1015 }
1016 speedtest1_exec("COMMIT");
1017 speedtest1_end_test();
1018
1019 speedtest1_begin_test(101, "Copy from rtree to a regular table");
1020 speedtest1_exec("CREATE TABLE t1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
1021 speedtest1_exec("INSERT INTO t1 SELECT * FROM rt1");
1022 speedtest1_end_test();
1023
1024 n = g.szTest*20;
1025 speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
1026 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
1027 iStep = mxCoord/n;
1028 for(i=0; i<n; i++){
1029 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1030 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1031 speedtest1_execute();
1032 aCheck[i] = atoi(g.zResult);
1033 }
1034 speedtest1_end_test();
1035
1036 if( g.bVerify ){
1037 n = g.szTest*20;
1038 speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
1039 speedtest1_prepare("SELECT count(*) FROM t1 WHERE x0>=?1 AND x1<=?2");
1040 iStep = mxCoord/n;
1041 for(i=0; i<n; i++){
1042 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1043 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1044 speedtest1_execute();
1045 if( aCheck[i]!=atoi(g.zResult) ){
1046 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1047 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1048 }
1049 }
1050 speedtest1_end_test();
1051 }
1052
1053 n = g.szTest*20;
1054 speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
1055 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
1056 iStep = mxCoord/n;
1057 for(i=0; i<n; i++){
1058 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1059 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1060 speedtest1_execute();
1061 aCheck[i] = atoi(g.zResult);
1062 }
1063 speedtest1_end_test();
1064
1065 if( g.bVerify ){
1066 n = g.szTest*20;
1067 speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
1068 speedtest1_prepare("SELECT count(*) FROM t1 WHERE y1>=?1 AND y0<=?2");
1069 iStep = mxCoord/n;
1070 for(i=0; i<n; i++){
1071 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1072 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1073 speedtest1_execute();
1074 if( aCheck[i]!=atoi(g.zResult) ){
1075 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1076 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1077 }
1078 }
1079 speedtest1_end_test();
1080 }
1081
1082
1083 n = g.szTest*20;
1084 speedtest1_begin_test(125, "%d custom geometry callback queries", n);
1085 //sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
1086 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
1087 iStep = mxCoord/n;
1088 for(i=0; i<n; i++){
1089 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1090 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1091 speedtest1_execute();
1092 if( aCheck[i]!=atoi(g.zResult) ){
1093 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1094 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1095 }
1096 }
1097 speedtest1_end_test();
1098
1099 n = g.szTest*80;
1100 speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
1101 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
1102 " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
1103 iStep = mxCoord/n;
1104 for(i=0; i<n; i++){
1105 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1106 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1107 speedtest1_execute();
1108 aCheck[i] = atoi(g.zResult);
1109 }
1110 speedtest1_end_test();
1111
1112 n = g.szTest*100;
1113 speedtest1_begin_test(140, "%d rowid queries", n);
1114 speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
1115 for(i=1; i<=n; i++){
1116 sqlite3_bind_int(g.pStmt, 1, i);
1117 speedtest1_execute();
1118 }
1119 speedtest1_end_test();
1120 }
1121
1122 /*
1123 ** A testset used for debugging speedtest1 itself.
1124 */
testset_debug1(void)1125 void testset_debug1(void){
1126 unsigned i, n;
1127 unsigned x1, x2;
1128 char zNum[2000]; /* A number name */
1129
1130 n = g.szTest;
1131 for(i=1; i<=n; i++){
1132 x1 = swizzle(i, n);
1133 x2 = swizzle(x1, n);
1134 speedtest1_numbername(x1, zNum, sizeof(zNum));
1135 printf("%5d %5d %5d %s\n", i, x1, x2, zNum);
1136 }
1137 }
1138
main(int argc,char ** argv)1139 int main(int argc, char **argv){
1140 int doAutovac = 0; /* True for --autovacuum */
1141 int cacheSize = 0; /* Desired cache size. 0 means default */
1142 int doExclusive = 0; /* True for --exclusive */
1143 int nHeap = 0, mnHeap = 0; /* Heap size from --heap */
1144 int doIncrvac = 0; /* True for --incrvacuum */
1145 const char *zJMode = 0; /* Journal mode */
1146 const char *zKey = 0; /* Encryption key */
1147 int nLook = 0, szLook = 0; /* --lookaside configuration */
1148 int noSync = 0; /* True for --nosync */
1149 int pageSize = 0; /* Desired page size. 0 means default */
1150 int nPCache = 0, szPCache = 0;/* --pcache configuration */
1151 int nScratch = 0, szScratch=0;/* --scratch configuration */
1152 int showStats = 0; /* True for --stats */
1153 const char *zTSet = "main"; /* Which --testset torun */
1154 int doTrace = 0; /* True for --trace */
1155 const char *zEncoding = 0; /* --utf16be or --utf16le */
1156 const char *zDbName = 0; /* Name of the test database */
1157
1158 void *pHeap = 0; /* Allocated heap space */
1159 void *pLook = 0; /* Allocated lookaside space */
1160 void *pPCache = 0; /* Allocated storage for pcache */
1161 void *pScratch = 0; /* Allocated storage for scratch */
1162 int iCur, iHi; /* Stats values, current and "highwater" */
1163 int i; /* Loop counter */
1164 int rc; /* API return code */
1165
1166 /* Process command-line arguments */
1167 g.zWR = "";
1168 g.zNN = "";
1169 g.zPK = "UNIQUE";
1170 g.szTest = 100;
1171
1172 /* Emscripten commandline argument processing - first arg is ours */
1173 int arg = argc > 1 ? argv[1][0] - '0' : 3;
1174 switch(arg) {
1175 case 0: return 0; break;
1176 case 1: arg = 5; break;
1177 case 2: arg = 15; break;
1178 case 3: arg = 40; break;
1179 case 4: arg = 80; break;
1180 case 5: arg = 160; break;
1181 default:
1182 printf("error: %d\\n", arg);
1183 return -1;
1184 }
1185 g.szTest = arg;
1186
1187 /* Back to sqlite - after the first is theirs*/
1188 for(i=2; i<argc; i++){
1189 const char *z = argv[i];
1190 if( z[0]=='-' ){
1191 do{ z++; }while( z[0]=='-' );
1192 if( strcmp(z,"autovacuum")==0 ){
1193 doAutovac = 1;
1194 }else if( strcmp(z,"cachesize")==0 ){
1195 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1196 i++;
1197 cacheSize = integerValue(argv[i]);
1198 }else if( strcmp(z,"exclusive")==0 ){
1199 doExclusive = 1;
1200 }else if( strcmp(z,"explain")==0 ){
1201 g.bSqlOnly = 1;
1202 g.bExplain = 1;
1203 }else if( strcmp(z,"heap")==0 ){
1204 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1205 nHeap = integerValue(argv[i+1]);
1206 mnHeap = integerValue(argv[i+2]);
1207 i += 2;
1208 }else if( strcmp(z,"incrvacuum")==0 ){
1209 doIncrvac = 1;
1210 }else if( strcmp(z,"journal")==0 ){
1211 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1212 zJMode = argv[++i];
1213 }else if( strcmp(z,"key")==0 ){
1214 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1215 zKey = argv[++i];
1216 }else if( strcmp(z,"lookaside")==0 ){
1217 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1218 nLook = integerValue(argv[i+1]);
1219 szLook = integerValue(argv[i+2]);
1220 i += 2;
1221 }else if( strcmp(z,"nosync")==0 ){
1222 noSync = 1;
1223 }else if( strcmp(z,"notnull")==0 ){
1224 g.zNN = "NOT NULL";
1225 }else if( strcmp(z,"pagesize")==0 ){
1226 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1227 pageSize = integerValue(argv[++i]);
1228 }else if( strcmp(z,"pcache")==0 ){
1229 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1230 nPCache = integerValue(argv[i+1]);
1231 szPCache = integerValue(argv[i+2]);
1232 i += 2;
1233 }else if( strcmp(z,"primarykey")==0 ){
1234 g.zPK = "PRIMARY KEY";
1235 }else if( strcmp(z,"reprepare")==0 ){
1236 g.bReprepare = 1;
1237 }else if( strcmp(z,"scratch")==0 ){
1238 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1239 nScratch = integerValue(argv[i+1]);
1240 szScratch = integerValue(argv[i+2]);
1241 i += 2;
1242 }else if( strcmp(z,"sqlonly")==0 ){
1243 g.bSqlOnly = 1;
1244 }else if( strcmp(z,"size")==0 ){
1245 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1246 g.szTest = integerValue(argv[++i]);
1247 }else if( strcmp(z,"stats")==0 ){
1248 showStats = 1;
1249 }else if( strcmp(z,"testset")==0 ){
1250 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1251 zTSet = argv[++i];
1252 }else if( strcmp(z,"trace")==0 ){
1253 doTrace = 1;
1254 }else if( strcmp(z,"utf16le")==0 ){
1255 zEncoding = "utf16le";
1256 }else if( strcmp(z,"utf16be")==0 ){
1257 zEncoding = "utf16be";
1258 }else if( strcmp(z,"verify")==0 ){
1259 g.bVerify = 1;
1260 }else if( strcmp(z,"without-rowid")==0 ){
1261 g.zWR = "WITHOUT ROWID";
1262 g.zPK = "PRIMARY KEY";
1263 }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
1264 printf(zHelp, argv[0]);
1265 exit(0);
1266 }else{
1267 fatal_error("unknown option: %s\nUse \"%s -?\" for help\n",
1268 argv[i], argv[0]);
1269 }
1270 }else if( zDbName==0 ){
1271 zDbName = argv[i];
1272 }else{
1273 fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n",
1274 argv[i], argv[0]);
1275 }
1276 }
1277 #if 0
1278 if( zDbName==0 ){
1279 fatal_error(zHelp, argv[0]);
1280 }
1281 #endif
1282 if( nHeap>0 ){
1283 pHeap = malloc( nHeap );
1284 if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap);
1285 rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap);
1286 if( rc ) fatal_error("heap configuration failed: %d\n", rc);
1287 }
1288 if( nPCache>0 && szPCache>0 ){
1289 pPCache = malloc( nPCache*(sqlite3_int64)szPCache );
1290 if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n",
1291 nPCache*(sqlite3_int64)szPCache);
1292 rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache);
1293 if( rc ) fatal_error("pcache configuration failed: %d\n", rc);
1294 }
1295 if( nScratch>0 && szScratch>0 ){
1296 pScratch = malloc( nScratch*(sqlite3_int64)szScratch );
1297 if( pScratch==0 ) fatal_error("cannot allocate %lld-byte scratch\n",
1298 nScratch*(sqlite3_int64)szScratch);
1299 rc = sqlite3_config(SQLITE_CONFIG_SCRATCH, pScratch, szScratch, nScratch);
1300 if( rc ) fatal_error("scratch configuration failed: %d\n", rc);
1301 }
1302 if( nLook>0 ){
1303 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0);
1304 }
1305
1306 /* Open the database and the input file */
1307 if( sqlite3_open(":memory:", &g.db) ){
1308 fatal_error("Cannot open database file: %s\n", zDbName);
1309 }
1310 if( nLook>0 && szLook>0 ){
1311 pLook = malloc( nLook*szLook );
1312 rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE, pLook, szLook,nLook);
1313 if( rc ) fatal_error("lookaside configuration failed: %d\n", rc);
1314 }
1315
1316 /* Set database connection options */
1317 sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc1, 0, 0);
1318 if( doTrace ) sqlite3_trace(g.db, traceCallback, 0);
1319 if( zKey ){
1320 speedtest1_exec("PRAGMA key('%s')", zKey);
1321 }
1322 if( zEncoding ){
1323 speedtest1_exec("PRAGMA encoding=%s", zEncoding);
1324 }
1325 if( doAutovac ){
1326 speedtest1_exec("PRAGMA auto_vacuum=FULL");
1327 }else if( doIncrvac ){
1328 speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL");
1329 }
1330 if( pageSize ){
1331 speedtest1_exec("PRAGMA page_size=%d", pageSize);
1332 }
1333 if( cacheSize ){
1334 speedtest1_exec("PRAGMA cache_size=%d", cacheSize);
1335 }
1336 if( noSync ) speedtest1_exec("PRAGMA synchronous=OFF");
1337 if( doExclusive ){
1338 speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
1339 }
1340 if( zJMode ){
1341 speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
1342 }
1343
1344 if( g.bExplain ) printf(".explain\n.echo on\n");
1345 if( strcmp(zTSet,"main")==0 ){
1346 testset_main();
1347 }else if( strcmp(zTSet,"debug1")==0 ){
1348 testset_debug1();
1349 }else if( strcmp(zTSet,"cte")==0 ){
1350 testset_cte();
1351 }else if( strcmp(zTSet,"rtree")==0 ){
1352 testset_rtree(6, 147);
1353 }else{
1354 fatal_error("unknown testset: \"%s\"\nChoices: main debug1 cte rtree\n",
1355 zTSet);
1356 }
1357 speedtest1_final();
1358
1359 /* Database connection statistics printed after both prepared statements
1360 ** have been finalized */
1361 #if SQLITE_VERSION_NUMBER>=3007009
1362 if( showStats ){
1363 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0);
1364 printf("-- Lookaside Slots Used: %d (max %d)\n", iCur,iHi);
1365 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0);
1366 printf("-- Successful lookasides: %d\n", iHi);
1367 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0);
1368 printf("-- Lookaside size faults: %d\n", iHi);
1369 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0);
1370 printf("-- Lookaside OOM faults: %d\n", iHi);
1371 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0);
1372 printf("-- Pager Heap Usage: %d bytes\n", iCur);
1373 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1);
1374 printf("-- Page cache hits: %d\n", iCur);
1375 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1);
1376 printf("-- Page cache misses: %d\n", iCur);
1377 #if SQLITE_VERSION_NUMBER>=3007012
1378 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1);
1379 printf("-- Page cache writes: %d\n", iCur);
1380 #endif
1381 sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0);
1382 printf("-- Schema Heap Usage: %d bytes\n", iCur);
1383 sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0);
1384 printf("-- Statement Heap Usage: %d bytes\n", iCur);
1385 }
1386 #endif
1387
1388 sqlite3_close(g.db);
1389
1390 /* Global memory usage statistics printed after the database connection
1391 ** has closed. Memory usage should be zero at this point. */
1392 if( showStats ){
1393 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0);
1394 printf("-- Memory Used (bytes): %d (max %d)\n", iCur,iHi);
1395 #if SQLITE_VERSION_NUMBER>=3007000
1396 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0);
1397 printf("-- Outstanding Allocations: %d (max %d)\n", iCur,iHi);
1398 #endif
1399 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0);
1400 printf("-- Pcache Overflow Bytes: %d (max %d)\n", iCur,iHi);
1401 sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHi, 0);
1402 printf("-- Scratch Overflow Bytes: %d (max %d)\n", iCur,iHi);
1403 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0);
1404 printf("-- Largest Allocation: %d bytes\n",iHi);
1405 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0);
1406 printf("-- Largest Pcache Allocation: %d bytes\n",iHi);
1407 sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHi, 0);
1408 printf("-- Largest Scratch Allocation: %d bytes\n", iHi);
1409 }
1410
1411 /* Release memory */
1412 free( pLook );
1413 free( pPCache );
1414 free( pScratch );
1415 free( pHeap );
1416 return 0;
1417 }
1418