/ Check-in [9d485c42]
Login

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

Overview
Comment:Add the --verify option to speedtest1. Add verification test cases to the "rtree" testset and a case that uses a custom geometry callback.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rtree-enhancements
Files: files | file ages | folders
SHA1:9d485c4207a81f32334857d4a608c5c511dd2b83
User & Date: drh 2014-04-11 17:41:08
Context
2014-04-11
23:14
Add the new interfaces to rtree, though they do not yet work. Add the "show_speedtest1_rtree.tcl" script for showing the test data used for the R-Tree tests of speedtest1. Change speedtest1 to generate better R-Tree test data. check-in: 0b702759 user: drh tags: rtree-enhancements
17:41
Add the --verify option to speedtest1. Add verification test cases to the "rtree" testset and a case that uses a custom geometry callback. check-in: 9d485c42 user: drh tags: rtree-enhancements
16:14
Update comments in the R-Tree module in preparation for some big changes. Add an "rtree" performance test to speedtest1. check-in: 20a73ec0 user: drh tags: rtree-enhancements
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/speedtest1.c.

    25     25     "  --sqlonly           No-op.  Only show the SQL that would have been run.\n"
    26     26     "  --size N            Relative test size.  Default=100\n"
    27     27     "  --stats             Show statistics at the end\n"
    28     28     "  --testset T         Run test-set T\n"
    29     29     "  --trace             Turn on SQL tracing\n"
    30     30     "  --utf16be           Set text encoding to UTF-16BE\n"
    31     31     "  --utf16le           Set text encoding to UTF-16LE\n"
           32  +  "  --verify            Run additional verification steps.\n"
    32     33     "  --without-rowid     Use WITHOUT ROWID where appropriate\n"
    33     34   ;
    34     35   
    35     36   
    36     37   #include "sqlite3.h"
    37     38   #include <assert.h>
    38     39   #include <stdio.h>
................................................................................
    47     48     sqlite3_stmt *pStmt;       /* Current SQL statement */
    48     49     sqlite3_int64 iStart;      /* Start-time for the current test */
    49     50     sqlite3_int64 iTotal;      /* Total time */
    50     51     int bWithoutRowid;         /* True for --without-rowid */
    51     52     int bReprepare;            /* True to reprepare the SQL on each rerun */
    52     53     int bSqlOnly;              /* True to print the SQL once only */
    53     54     int bExplain;              /* Print SQL with EXPLAIN prefix */
           55  +  int bVerify;               /* Try to verify that results are correct */
    54     56     int szTest;                /* Scale factor for test iterations */
    55     57     const char *zWR;           /* Might be WITHOUT ROWID */
    56     58     const char *zNN;           /* Might be NOT NULL */
    57     59     const char *zPK;           /* Might be UNIQUE or PRIMARY KEY */
    58     60     unsigned int x, y;         /* Pseudo-random number generator state */
    59     61     int nResult;               /* Size of the current result */
    60     62     char zResult[3000];        /* Text of the current result */
................................................................................
   946    948     if( speedtest1_random()%17==0 ) span = mx - 5;
   947    949     d = speedtest1_random()%span + 1;
   948    950     x0 = speedtest1_random()%(mx-d) + 1;
   949    951     x1 = x0 + d;
   950    952     *pX0 = x0;
   951    953     *pX1 = x1;
   952    954   }
          955  +
          956  +/* The following routine is an R-Tree geometry callback.  It returns
          957  +** true if the object overlaps a slice on the Y coordinate between the
          958  +** two values given as arguments.  In other words
          959  +**
          960  +**     SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
          961  +**
          962  +** Is the same as saying:
          963  +**
          964  +**     SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
          965  +*/
          966  +static int xsliceGeometryCallback(
          967  +  sqlite3_rtree_geometry *p,
          968  +  int nCoord,
          969  +  double *aCoord,
          970  +  int *pRes
          971  +){
          972  +  *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
          973  +  return SQLITE_OK;
          974  +}
   953    975   
   954    976   /*
   955    977   ** A testset for the R-Tree virtual table
   956    978   */
   957    979   void testset_rtree(void){
   958    980     unsigned i, n;
   959    981     unsigned mxCoord;
   960    982     unsigned x0, x1, y0, y1, z0, z1;
          983  +  unsigned iStep;
          984  +  int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*100 );
   961    985   
   962    986     mxCoord = g.szTest*600;
   963    987     n = g.szTest*100;
   964    988     speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
   965    989     speedtest1_exec("BEGIN");
   966    990     speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
   967    991     speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
................................................................................
   978   1002       sqlite3_bind_int(g.pStmt, 6, z0);
   979   1003       sqlite3_bind_int(g.pStmt, 7, z1);
   980   1004       speedtest1_run();
   981   1005     }
   982   1006     speedtest1_exec("COMMIT");
   983   1007     speedtest1_end_test();
   984   1008   
         1009  +  speedtest1_begin_test(101, "Copy from rtree to a regular table");
         1010  +  speedtest1_exec("CREATE TABLE t1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
         1011  +  speedtest1_exec("INSERT INTO t1 SELECT * FROM rt1");
         1012  +  speedtest1_end_test();
         1013  +
   985   1014     n = g.szTest*20;
   986   1015     speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
   987   1016     speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
   988         -  for(i=0; i<mxCoord; i+=(mxCoord/n)){
   989         -    sqlite3_bind_int(g.pStmt, 1, i);
   990         -    sqlite3_bind_int(g.pStmt, 2, i+mxCoord/n);
         1017  +  iStep = mxCoord/n;
         1018  +  for(i=0; i<n; i++){
         1019  +    sqlite3_bind_int(g.pStmt, 1, i*iStep);
         1020  +    sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
   991   1021       speedtest1_run();
         1022  +    aCheck[i] = atoi(g.zResult);
   992   1023     }
   993   1024     speedtest1_end_test();
   994   1025   
         1026  +  if( g.bVerify ){
         1027  +    n = g.szTest*20;
         1028  +    speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
         1029  +    speedtest1_prepare("SELECT count(*) FROM t1 WHERE x0>=?1 AND x1<=?2");
         1030  +    iStep = mxCoord/n;
         1031  +    for(i=0; i<n; i++){
         1032  +      sqlite3_bind_int(g.pStmt, 1, i*iStep);
         1033  +      sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
         1034  +      speedtest1_run();
         1035  +      if( aCheck[i]!=atoi(g.zResult) ){
         1036  +        fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
         1037  +                    i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
         1038  +      }
         1039  +    }
         1040  +    speedtest1_end_test();
         1041  +  }
         1042  +  
   995   1043     n = g.szTest*20;
   996   1044     speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
   997   1045     speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
   998         -  for(i=0; i<mxCoord; i+=(mxCoord/n)){
   999         -    sqlite3_bind_int(g.pStmt, 1, i);
  1000         -    sqlite3_bind_int(g.pStmt, 2, i+mxCoord/n);
         1046  +  iStep = mxCoord/n;
         1047  +  for(i=0; i<n; i++){
         1048  +    sqlite3_bind_int(g.pStmt, 1, i*iStep);
         1049  +    sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
         1050  +    speedtest1_run();
         1051  +    aCheck[i] = atoi(g.zResult);
         1052  +  }
         1053  +  speedtest1_end_test();
         1054  +
         1055  +  if( g.bVerify ){
         1056  +    n = g.szTest*20;
         1057  +    speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
         1058  +    speedtest1_prepare("SELECT count(*) FROM t1 WHERE y1>=?1 AND y0<=?2");
         1059  +    iStep = mxCoord/n;
         1060  +    for(i=0; i<n; i++){
         1061  +      sqlite3_bind_int(g.pStmt, 1, i*iStep);
         1062  +      sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
         1063  +      speedtest1_run();
         1064  +      if( aCheck[i]!=atoi(g.zResult) ){
         1065  +        fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
         1066  +                    i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
         1067  +      }
         1068  +    }
         1069  +    speedtest1_end_test();
         1070  +  }
         1071  +  
         1072  +
         1073  +  n = g.szTest*20;
         1074  +  speedtest1_begin_test(125, "%d custom geometry callback queries", n);
         1075  +  sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
         1076  +  speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
         1077  +  iStep = mxCoord/n;
         1078  +  for(i=0; i<n; i++){
         1079  +    sqlite3_bind_int(g.pStmt, 1, i*iStep);
         1080  +    sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
  1001   1081       speedtest1_run();
         1082  +    if( aCheck[i]!=atoi(g.zResult) ){
         1083  +      fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
         1084  +                  i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
         1085  +    }
  1002   1086     }
  1003   1087     speedtest1_end_test();
  1004   1088   
  1005   1089     n = g.szTest*80;
  1006   1090     speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
  1007   1091     speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
  1008   1092                        " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
  1009         -  for(i=0; i<mxCoord; i+=(mxCoord/n)){
  1010         -    sqlite3_bind_int(g.pStmt, 1, i);
  1011         -    sqlite3_bind_int(g.pStmt, 2, i+mxCoord/n);
         1093  +  iStep = mxCoord/n;
         1094  +  for(i=0; i<n; i++){
         1095  +    sqlite3_bind_int(g.pStmt, 1, i*iStep);
         1096  +    sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
  1012   1097       speedtest1_run();
         1098  +    aCheck[i] = atoi(g.zResult);
  1013   1099     }
  1014   1100     speedtest1_end_test();
  1015   1101   
  1016   1102     n = g.szTest*100;
  1017   1103     speedtest1_begin_test(140, "%d rowid queries", n);
  1018   1104     speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
  1019   1105     for(i=1; i<=n; i++){
................................................................................
  1138   1224           zTSet = argv[++i];
  1139   1225         }else if( strcmp(z,"trace")==0 ){
  1140   1226           doTrace = 1;
  1141   1227         }else if( strcmp(z,"utf16le")==0 ){
  1142   1228           zEncoding = "utf16le";
  1143   1229         }else if( strcmp(z,"utf16be")==0 ){
  1144   1230           zEncoding = "utf16be";
         1231  +      }else if( strcmp(z,"verify")==0 ){
         1232  +        g.bVerify = 1;
  1145   1233         }else if( strcmp(z,"without-rowid")==0 ){
  1146   1234           g.zWR = "WITHOUT ROWID";
  1147   1235           g.zPK = "PRIMARY KEY";
  1148   1236         }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
  1149   1237           printf(zHelp, argv[0]);
  1150   1238           exit(0);
  1151   1239         }else{