/ Check-in [b5733cae]
Login

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

Overview
Comment:Tweaks to speedtest1.c to better reflect mobile usage based on findings in the "Pocket Data" paper by Kennedy, Ajay, Challen, and Ziarek.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b5733cae3c519482cf6bc642bd63814afb113d35
User & Date: drh 2016-11-08 00:30:11
Context
2016-11-08
16:27
Fix the speedtest1.c test program so that it gives closing "%" wildcards on LIKE searches. check-in: c2f84f63 user: drh tags: trunk
00:30
Tweaks to speedtest1.c to better reflect mobile usage based on findings in the "Pocket Data" paper by Kennedy, Ajay, Challen, and Ziarek. check-in: b5733cae user: drh tags: trunk
2016-11-07
18:18
Fix OOM error reporting in the json1_group_object() SQL function. Remove an unreachable branch from the JSON1 logic. check-in: b5409849 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/speedtest1.c.

525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
...
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
...
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
...
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
...
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
...
826
827
828
829
830
831
832























































833
834
835
836
837
838
839
  speedtest1_end_test();


  n = 25;
  speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
    " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = speedtest1_random()%maxb;
      x2 = speedtest1_random()%10 + sz/5000 + x1;
    }
................................................................................
  speedtest1_end_test();


  n = 10;
  speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
    " WHERE c LIKE ?1; -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = speedtest1_random()%maxb;
      zNum[0] = '%';
      len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
................................................................................
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
    " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = speedtest1_random()%maxb;
      x2 = speedtest1_random()%10 + sz/5000 + x1;
    }
................................................................................
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)) FROM t2\n"
    " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = speedtest1_random()%maxb;
      x2 = speedtest1_random()%10 + sz/5000 + x1;
    }
................................................................................
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
    " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = swizzle(i, maxb);
      len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
    }
................................................................................
    "SELECT sum(a), max(c),\n"
    "       avg((SELECT a FROM t2 WHERE 5+t2.b=t1.b) AND rowid<?1), max(c)\n"
    " FROM t1 WHERE rowid<?1;"
  );
  sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
  speedtest1_run();
  speedtest1_end_test();
























































  speedtest1_begin_test(980, "PRAGMA integrity_check");
  speedtest1_exec("PRAGMA integrity_check");
  speedtest1_end_test();


  speedtest1_begin_test(990, "ANALYZE");







|







 







|







 







|







 







|







 







|







 







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







525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
...
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
...
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
...
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
...
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
...
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
  speedtest1_end_test();


  n = 25;
  speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM t1\n"
    " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = speedtest1_random()%maxb;
      x2 = speedtest1_random()%10 + sz/5000 + x1;
    }
................................................................................
  speedtest1_end_test();


  n = 10;
  speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM t1\n"
    " WHERE c LIKE ?1; -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = speedtest1_random()%maxb;
      zNum[0] = '%';
      len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
................................................................................
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM t1\n"
    " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = speedtest1_random()%maxb;
      x2 = speedtest1_random()%10 + sz/5000 + x1;
    }
................................................................................
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM t2\n"
    " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = speedtest1_random()%maxb;
      x2 = speedtest1_random()%10 + sz/5000 + x1;
    }
................................................................................
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
    "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM t1\n"
    " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
  );
  for(i=1; i<=n; i++){
    if( (i-1)%g.nRepeat==0 ){
      x1 = swizzle(i, maxb);
      len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
    }
................................................................................
    "SELECT sum(a), max(c),\n"
    "       avg((SELECT a FROM t2 WHERE 5+t2.b=t1.b) AND rowid<?1), max(c)\n"
    " FROM t1 WHERE rowid<?1;"
  );
  sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
  speedtest1_run();
  speedtest1_end_test();

  sz = n = g.szTest*700;
  zNum[0] = 0;
  maxb = roundup_allones(sz/3);
  speedtest1_begin_test(400, "%d INSERT OR REPLACE ops on an IPK", n);
  speedtest1_exec("BEGIN");
  speedtest1_exec("CREATE%s TABLE t5(a INTEGER PRIMARY KEY, b %s);",
                  isTemp(9), g.zNN);
  speedtest1_prepare("INSERT OR REPLACE INTO t5 VALUES(?1,?2); --  %d times",n);
  for(i=1; i<=n; i++){
    x1 = swizzle(i,maxb);
    speedtest1_numbername(i, zNum, sizeof(zNum));
    sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
    sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
    speedtest1_run();
  }
  speedtest1_exec("COMMIT");
  speedtest1_end_test();
  speedtest1_begin_test(410, "%d SELECTS on an IPK", n);
  speedtest1_prepare("SELECT b FROM t5 WHERE a=?1; --  %d times",n);
  for(i=1; i<=n; i++){
    x1 = swizzle(i,maxb);
    sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
    speedtest1_run();
  }
  speedtest1_end_test();

  sz = n = g.szTest*700;
  zNum[0] = 0;
  maxb = roundup_allones(sz/3);
  speedtest1_begin_test(500, "%d INSERT OR REPLACE ops on TEXT PK", n);
  speedtest1_exec("BEGIN");
  speedtest1_exec("CREATE%s TABLE t6(a TEXT PRIMARY KEY, b %s)%s;",
                  isTemp(9), g.zNN,
                  sqlite3_libversion_number()>=3008002 ? "WITHOUT ROWID" : "");
  speedtest1_prepare("INSERT OR REPLACE INTO t6 VALUES(?1,?2); --  %d times",n);
  for(i=1; i<=n; i++){
    x1 = swizzle(i,maxb);
    speedtest1_numbername(x1, zNum, sizeof(zNum));
    sqlite3_bind_int(g.pStmt, 2, i);
    sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
    speedtest1_run();
  }
  speedtest1_exec("COMMIT");
  speedtest1_end_test();
  speedtest1_begin_test(510, "%d SELECTS on an IPK", n);
  speedtest1_prepare("SELECT b FROM t6 WHERE a=?1; --  %d times",n);
  for(i=1; i<=n; i++){
    x1 = swizzle(i,maxb);
    speedtest1_numbername(x1, zNum, sizeof(zNum));
    sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
    speedtest1_run();
  }
  speedtest1_end_test();


  speedtest1_begin_test(980, "PRAGMA integrity_check");
  speedtest1_exec("PRAGMA integrity_check");
  speedtest1_end_test();


  speedtest1_begin_test(990, "ANALYZE");