/ Check-in [53fff7d1]
Login

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

Overview
Comment:Improvements to the XFER Optimization of the INSERT statement. (CVS 3736)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:53fff7d1f2f829010a2641c7738538b3155da4b2
User & Date: drh 2007-03-28 18:04:10
Context
2007-03-29
00:08
Patch so the the code compiles with -DSQLITE_OMIT_CHECK=1. (CVS 3737) check-in: 72cea358 user: drh tags: trunk
2007-03-28
18:04
Improvements to the XFER Optimization of the INSERT statement. (CVS 3736) check-in: 53fff7d1 user: drh tags: trunk
14:30
Correctly handle NULLs in IN operators. Ticket #2273. The changes in where.c and in the WhereLevel.aInLoop structure are not strictly necessary to fix this problem - they just make the code easier to read. Only the change in OP_Next/OP_Prev operator of vdbe.c is required. (CVS 3735) check-in: 26348556 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.177 2007/03/27 12:04:05 drh Exp $
           15  +** $Id: insert.c,v 1.178 2007/03/28 18:04:10 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Set P3 of the most recently inserted opcode to a column affinity
    21     21   ** string for index pIdx. A column affinity string has one character
    22     22   ** for each column in the table, according to the affinity of the column:
................................................................................
  1494   1494       emptyDestTest = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
  1495   1495       sqlite3VdbeJumpHere(v, addr1);
  1496   1496     }else{
  1497   1497       emptyDestTest = 0;
  1498   1498     }
  1499   1499     sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
  1500   1500     emptySrcTest = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0);
  1501         -  memRowid = pParse->nMem++;
  1502         -  sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
  1503         -  sqlite3VdbeAddOp(v, OP_MemStore, memRowid, 1);
  1504         -  addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
  1505         -  sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
  1506         -  addr2 = sqlite3VdbeAddOp(v, OP_NotExists, iDest, 0);
  1507         -  sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, 
  1508         -                    "PRIMARY KEY must be unique", P3_STATIC);
  1509         -  sqlite3VdbeJumpHere(v, addr2);
  1510         -  autoIncStep(pParse, counterMem);
         1501  +  if( pDest->iPKey>=0 ){
         1502  +    memRowid = pParse->nMem++;
         1503  +    sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
         1504  +    sqlite3VdbeAddOp(v, OP_MemStore, memRowid, 1);
         1505  +    addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
         1506  +    sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
         1507  +    addr2 = sqlite3VdbeAddOp(v, OP_NotExists, iDest, 0);
         1508  +    sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, 
         1509  +                      "PRIMARY KEY must be unique", P3_STATIC);
         1510  +    sqlite3VdbeJumpHere(v, addr2);
         1511  +    autoIncStep(pParse, counterMem);
         1512  +  }else{
         1513  +    addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
         1514  +  }
  1511   1515     sqlite3VdbeAddOp(v, OP_RowData, iSrc, 0);
  1512   1516     sqlite3VdbeOp3(v, OP_Insert, iDest, OPFLAG_NCHANGE|OPFLAG_LASTROWID,
  1513   1517                       pDest->zName, 0);
  1514   1518     sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1);
  1515   1519     autoIncEnd(pParse, iDbDest, pDest, counterMem);
  1516   1520     for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
  1517   1521       for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){

Changes to test/speed2.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #*************************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this script is measuring executing speed.
    13     13   #
    14         -# $Id: speed2.test,v 1.3 2007/03/27 12:04:06 drh Exp $
           14  +# $Id: speed2.test,v 1.4 2007/03/28 18:04:10 drh Exp $
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Set a uniform random seed
    21     21   expr srand(0)
................................................................................
   107    107   #
   108    108   set sql {}
   109    109   for {set i 0} {$i<50} {incr i} {
   110    110     set lwr [expr {$i*100}]
   111    111     set upr [expr {($i+10)*100}]
   112    112     append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
   113    113   }
   114         -speed_trial speed2-select1 [expr {50*50000}] row $sql
          114  +speed_trial speed2-select1a [expr {50*50000}] row $sql
          115  +
          116  +# 50 SELECTs on an LIKE comparison.  There is no index so a full
          117  +# table scan is required.
          118  +#
          119  +set sql {}
          120  +for {set i 0} {$i<50} {incr i} {
          121  +  append sql \
          122  +    "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"
          123  +}
          124  +speed_trial speed2-select2a [expr {50*50000}] row $sql
          125  +
          126  +# Vacuum
          127  +speed_trial speed2-vacuum1 100000 row VACUUM
          128  +
          129  +# 50 SELECTs on an integer comparison.  There is no index so
          130  +# a full table scan is required.
          131  +#
          132  +set sql {}
          133  +for {set i 0} {$i<50} {incr i} {
          134  +  set lwr [expr {$i*100}]
          135  +  set upr [expr {($i+10)*100}]
          136  +  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
          137  +}
          138  +speed_trial speed2-select1b [expr {50*50000}] row $sql
   115    139   
   116    140   # 50 SELECTs on an LIKE comparison.  There is no index so a full
   117    141   # table scan is required.
   118    142   #
   119    143   set sql {}
   120    144   for {set i 0} {$i<50} {incr i} {
   121    145     append sql \
   122    146       "SELECT count(*), avg(b) FROM t1 WHERE c LIKE '%[number_name $i]%';"
   123    147   }
   124         -speed_trial speed2-select2 [expr {50*50000}] row $sql
          148  +speed_trial speed2-select2b [expr {50*50000}] row $sql
   125    149   
   126    150   # Create indices
   127    151   #
   128    152   db eval BEGIN
   129    153   speed_trial speed2-createidx 150000 row {
   130    154     CREATE INDEX i1a ON t1(a);
   131    155     CREATE INDEX i1b ON t1(b);
................................................................................
   138    162   #
   139    163   set sql {}
   140    164   for {set i 0} {$i<5000} {incr i} {
   141    165     set lwr [expr {$i*100}]
   142    166     set upr [expr {($i+10)*100}]
   143    167     append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
   144    168   }
   145         -speed_trial speed2-select3 5000 stmt $sql
          169  +speed_trial speed2-select3a 5000 stmt $sql
          170  +
          171  +# 100000 random SELECTs against rowid.
          172  +#
          173  +set sql {}
          174  +for {set i 1} {$i<=100000} {incr i} {
          175  +  set id [expr {int(rand()*50000)+1}]
          176  +  append sql "SELECT c=='hi' FROM t1 WHERE rowid=$id;\n"
          177  +}
          178  +speed_trial speed2-select4a 100000 row $sql
          179  +
          180  +# 100000 random SELECTs against a unique indexed column.
          181  +#
          182  +set sql {}
          183  +for {set i 1} {$i<=100000} {incr i} {
          184  +  set id [expr {int(rand()*50000)+1}]
          185  +  append sql "SELECT c FROM t1 WHERE a=$id;"
          186  +}
          187  +speed_trial speed2-select5a 100000 row $sql
          188  +
          189  +# 50000 random SELECTs against an indexed column text column
          190  +#
          191  +set sql {}
          192  +db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {
          193  +  append sql "SELECT c FROM t1 WHERE c='$c';"
          194  +}
          195  +speed_trial speed2-select6a 50000 row $sql
          196  +
          197  +# Vacuum
          198  +speed_trial speed2-vacuum2 100000 row VACUUM
          199  +
          200  +
          201  +# 5000 SELECTs on an integer comparison where the integer is
          202  +# indexed.
          203  +#
          204  +set sql {}
          205  +for {set i 0} {$i<5000} {incr i} {
          206  +  set lwr [expr {$i*100}]
          207  +  set upr [expr {($i+10)*100}]
          208  +  append sql "SELECT count(*), avg(b) FROM t1 WHERE b>=$lwr AND b<$upr;"
          209  +}
          210  +speed_trial speed2-select3b 5000 stmt $sql
   146    211   
   147    212   # 100000 random SELECTs against rowid.
   148    213   #
   149    214   set sql {}
   150    215   for {set i 1} {$i<=100000} {incr i} {
   151    216     set id [expr {int(rand()*50000)+1}]
   152    217     append sql "SELECT c=='hi' FROM t1 WHERE rowid=$id;\n"
   153    218   }
   154         -speed_trial speed2-select4 100000 row $sql
          219  +speed_trial speed2-select4b 100000 row $sql
   155    220   
   156    221   # 100000 random SELECTs against a unique indexed column.
   157    222   #
   158    223   set sql {}
   159    224   for {set i 1} {$i<=100000} {incr i} {
   160    225     set id [expr {int(rand()*50000)+1}]
   161    226     append sql "SELECT c FROM t1 WHERE a=$id;"
   162    227   }
   163         -speed_trial speed2-select5 100000 row $sql
          228  +speed_trial speed2-select5b 100000 row $sql
   164    229   
   165    230   # 50000 random SELECTs against an indexed column text column
   166    231   #
   167    232   set sql {}
   168    233   db eval {SELECT c FROM t1 ORDER BY random() LIMIT 50000} {
   169    234     append sql "SELECT c FROM t1 WHERE c='$c';"
   170    235   }
   171         -speed_trial speed2-select6 50000 row $sql
   172         -
   173         -# Vacuum
   174         -speed_trial speed2-vacuum 100000 row VACUUM
          236  +speed_trial speed2-select6b 50000 row $sql
   175    237   
   176    238   # 5000 updates of ranges where the field being compared is indexed.
   177    239   #
   178    240   set sql {}
   179    241   for {set i 0} {$i<5000} {incr i} {
   180    242     set lwr [expr {$i*2}]
   181    243     set upr [expr {($i+1)*2}]