/ Check-in [c6239bf9]
Login

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

Overview
Comment:Change the code generator for UPDATE to generate code in an order that might run more efficiently in many cases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | improved-update
Files: files | file ages | folders
SHA1: c6239bf943fc42b8fc1ece95c9268240b7c53e80
User & Date: drh 2015-10-17 01:00:03
Context
2015-10-17
01:00
Change the code generator for UPDATE to generate code in an order that might run more efficiently in many cases. Leaf check-in: c6239bf9 user: drh tags: improved-update
2015-10-16
23:55
Fix a comment in expr.c and add a CORRUPT_DB to an assert() in btree.c. check-in: 0df371d1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/update.c.

   492    492     }
   493    493   
   494    494     /* Populate the array of registers beginning at regNew with the new
   495    495     ** row data. This array is used to check constants, create the new
   496    496     ** table and index records, and as the values for any new.* references
   497    497     ** made by triggers.
   498    498     **
          499  +  ** As an optimization, the array is loaded in two passes.  The first pass
          500  +  ** loads unchanged values using OP_Column opcodes so that they can be
          501  +  ** cached and potentially reused by subsequent expressions.  Also, the
          502  +  ** OP_Columns are loaded in reverse order so that the row type/offset cache
          503  +  ** inside the OP_Column implementation in the VDBE will warm up completely
          504  +  ** on the first opcode, rather than incrementally across each opcode.
          505  +  **
   499    506     ** If there are one or more BEFORE triggers, then do not populate the
   500    507     ** registers associated with columns that are (a) not modified by
   501    508     ** this UPDATE statement and (b) not accessed by new.* references. The
   502    509     ** values for registers not modified by the UPDATE must be reloaded from 
   503    510     ** the database after the BEFORE triggers are fired anyway (as the trigger 
   504    511     ** may have modified them). So not loading those that are not going to
   505    512     ** be used eliminates some redundant opcodes.
   506    513     */
   507    514     newmask = sqlite3TriggerColmask(
   508    515         pParse, pTrigger, pChanges, 1, TRIGGER_BEFORE, pTab, onError
   509    516     );
   510         -  for(i=0; i<pTab->nCol; i++){
   511         -    if( i==pTab->iPKey ){
   512         -      sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i);
          517  +  for(i=pTab->nCol-1; i>=0; i--){
          518  +    if( aXRef[i]>=0 && i!=pTab->iPKey ) continue;
          519  +    if( i!=pTab->iPKey
          520  +     && (0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i))!=0)
          521  +    ){
          522  +      testcase( i==31 );
          523  +      testcase( i==32 );
          524  +      sqlite3ExprCodeGetColumnToReg(pParse, pTab, i, iDataCur, regNew+i);
   513    525       }else{
   514         -      j = aXRef[i];
   515         -      if( j>=0 ){
   516         -        sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
   517         -      }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i)) ){
   518         -        /* This branch loads the value of a column that will not be changed 
   519         -        ** into a register. This is done if there are no BEFORE triggers, or
   520         -        ** if there are one or more BEFORE triggers that use this value via
   521         -        ** a new.* reference in a trigger program.
   522         -        */
   523         -        testcase( i==31 );
   524         -        testcase( i==32 );
   525         -        sqlite3ExprCodeGetColumnToReg(pParse, pTab, i, iDataCur, regNew+i);
   526         -      }else{
   527         -        sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i);
   528         -      }
          526  +      sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i);
          527  +    }  
          528  +  }
          529  +  for(i=0; i<pTab->nCol; i++){
          530  +    if( (j = aXRef[i])>=0 && i!=pTab->iPKey ){
          531  +      sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
   529    532       }
   530    533     }
   531    534   
   532    535     /* Fire any BEFORE UPDATE triggers. This happens before constraints are
   533    536     ** verified. One could argue that this is wrong.
   534    537     */
   535    538     if( tmask&TRIGGER_BEFORE ){