/ Check-in [b7a7dae9]
Login

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

Overview
Comment:Optimizations to the processing of integer comparisons. (CVS 420)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:b7a7dae919be0e4c35b1fe8cb24fa7359a4b1200
User & Date: drh 2002-03-06 03:08:26
Context
2002-03-06
22:01
Added the default_cache_size and default_synchronous pragmas. Added additional tests for pragmas. Added a new speedtest script. (CVS 421) check-in: 161c0c5f user: drh tags: trunk
03:08
Optimizations to the processing of integer comparisons. (CVS 420) check-in: b7a7dae9 user: drh tags: trunk
2002-03-05
12:41
Change the pager locking mechanism so that we don't have to write page 1 to the journal and to the database unless it actually changes. (CVS 419) check-in: 480eef1a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
726
727
728
729
730
731
732
733
734





735
736
737
738
739
740
741
...
794
795
796
797
798
799
800



801

802
803
804
805
806
807
808
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.54 2002/03/03 18:59:41 drh Exp $
*/
#include "sqliteInt.h"


/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
      }else if( pExpr->iColumn>=0 ){
        sqliteVdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
      }else{
        sqliteVdbeAddOp(v, OP_Recno, pExpr->iTable, 0);
      }
      break;
    }
    case TK_FLOAT:
    case TK_INTEGER: {





      sqliteVdbeAddOp(v, OP_String, 0, 0);
      assert( pExpr->token.z );
      sqliteVdbeChangeP3(v, -1, pExpr->token.z, pExpr->token.n);
      break;
    }
    case TK_STRING: {
      int addr = sqliteVdbeAddOp(v, OP_String, 0, 0);
................................................................................
    }
    case TK_UMINUS: {
      assert( pExpr->pLeft );
      if( pExpr->pLeft->op==TK_FLOAT || pExpr->pLeft->op==TK_INTEGER ){
        Token *p = &pExpr->pLeft->token;
        char *z = sqliteMalloc( p->n + 2 );
        sprintf(z, "-%.*s", p->n, p->z);



        sqliteVdbeAddOp(v, OP_String, 0, 0);

        sqliteVdbeChangeP3(v, -1, z, p->n+1);
        sqliteFree(z);
        break;
      }
      /* Fall through into TK_NOT */
    }
    case TK_BITNOT:







|







 







<

>
>
>
>
>







 







>
>
>
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
726
727
728
729
730
731
732

733
734
735
736
737
738
739
740
741
742
743
744
745
...
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.55 2002/03/06 03:08:26 drh Exp $
*/
#include "sqliteInt.h"


/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
      }else if( pExpr->iColumn>=0 ){
        sqliteVdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
      }else{
        sqliteVdbeAddOp(v, OP_Recno, pExpr->iTable, 0);
      }
      break;
    }

    case TK_INTEGER: {
      sqliteVdbeAddOp(v, OP_Integer, atoi(pExpr->token.z), 0);
      sqliteVdbeChangeP3(v, -1, pExpr->token.z, pExpr->token.n);
      break;
    }
    case TK_FLOAT: {
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      assert( pExpr->token.z );
      sqliteVdbeChangeP3(v, -1, pExpr->token.z, pExpr->token.n);
      break;
    }
    case TK_STRING: {
      int addr = sqliteVdbeAddOp(v, OP_String, 0, 0);
................................................................................
    }
    case TK_UMINUS: {
      assert( pExpr->pLeft );
      if( pExpr->pLeft->op==TK_FLOAT || pExpr->pLeft->op==TK_INTEGER ){
        Token *p = &pExpr->pLeft->token;
        char *z = sqliteMalloc( p->n + 2 );
        sprintf(z, "-%.*s", p->n, p->z);
        if( pExpr->pLeft->op==TK_INTEGER ){
          sqliteVdbeAddOp(v, OP_Integer, atoi(z), 0);
        }else{
          sqliteVdbeAddOp(v, OP_String, 0, 0);
        }
        sqliteVdbeChangeP3(v, -1, z, p->n+1);
        sqliteFree(z);
        break;
      }
      /* Fall through into TK_NOT */
    }
    case TK_BITNOT:

Changes to src/util.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
...
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
...
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.40 2002/02/28 04:10:30 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
................................................................................

/*
** Return TRUE if z is a pure numeric string.  Return FALSE if the
** string contains any character which is not part of a number.
**
** Am empty string is considered numeric.
*/
static int isNum(const char *z){
  if( *z=='-' || *z=='+' ) z++;
  if( !isdigit(*z) ){
    return *z==0;
  }
  z++;
  while( isdigit(*z) ){ z++; }
  if( *z=='.' ){
................................................................................
  int result;
  int isNumA, isNumB;
  if( atext==0 ){
    return -(btext!=0);
  }else if( btext==0 ){
    return 1;
  }
  isNumA = isNum(atext);
  isNumB = isNum(btext);
  if( isNumA ){
    if( !isNumB ){
      result = -1;
    }else{
      double rA, rB;
      rA = atof(atext);
      rB = atof(btext);
................................................................................
*/
int sqliteSortCompare(const char *a, const char *b){
  int len;
  int res = 0;
  int isNumA, isNumB;

  while( res==0 && *a && *b ){
    isNumA = isNum(&a[1]);
    isNumB = isNum(&b[1]);
    if( isNumA ){
      double rA, rB;
      if( !isNumB ){
        res = -1;
        break;
      }
      rA = atof(&a[1]);







|







 







|







 







|
|







 







|
|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
...
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
...
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.41 2002/03/06 03:08:26 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
................................................................................

/*
** Return TRUE if z is a pure numeric string.  Return FALSE if the
** string contains any character which is not part of a number.
**
** Am empty string is considered numeric.
*/
static int sqliteIsNumber(const char *z){
  if( *z=='-' || *z=='+' ) z++;
  if( !isdigit(*z) ){
    return *z==0;
  }
  z++;
  while( isdigit(*z) ){ z++; }
  if( *z=='.' ){
................................................................................
  int result;
  int isNumA, isNumB;
  if( atext==0 ){
    return -(btext!=0);
  }else if( btext==0 ){
    return 1;
  }
  isNumA = sqliteIsNumber(atext);
  isNumB = sqliteIsNumber(btext);
  if( isNumA ){
    if( !isNumB ){
      result = -1;
    }else{
      double rA, rB;
      rA = atof(atext);
      rB = atof(btext);
................................................................................
*/
int sqliteSortCompare(const char *a, const char *b){
  int len;
  int res = 0;
  int isNumA, isNumB;

  while( res==0 && *a && *b ){
    isNumA = sqliteIsNumber(&a[1]);
    isNumB = sqliteIsNumber(&b[1]);
    if( isNumA ){
      double rA, rB;
      if( !isNumB ){
        res = -1;
        break;
      }
      rA = atof(&a[1]);

Changes to src/vdbe.c.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365

1366
1367
1368
1369
1370
1371





1372
1373
1374
1375
1376
1377
1378
....
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
....
1967
1968
1969
1970
1971
1972
1973
1974






1975
1976
1977
1978
1979
1980
1981
....
4616
4617
4618
4619
4620
4621
4622


4623
4624
4625
4626
4627
4628
4629
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.131 2002/03/05 01:11:14 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
    goto abort_due_to_error;
  }else{
    pc = p->nOp-1;
  }
  break;
}

/* Opcode: Integer P1 * *
**
** The integer value P1 is pushed onto the stack.

*/
case OP_Integer: {
  int i = ++p->tos;
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  aStack[i].i = pOp->p1;
  aStack[i].flags = STK_Int;





  break;
}

/* Opcode: String * * P3
**
** The string value P3 is pushed onto the stack.  If P3==0 then a
** NULL is pushed onto the stack.
................................................................................
  int j = ++p->tos;
  VERIFY( if( i<0 ) goto not_enough_stack; )
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  memcpy(&aStack[j], &aStack[i], sizeof(aStack[i])-NBFS);
  if( aStack[j].flags & STK_Str ){
    if( pOp->p2 || (aStack[j].flags & STK_Static)!=0 ){
      zStack[j] = zStack[i];
      aStack[j].flags = STK_Str | STK_Static;
    }else if( aStack[i].n<=NBFS ){
      memcpy(aStack[j].z, zStack[i], aStack[j].n);
      zStack[j] = aStack[j].z;
      aStack[j].flags = STK_Str;
    }else{
      zStack[j] = sqliteMalloc( aStack[j].n );
      if( zStack[j]==0 ) goto no_mem;
      memcpy(zStack[j], zStack[i], aStack[j].n);
      aStack[j].flags = STK_Str | STK_Dyn;
    }
  }
  break;
}

/* Opcode: Pull P1 * *
**
................................................................................
  int tos = p->tos;
  int nos = tos - 1;
  int c;
  int ft, fn;
  VERIFY( if( nos<0 ) goto not_enough_stack; )
  ft = aStack[tos].flags;
  fn = aStack[nos].flags;
  if( (ft & fn)==STK_Int ){






    c = aStack[nos].i - aStack[tos].i;
  }else{
    if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem;
    c = sqliteCompare(zStack[nos], zStack[tos]);
  }
  switch( pOp->opcode ){
    case OP_Eq:    c = c==0;     break;
................................................................................
    }
    if( p->trace && p->tos>=0 ){
      int i;
      fprintf(p->trace, "Stack:");
      for(i=p->tos; i>=0 && i>p->tos-5; i--){
        if( aStack[i].flags & STK_Null ){
          fprintf(p->trace, " NULL");


        }else if( aStack[i].flags & STK_Int ){
          fprintf(p->trace, " i:%d", aStack[i].i);
        }else if( aStack[i].flags & STK_Real ){
          fprintf(p->trace, " r:%g", aStack[i].r);
        }else if( aStack[i].flags & STK_Str ){
          int j, k;
          char zBuf[100];







|







 







|

|
>






>
>
>
>
>







 







|



|




|







 







|
>
>
>
>
>
>







 







>
>







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
....
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
....
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
....
4628
4629
4630
4631
4632
4633
4634
4635
4636
4637
4638
4639
4640
4641
4642
4643
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.132 2002/03/06 03:08:26 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
    goto abort_due_to_error;
  }else{
    pc = p->nOp-1;
  }
  break;
}

/* Opcode: Integer P1 * P3
**
** The integer value P1 is pushed onto the stack.  If P3 is not zero
** then it is assumed to be a string representation of the same integer.
*/
case OP_Integer: {
  int i = ++p->tos;
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  aStack[i].i = pOp->p1;
  aStack[i].flags = STK_Int;
  if( pOp->p3 ){
    zStack[i] = pOp->p3;
    aStack[i].flags |= STK_Str | STK_Static;
    aStack[i].n = strlen(pOp->p3)+1;
  }
  break;
}

/* Opcode: String * * P3
**
** The string value P3 is pushed onto the stack.  If P3==0 then a
** NULL is pushed onto the stack.
................................................................................
  int j = ++p->tos;
  VERIFY( if( i<0 ) goto not_enough_stack; )
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  memcpy(&aStack[j], &aStack[i], sizeof(aStack[i])-NBFS);
  if( aStack[j].flags & STK_Str ){
    if( pOp->p2 || (aStack[j].flags & STK_Static)!=0 ){
      zStack[j] = zStack[i];
      aStack[j].flags &= ~STK_Dyn;
    }else if( aStack[i].n<=NBFS ){
      memcpy(aStack[j].z, zStack[i], aStack[j].n);
      zStack[j] = aStack[j].z;
      aStack[j].flags &= ~(STK_Static|STK_Dyn);
    }else{
      zStack[j] = sqliteMalloc( aStack[j].n );
      if( zStack[j]==0 ) goto no_mem;
      memcpy(zStack[j], zStack[i], aStack[j].n);
      aStack[j].flags &= ~STK_Static;
    }
  }
  break;
}

/* Opcode: Pull P1 * *
**
................................................................................
  int tos = p->tos;
  int nos = tos - 1;
  int c;
  int ft, fn;
  VERIFY( if( nos<0 ) goto not_enough_stack; )
  ft = aStack[tos].flags;
  fn = aStack[nos].flags;
  if( (ft & fn & STK_Int)==STK_Int ){
    c = aStack[nos].i - aStack[tos].i;
  }else if( (ft & STK_Int)!=0 && (fn & STK_Str)!=0 && isInteger(zStack[nos]) ){
    Integerify(p, nos);
    c = aStack[nos].i - aStack[tos].i;
  }else if( (fn & STK_Int)!=0 && (ft & STK_Str)!=0 && isInteger(zStack[tos]) ){
    Integerify(p, tos);
    c = aStack[nos].i - aStack[tos].i;
  }else{
    if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem;
    c = sqliteCompare(zStack[nos], zStack[tos]);
  }
  switch( pOp->opcode ){
    case OP_Eq:    c = c==0;     break;
................................................................................
    }
    if( p->trace && p->tos>=0 ){
      int i;
      fprintf(p->trace, "Stack:");
      for(i=p->tos; i>=0 && i>p->tos-5; i--){
        if( aStack[i].flags & STK_Null ){
          fprintf(p->trace, " NULL");
        }else if( (aStack[i].flags & (STK_Int|STK_Str))==(STK_Int|STK_Str) ){
          fprintf(p->trace, " si:%d", aStack[i].i);
        }else if( aStack[i].flags & STK_Int ){
          fprintf(p->trace, " i:%d", aStack[i].i);
        }else if( aStack[i].flags & STK_Real ){
          fprintf(p->trace, " r:%g", aStack[i].r);
        }else if( aStack[i].flags & STK_Str ){
          int j, k;
          char zBuf[100];

Changes to tool/speedtest.tcl.

33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
...
153
154
155
156
157
158
159

160
161
162
163
164
165
166
  set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.3f</td></tr>}
  set delay 1000
  exec sync; after $delay;
  set t [time "exec psql drh <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format PostgreSQL: $t]
  exec sync; after $delay;
  set t [time "exec mysql drh <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format MySQL: $t]
#  set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
#  set t [expr {[lindex $t 0]/1000000.0}]
#  puts [format $format {SQLite 2.3.2:} $t]
#  set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
#  set t [expr {[lindex $t 0]/1000000.0}]
#  puts [format $format {SQLite 2.4 (cache=100):} $t]
  exec sync; after $delay;
................................................................................
runtest {100 SELECTs on a string comparison}



set fd [open test$cnt.sql w]
puts $fd {CREATE INDEX i2a ON t2(a);}
puts $fd {CREATE INDEX i2b ON t2(b);}

close $fd
runtest {Creating an index}



set fd [open test$cnt.sql w]
for {set i 0} {$i<5000} {incr i} {







|
|
|







 







>







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
...
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
  set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.3f</td></tr>}
  set delay 1000
  exec sync; after $delay;
  set t [time "exec psql drh <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format PostgreSQL: $t]
  exec sync; after $delay;
#  set t [time "exec mysql -f drh <$sqlfile" 1]
#  set t [expr {[lindex $t 0]/1000000.0}]
#  puts [format $format MySQL: $t]
#  set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
#  set t [expr {[lindex $t 0]/1000000.0}]
#  puts [format $format {SQLite 2.3.2:} $t]
#  set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
#  set t [expr {[lindex $t 0]/1000000.0}]
#  puts [format $format {SQLite 2.4 (cache=100):} $t]
  exec sync; after $delay;
................................................................................
runtest {100 SELECTs on a string comparison}



set fd [open test$cnt.sql w]
puts $fd {CREATE INDEX i2a ON t2(a);}
puts $fd {CREATE INDEX i2b ON t2(b);}
puts $fd {VACUUM;}
close $fd
runtest {Creating an index}



set fd [open test$cnt.sql w]
for {set i 0} {$i<5000} {incr i} {