/ Check-in [d8ef9f58]
Login
☰Home Timeline Branches

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the wordcount.c test program and add a new script "time-wordcount.sh" for additional performance testing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d8ef9f58643f13dd3d16dcde0d829ae08324f04b
User & Date: drh 2016-08-01 21:17:53
Context
2016-08-02
08:45
Fix harmless compiler warnings in sqlite3_status(). (check-in: 7c38a79c user: drh tags: trunk)
2016-08-01
21:17
Update the wordcount.c test program and add a new script "time-wordcount.sh" for additional performance testing. (check-in: d8ef9f58 user: drh tags: trunk)
17:06
Fix other harmless compiler warnings. (check-in: 90d2c490 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added test/time-wordcount.sh. 







































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

#!/bin/sh
#
# This script runs the wordcount program in different ways and generates
# an output useful for performance comparisons.
#

# Select the source text to be analyzed.
#
if test "x$1" = "x";
then echo "Usage: $0 FILENAME [ARGS...]"; exit 1;
fi

# Do test runs
#
rm -f wcdb1.db
./wordcount --tag A: --timer --summary wcdb1.db $* --insert
rm -f wcdb2.db
./wordcount --tag B: --timer --summary wcdb2.db $* --insert --without-rowid
rm -f wcdb1.db
./wordcount --tag C: --timer --summary wcdb1.db $* --replace
rm -f wcdb2.db
./wordcount --tag D: --timer --summary wcdb2.db $* --replace --without-rowid
rm -f wcdb1.db
./wordcount --tag E: --timer --summary wcdb1.db $* --select
rm -f wcdb2.db
./wordcount --tag F: --timer --summary wcdb2.db $* --select --without-rowid
./wordcount --tag G: --timer --summary wcdb1.db $* --query
./wordcount --tag H: --timer --summary wcdb1.db $* --query --without-rowid
./wordcount --tag I: --timer --summary wcdb1.db $* --delete
./wordcount --tag J: --timer --summary wcdb2.db $* --delete --without-rowid

# Clean up temporary files created.
#
rm -f wcdb1.db wcdb2.db

Changes to test/wordcount.c. 

26
27
28
29
30
31
32

33
34
35
36
37
38
39
..
78
79
80
81
82
83
84



85
86
87
88
89
90
91
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
...
216
217
218
219
220
221
222

223
224
225
226
227
228
229
...
262
263
264
265
266
267
268



269
270
271
272
273
274
275
...
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
...
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546

**     --stats              Show sqlite3_status() results at the end.
**     --pagesize NNN       Use a page size of NNN
**     --cachesize NNN      Use a cache size of NNN
**     --commit NNN         Commit after every NNN operations
**     --nosync             Use PRAGMA synchronous=OFF
**     --journal MMMM       Use PRAGMA journal_mode=MMMM
**     --timer              Time the operation of this program

**
** Modes:
**
** Insert mode means:
**    (1) INSERT OR IGNORE INTO wordcount VALUES($new,1)
**    (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new -- if (1) is a noop
**
................................................................................
#include <string.h>
#include <ctype.h>
#include <stdlib.h>
#include <stdarg.h>
#include "sqlite3.h"
#define ISALPHA(X) isalpha((unsigned char)(X))




/* Return the current wall-clock time */
static sqlite3_int64 realTime(void){
  static sqlite3_vfs *clockVfs = 0;
  sqlite3_int64 t;
  if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
  if( clockVfs->iVersion>=1 && clockVfs->xCurrentTimeInt64!=0 ){
    clockVfs->xCurrentTimeInt64(clockVfs, &t);
................................................................................
  printf("%s;\n", zSql);
}

/* An sqlite3_exec() callback that prints results on standard output,
** each column separated by a single space. */
static int printResult(void *NotUsed, int nArg, char **azArg, char **azNm){
  int i;
  printf("--");
  for(i=0; i<nArg; i++){
    printf(" %s", azArg[i] ? azArg[i] : "(null)");
  }
  printf("\n");
  return 0;
}

................................................................................
  sqlite3_stmt *pInsert = 0;    /* The INSERT statement */
  sqlite3_stmt *pUpdate = 0;    /* The UPDATE statement */
  sqlite3_stmt *pSelect = 0;    /* The SELECT statement */
  sqlite3_stmt *pDelete = 0;    /* The DELETE statement */
  FILE *in;                     /* The open input file */
  int rc;                       /* Return code from an SQLite interface */
  int iCur, iHiwtr;             /* Statistics values, current and "highwater" */

  sqlite3_int64 sumCnt = 0;     /* Sum in QUERY mode */
  sqlite3_int64 startTime;
  char zInput[2000];            /* A single line of input */

  /* Process command-line arguments */
  for(i=1; i<argc; i++){
    const char *z = argv[i];
................................................................................
        i++;
        pageSize = atoi(argv[i]);
      }else if( strcmp(z,"commit")==0 && i<argc-1 ){
        i++;
        commitInterval = atoi(argv[i]);
      }else if( strcmp(z,"journal")==0 && i<argc-1 ){
        zJMode = argv[++i];



      }else{
        fatal_error("unknown option: %s\n", argv[i]);
      }
    }else if( zDbName==0 ){
      zDbName = argv[i];
    }else if( zFileToRead==0 ){
      zFileToRead = argv[i];
................................................................................
  if( zFileToRead ) fclose(in);
  sqlite3_finalize(pInsert);
  sqlite3_finalize(pUpdate);
  sqlite3_finalize(pSelect);
  sqlite3_finalize(pDelete);

  if( iMode==MODE_QUERY ){
    printf("sum of cnt: %lld\n", sumCnt);
    rc = sqlite3_prepare_v2(db,"SELECT sum(cnt*cnt) FROM wordcount", -1,
                            &pSelect, 0);
    if( rc==SQLITE_OK && sqlite3_step(pSelect)==SQLITE_ROW ){
      printf("double-check: %lld\n", sqlite3_column_int64(pSelect, 0));
    }
    sqlite3_finalize(pSelect);
  }


  if( showTimer ){
    sqlite3_int64 elapseTime = realTime() - startTime;
    fprintf(stderr, "%3d.%03d wordcount", (int)(elapseTime/1000),
                                   (int)(elapseTime%1000));
    for(i=1; i<argc; i++) if( i!=showTimer ) fprintf(stderr, " %s", argv[i]);
    fprintf(stderr, "\n");
  }

  if( showSummary ){
    sqlite3_create_function(db, "checksum", -1, SQLITE_UTF8, 0,
                            0, checksumStep, checksumFinalize);
    sqlite3_exec(db, 
      "SELECT 'count(*):  ', count(*) FROM wordcount;\n"
................................................................................
      printResult, 0, 0);
  }

  /* Database connection statistics printed after both prepared statements
  ** have been finalized */
  if( showStats ){
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHiwtr, 0);
    printf("-- Lookaside Slots Used:        %d (max %d)\n", iCur,iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHiwtr, 0);
    printf("-- Successful lookasides:       %d\n", iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHiwtr,0);
    printf("-- Lookaside size faults:       %d\n", iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHiwtr,0);
    printf("-- Lookaside OOM faults:        %d\n", iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHiwtr, 0);
    printf("-- Pager Heap Usage:            %d bytes\n", iCur);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHiwtr, 1);
    printf("-- Page cache hits:             %d\n", iCur);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHiwtr, 1);
    printf("-- Page cache misses:           %d\n", iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHiwtr, 1);
    printf("-- Page cache writes:           %d\n", iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, 0);
    printf("-- Schema Heap Usage:           %d bytes\n", iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, 0);
    printf("-- Statement Heap Usage:        %d bytes\n", iCur); 
  }

  sqlite3_close(db);

  /* Global memory usage statistics printed after the database connection
  ** has closed.  Memory usage should be zero at this point. */
  if( showStats ){
    sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHiwtr, 0);
    printf("-- Memory Used (bytes):         %d (max %d)\n", iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHiwtr, 0);
    printf("-- Outstanding Allocations:     %d (max %d)\n", iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHiwtr, 0);
    printf("-- Pcache Overflow Bytes:       %d (max %d)\n", iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHiwtr, 0);
    printf("-- Scratch Overflow Bytes:      %d (max %d)\n", iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHiwtr, 0);
    printf("-- Largest Allocation:          %d bytes\n",iHiwtr);
    sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHiwtr, 0);
    printf("-- Largest Pcache Allocation:   %d bytes\n",iHiwtr);
    sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHiwtr, 0);
    printf("-- Largest Scratch Allocation:  %d bytes\n", iHiwtr);
  }
  return 0;
}








>







 







>
>
>







 







|







 







>







 







>
>
>







 







|



|







|

|
|







 







|

|

|

|

|

|

|

|

|

|








|

|

|

|

|

|

|




26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
..
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
...
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
...
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
...
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
...
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554

**     --stats              Show sqlite3_status() results at the end.
**     --pagesize NNN       Use a page size of NNN
**     --cachesize NNN      Use a cache size of NNN
**     --commit NNN         Commit after every NNN operations
**     --nosync             Use PRAGMA synchronous=OFF
**     --journal MMMM       Use PRAGMA journal_mode=MMMM
**     --timer              Time the operation of this program
**     --tag NAME           Tag all output using NAME.  Use only stdout.
**
** Modes:
**
** Insert mode means:
**    (1) INSERT OR IGNORE INTO wordcount VALUES($new,1)
**    (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new -- if (1) is a noop
**
................................................................................
#include <string.h>
#include <ctype.h>
#include <stdlib.h>
#include <stdarg.h>
#include "sqlite3.h"
#define ISALPHA(X) isalpha((unsigned char)(X))

/* Output tag */
char *zTag = "--";

/* Return the current wall-clock time */
static sqlite3_int64 realTime(void){
  static sqlite3_vfs *clockVfs = 0;
  sqlite3_int64 t;
  if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
  if( clockVfs->iVersion>=1 && clockVfs->xCurrentTimeInt64!=0 ){
    clockVfs->xCurrentTimeInt64(clockVfs, &t);
................................................................................
  printf("%s;\n", zSql);
}

/* An sqlite3_exec() callback that prints results on standard output,
** each column separated by a single space. */
static int printResult(void *NotUsed, int nArg, char **azArg, char **azNm){
  int i;
  printf("%s", zTag);
  for(i=0; i<nArg; i++){
    printf(" %s", azArg[i] ? azArg[i] : "(null)");
  }
  printf("\n");
  return 0;
}

................................................................................
  sqlite3_stmt *pInsert = 0;    /* The INSERT statement */
  sqlite3_stmt *pUpdate = 0;    /* The UPDATE statement */
  sqlite3_stmt *pSelect = 0;    /* The SELECT statement */
  sqlite3_stmt *pDelete = 0;    /* The DELETE statement */
  FILE *in;                     /* The open input file */
  int rc;                       /* Return code from an SQLite interface */
  int iCur, iHiwtr;             /* Statistics values, current and "highwater" */
  FILE *pTimer = stderr;        /* Output channel for the timer */
  sqlite3_int64 sumCnt = 0;     /* Sum in QUERY mode */
  sqlite3_int64 startTime;
  char zInput[2000];            /* A single line of input */

  /* Process command-line arguments */
  for(i=1; i<argc; i++){
    const char *z = argv[i];
................................................................................
        i++;
        pageSize = atoi(argv[i]);
      }else if( strcmp(z,"commit")==0 && i<argc-1 ){
        i++;
        commitInterval = atoi(argv[i]);
      }else if( strcmp(z,"journal")==0 && i<argc-1 ){
        zJMode = argv[++i];
      }else if( strcmp(z,"tag")==0 && i<argc-1 ){
        zTag = argv[++i];
        pTimer = stdout;
      }else{
        fatal_error("unknown option: %s\n", argv[i]);
      }
    }else if( zDbName==0 ){
      zDbName = argv[i];
    }else if( zFileToRead==0 ){
      zFileToRead = argv[i];
................................................................................
  if( zFileToRead ) fclose(in);
  sqlite3_finalize(pInsert);
  sqlite3_finalize(pUpdate);
  sqlite3_finalize(pSelect);
  sqlite3_finalize(pDelete);

  if( iMode==MODE_QUERY ){
    printf("%s sum of cnt: %lld\n", zTag, sumCnt);
    rc = sqlite3_prepare_v2(db,"SELECT sum(cnt*cnt) FROM wordcount", -1,
                            &pSelect, 0);
    if( rc==SQLITE_OK && sqlite3_step(pSelect)==SQLITE_ROW ){
      printf("%s double-check: %lld\n", zTag, sqlite3_column_int64(pSelect, 0));
    }
    sqlite3_finalize(pSelect);
  }


  if( showTimer ){
    sqlite3_int64 elapseTime = realTime() - startTime;
    fprintf(pTimer, "%3d.%03d wordcount", (int)(elapseTime/1000),
                                   (int)(elapseTime%1000));
    for(i=1; i<argc; i++) if( i!=showTimer ) fprintf(pTimer, " %s", argv[i]);
    fprintf(pTimer, "\n");
  }

  if( showSummary ){
    sqlite3_create_function(db, "checksum", -1, SQLITE_UTF8, 0,
                            0, checksumStep, checksumFinalize);
    sqlite3_exec(db, 
      "SELECT 'count(*):  ', count(*) FROM wordcount;\n"
................................................................................
      printResult, 0, 0);
  }

  /* Database connection statistics printed after both prepared statements
  ** have been finalized */
  if( showStats ){
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHiwtr, 0);
    printf("%s Lookaside Slots Used:        %d (max %d)\n", zTag, iCur,iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHiwtr, 0);
    printf("%s Successful lookasides:       %d\n", zTag, iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHiwtr,0);
    printf("%s Lookaside size faults:       %d\n", zTag, iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHiwtr,0);
    printf("%s Lookaside OOM faults:        %d\n", zTag, iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHiwtr, 0);
    printf("%s Pager Heap Usage:            %d bytes\n", zTag, iCur);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHiwtr, 1);
    printf("%s Page cache hits:             %d\n", zTag, iCur);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHiwtr, 1);
    printf("%s Page cache misses:           %d\n", zTag, iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHiwtr, 1);
    printf("%s Page cache writes:           %d\n", zTag, iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, 0);
    printf("%s Schema Heap Usage:           %d bytes\n", zTag, iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, 0);
    printf("%s Statement Heap Usage:        %d bytes\n", zTag, iCur); 
  }

  sqlite3_close(db);

  /* Global memory usage statistics printed after the database connection
  ** has closed.  Memory usage should be zero at this point. */
  if( showStats ){
    sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHiwtr, 0);
    printf("%s Memory Used (bytes):         %d (max %d)\n", zTag,iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHiwtr, 0);
    printf("%s Outstanding Allocations:     %d (max %d)\n",zTag,iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHiwtr, 0);
    printf("%s Pcache Overflow Bytes:       %d (max %d)\n",zTag,iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHiwtr, 0);
    printf("%s Scratch Overflow Bytes:      %d (max %d)\n",zTag,iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHiwtr, 0);
    printf("%s Largest Allocation:          %d bytes\n",zTag,iHiwtr);
    sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHiwtr, 0);
    printf("%s Largest Pcache Allocation:   %d bytes\n",zTag,iHiwtr);
    sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHiwtr, 0);
    printf("%s Largest Scratch Allocation:  %d bytes\n",zTag,iHiwtr);
  }
  return 0;
}

This page was generated in about 0.005s by Fossil 2.11 [8ccab68fc6] 2020-03-27 22:25:20