/ Check-in [c9f04529]
Login

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

Overview
Comment:Improved presentation on the new code that prevents unnecessary writes to expressions on indexes during an UPDATE when the expression does not reference any of the columns that are changing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c9f045295c4577752b0847ff2027b44661e6cb15bb08b942ccb3a0ef396f3dec
User & Date: drh 2018-09-16 15:01:25
Context
2018-09-16
16:18
Add the new "explain" virtual table in ext/misc. Use this virtual table for additional test cases for the optimization that avoids updating indexes on expressions when none of the columns changed by the UPDATE are in the expression. check-in: 2404304c user: drh tags: trunk
15:01
Improved presentation on the new code that prevents unnecessary writes to expressions on indexes during an UPDATE when the expression does not reference any of the columns that are changing. check-in: c9f04529 user: drh tags: trunk
2018-09-15
21:43
Increase the version number to 3.26.0 as we start the next development cycle. check-in: 885f0f82 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/update.c.

    80     80   }
    81     81   
    82     82   /*
    83     83   ** Check to see if column iCol of index pIdx references any of the
    84     84   ** columns defined by aXRef and chngRowid.  Return true if it does
    85     85   ** and false if not.
    86     86   **
    87         -** The iCol-th column of pIdx will be an expression.
    88         -**
    89     87   ** aXRef[j] will be non-negative if column j of the original table is
    90     88   ** being updated.  chngRowid will be true if the rowid of the table is
    91     89   ** being updated.
    92     90   */
    93         -static int indexExprRefsUpdatedColumn(
    94         -  Index *pIdx,      /* The index containing the expression to analyze */
    95         -  int iCol,         /* Which column of the index is the expression */
           91  +static int indexColumnIsBeingUpdated(
           92  +  Index *pIdx,      /* The index to check */
           93  +  int iCol,         /* Which column of the index to check */
    96     94     int *aXRef,       /* aXRef[j]>=0 if column j is being updated */
    97     95     int chngRowid     /* true if the rowid is being updated */
    98     96   ){
           97  +  i16 iIdxCol = pIdx->aiColumn[iCol];
           98  +  if( iIdxCol>=0 ){
           99  +    return aXRef[iIdxCol]>=0;
          100  +  }
          101  +  if( iIdxCol==XN_ROWID ) return 1;
          102  +  assert( iIdxCol==XN_EXPR );
    99    103     assert( pIdx->aColExpr!=0 );
   100    104     assert( pIdx->aColExpr->a[iCol].pExpr!=0 );
   101    105     return sqlite3ExprReferencesUpdatedColumn(pIdx->aColExpr->a[iCol].pExpr,
   102    106                                               aXRef,chngRowid);
   103    107   }
   104    108   
   105    109   /*
................................................................................
   330    334       int reg;
   331    335       if( chngKey || hasFK>1 || pIdx->pPartIdxWhere || pIdx==pPk ){
   332    336         reg = ++pParse->nMem;
   333    337         pParse->nMem += pIdx->nColumn;
   334    338       }else{
   335    339         reg = 0;
   336    340         for(i=0; i<pIdx->nKeyCol; i++){
   337         -        i16 iIdxCol = pIdx->aiColumn[i];
   338         -        if( (iIdxCol>=0 && aXRef[iIdxCol]>=0)
   339         -         || iIdxCol==XN_ROWID
   340         -         || (iIdxCol==XN_EXPR
   341         -             && indexExprRefsUpdatedColumn(pIdx,i,aXRef,chngRowid))
   342         -        ){
          341  +        if( indexColumnIsBeingUpdated(pIdx, i, aXRef, chngRowid) ){
   343    342             reg = ++pParse->nMem;
   344    343             pParse->nMem += pIdx->nColumn;
   345    344             if( (onError==OE_Replace)
   346    345              || (onError==OE_Default && pIdx->onError==OE_Replace) 
   347    346             ){
   348    347               bReplace = 1;
   349    348             }