/ Check-in [8b8314cb]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Enable the one-pass optimization for DELETE and UPDATE on RTree. This is parked in a branch because is seems to make no measureable performance difference.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | rtree-one-pass
Files: files | file ages | folders
SHA3-256: 8b8314cb9bfa6231e2f3b0e08facc1453473c2cd7e605745184ca5d1dee34732
User & Date: drh 2018-01-12 00:30:54
Context
2018-01-12
00:30
Enable the one-pass optimization for DELETE and UPDATE on RTree. This is parked in a branch because is seems to make no measureable performance difference. Leaf check-in: 8b8314cb user: drh tags: rtree-one-pass
2018-01-11
18:15
Add the sqlite3_vtab_nochange() interface. Test cases are in TH3. check-in: a5d09dfa user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree.c.

1847
1848
1849
1850
1851
1852
1853

1854
1855
1856
1857
1858
1859
1860
      ** and then a linear search of an R-Tree node. This should be 
      ** considered almost as quick as a direct rowid lookup (for which 
      ** sqlite uses an internal cost of 0.0). It is expected to return
      ** a single row.
      */ 
      pIdxInfo->estimatedCost = 30.0;
      pIdxInfo->estimatedRows = 1;

      return SQLITE_OK;
    }

    if( p->usable && (p->iColumn>0 || p->op==SQLITE_INDEX_CONSTRAINT_MATCH) ){
      u8 op;
      switch( p->op ){
        case SQLITE_INDEX_CONSTRAINT_EQ: op = RTREE_EQ; break;







>







1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
      ** and then a linear search of an R-Tree node. This should be 
      ** considered almost as quick as a direct rowid lookup (for which 
      ** sqlite uses an internal cost of 0.0). It is expected to return
      ** a single row.
      */ 
      pIdxInfo->estimatedCost = 30.0;
      pIdxInfo->estimatedRows = 1;
      pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
      return SQLITE_OK;
    }

    if( p->usable && (p->iColumn>0 || p->op==SQLITE_INDEX_CONSTRAINT_MATCH) ){
      u8 op;
      switch( p->op ){
        case SQLITE_INDEX_CONSTRAINT_EQ: op = RTREE_EQ; break;

Changes to src/delete.c.

475
476
477
478
479
480
481



482
483
484
485
486
487
488
        goto delete_from_cleanup;
      }
      memset(aToOpen, 1, nIdx+1);
      aToOpen[nIdx+1] = 0;
      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0;
      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0;
      if( addrEphOpen ) sqlite3VdbeChangeToNoop(v, addrEphOpen);



    }else{
      if( pPk ){
        /* Add the PK key for this row to the temporary table */
        iKey = ++pParse->nMem;
        nKey = 0;   /* Zero tells OP_Found to use a composite key */
        sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
            sqlite3IndexAffinityStr(pParse->db, pPk), nPk);







>
>
>







475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
        goto delete_from_cleanup;
      }
      memset(aToOpen, 1, nIdx+1);
      aToOpen[nIdx+1] = 0;
      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0;
      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0;
      if( addrEphOpen ) sqlite3VdbeChangeToNoop(v, addrEphOpen);
#ifndef SQLITE_OMIT_VIRTUALTABLE
      if( IsVirtual(pTab) ) sqlite3VdbeAddOp1(v, OP_Close, iTabCur);
#endif
    }else{
      if( pPk ){
        /* Add the PK key for this row to the temporary table */
        iKey = ++pParse->nMem;
        nKey = 0;   /* Zero tells OP_Found to use a composite key */
        sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
            sqlite3IndexAffinityStr(pParse->db, pPk), nPk);

Changes to src/update.c.

860
861
862
863
864
865
866


867
868


869
870
871
872
873
874
875
876
877
878
879
880
...
886
887
888
889
890
891
892
893
894
895
896
897
898
    }
  }else{
    /* Create a record from the argument register contents and insert it into
    ** the ephemeral table. */
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec);
    sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid);
    sqlite3VdbeAddOp3(v, OP_Insert, ephemTab, regRec, regRowid);


  }




  if( bOnePass==0 ){
    /* End the virtual table scan */
    sqlite3WhereEnd(pWInfo);

    /* Begin scannning through the ephemeral table. */
    addr = sqlite3VdbeAddOp1(v, OP_Rewind, ephemTab); VdbeCoverage(v);

    /* Extract arguments from the current row of the ephemeral table and 
    ** invoke the VUpdate method.  */
    for(i=0; i<nArg; i++){
      sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i, regArg+i);
................................................................................
  sqlite3MayAbort(pParse);

  /* End of the ephemeral table scan. Or, if using the onepass strategy,
  ** jump to here if the scan visited zero rows. */
  if( bOnePass==0 ){
    sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
    sqlite3VdbeJumpHere(v, addr);
    sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);
  }else{
    sqlite3WhereEnd(pWInfo);
  }
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */







>
>


>
>


<
<
<







 







|





860
861
862
863
864
865
866
867
868
869
870
871
872
873
874



875
876
877
878
879
880
881
...
887
888
889
890
891
892
893
894
895
896
897
898
899
    }
  }else{
    /* Create a record from the argument register contents and insert it into
    ** the ephemeral table. */
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec);
    sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid);
    sqlite3VdbeAddOp3(v, OP_Insert, ephemTab, regRec, regRowid);
    /* End the virtual table scan */
    sqlite3WhereEnd(pWInfo);
  }

  /* Make sure the scan cursor is closed prior to doing the update */
  sqlite3VdbeAddOp1(v, OP_Close, iCsr);

  if( bOnePass==0 ){



    /* Begin scannning through the ephemeral table. */
    addr = sqlite3VdbeAddOp1(v, OP_Rewind, ephemTab); VdbeCoverage(v);

    /* Extract arguments from the current row of the ephemeral table and 
    ** invoke the VUpdate method.  */
    for(i=0; i<nArg; i++){
      sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i, regArg+i);
................................................................................
  sqlite3MayAbort(pParse);

  /* End of the ephemeral table scan. Or, if using the onepass strategy,
  ** jump to here if the scan visited zero rows. */
  if( bOnePass==0 ){
    sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
    sqlite3VdbeJumpHere(v, addr);
    sqlite3VdbeAddOp1(v, OP_Close, ephemTab);
  }else{
    sqlite3WhereEnd(pWInfo);
  }
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */