/ Check-in [35b4d6e9]
Login

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

Overview
Comment:Avoid unnecessary calls to applyAffinity() during INSERT and UPDATE operations, especially for table that have indices and tables for which all columns have affinity "NONE".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | insert-optimization
Files: files | file ages | folders
SHA1: 35b4d6e938164fabaf30b504c54cfd9a69060cee
User & Date: drh 2014-02-17 14:59:22
Context
2014-02-17
15:36
Merge the VDBE_PROFILE fixes from trunk. check-in: 2914e419 user: drh tags: insert-optimization
14:59
Avoid unnecessary calls to applyAffinity() during INSERT and UPDATE operations, especially for table that have indices and tables for which all columns have affinity "NONE". check-in: 35b4d6e9 user: drh tags: insert-optimization
2014-02-16
01:55
Enhance the code generator for INSERT INTO ... SELECT so that the SELECT generates output directly in the registers that INSERT INTO will be using, in many cases, and OP_SCopy operations can thus be avoided. check-in: aa2d8b0e user: drh tags: insert-optimization
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

  1181   1181   #else
  1182   1182         for(i=0; i<nCol; i++){
  1183   1183           i16 iCol = pIdx->aiColumn[i];
  1184   1184           sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regCol+i);
  1185   1185         }
  1186   1186         sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol+1, regSample);
  1187   1187   #endif
  1188         -      sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 6, regTemp, "bbbbbb", 0);
         1188  +      sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp);
  1189   1189         sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid);
  1190   1190         sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regTemp, regNewRowid);
  1191   1191         sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext);
  1192   1192         sqlite3VdbeJumpHere(v, addrIsNull);
  1193   1193       }
  1194   1194   #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
  1195   1195   

Changes to src/delete.c.

   439    439         if( addrEphOpen ) sqlite3VdbeChangeToNoop(v, addrEphOpen);
   440    440         addrDelete = sqlite3VdbeAddOp0(v, OP_Goto); /* Jump to DELETE logic */
   441    441       }else if( pPk ){
   442    442         /* Construct a composite key for the row to be deleted and remember it */
   443    443         iKey = ++pParse->nMem;
   444    444         nKey = 0;   /* Zero tells OP_Found to use a composite key */
   445    445         sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
   446         -                        sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
          446  +                        sqlite3IndexAffinityStr(v, pPk), nPk);
   447    447         sqlite3VdbeAddOp2(v, OP_IdxInsert, iEphCur, iKey);
   448    448       }else{
   449    449         /* Get the rowid of the row to be deleted and remember it in the RowSet */
   450    450         nKey = 1;  /* OP_Seek always uses a single rowid */
   451    451         sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey);
   452    452       }
   453    453     

Changes to src/fkey.c.

   408    408             }
   409    409             sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent);
   410    410             sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
   411    411           }
   412    412           sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
   413    413         }
   414    414     
   415         -      sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec);
   416         -      sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
          415  +      sqlite3VdbeAddOp4(v, OP_MakeRecord, regTemp, nCol, regRec,
          416  +                        sqlite3IndexAffinityStr(v,pIdx), nCol);
   417    417         sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0);
   418    418     
   419    419         sqlite3ReleaseTempReg(pParse, regRec);
   420    420         sqlite3ReleaseTempRange(pParse, regTemp, nCol);
   421    421       }
   422    422     }
   423    423   

Changes to src/insert.c.

    94     94       pIdx->zColAff[n] = 0;
    95     95     }
    96     96    
    97     97     return pIdx->zColAff;
    98     98   }
    99     99   
   100    100   /*
   101         -** Set P4 of the most recently inserted opcode to a column affinity
   102         -** string for table pTab. A column affinity string has one character
   103         -** for each column indexed by the index, according to the affinity of the
   104         -** column:
          101  +** Compute the affinity string for table pTab, if it has not already been
          102  +** computed.  As an optimization, omit trailing SQLITE_AFF_NONE affinities.
          103  +**
          104  +** If the affinity exists (if it is no entirely SQLITE_AFF_NONE values and
          105  +** if iReg>0 then code an OP_Affinity opcode that will set the affinities
          106  +** for register iReg and following.  Or if affinities exists and iReg==0,
          107  +** then just set the P4 operand of the previous opcode (which should  be
          108  +** an OP_MakeRecord) to the affinity string.
          109  +**
          110  +** A column affinity string has one character column:
   105    111   **
   106    112   **  Character      Column affinity
   107    113   **  ------------------------------
   108    114   **  'a'            TEXT
   109    115   **  'b'            NONE
   110    116   **  'c'            NUMERIC
   111    117   **  'd'            INTEGER
   112    118   **  'e'            REAL
   113    119   */
   114         -void sqlite3TableAffinityStr(Vdbe *v, Table *pTab){
   115         -  /* The first time a column affinity string for a particular table
   116         -  ** is required, it is allocated and populated here. It is then 
   117         -  ** stored as a member of the Table structure for subsequent use.
   118         -  **
   119         -  ** The column affinity string will eventually be deleted by
   120         -  ** sqlite3DeleteTable() when the Table structure itself is cleaned up.
   121         -  */
   122         -  if( !pTab->zColAff ){
   123         -    char *zColAff;
   124         -    int i;
          120  +void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
          121  +  int i;
          122  +  char *zColAff = pTab->zColAff;
          123  +  if( zColAff==0 ){
   125    124       sqlite3 *db = sqlite3VdbeDb(v);
   126         -
   127    125       zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
   128    126       if( !zColAff ){
   129    127         db->mallocFailed = 1;
   130    128         return;
   131    129       }
   132    130   
   133    131       for(i=0; i<pTab->nCol; i++){
   134    132         zColAff[i] = pTab->aCol[i].affinity;
   135    133       }
   136         -    zColAff[pTab->nCol] = '\0';
   137         -
          134  +    do{
          135  +      zColAff[i--] = 0;
          136  +    }while( i>=0 && zColAff[i]==SQLITE_AFF_NONE );
   138    137       pTab->zColAff = zColAff;
   139    138     }
   140         -
   141         -  sqlite3VdbeChangeP4(v, -1, pTab->zColAff, P4_TRANSIENT);
          139  +  i = sqlite3Strlen30(zColAff);
          140  +  if( i ){
          141  +    if( iReg ){
          142  +      sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);
          143  +    }else{
          144  +      sqlite3VdbeChangeP4(v, -1, zColAff, i);
          145  +    }
          146  +  }
   142    147   }
   143    148   
   144    149   /*
   145    150   ** Return non-zero if the table pTab in database iDb or any of its indices
   146    151   ** have been opened at any point in the VDBE program beginning at location
   147    152   ** iStartAddr throught the end of the program.  This is used to see if 
   148    153   ** a statement of the form  "INSERT INTO <iDb, pTab> SELECT ..." can 
................................................................................
   842    847   
   843    848       /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
   844    849       ** do not attempt any conversions before assembling the record.
   845    850       ** If this is a real table, attempt conversions as required by the
   846    851       ** table column affinities.
   847    852       */
   848    853       if( !isView ){
   849         -      sqlite3VdbeAddOp2(v, OP_Affinity, regCols+1, pTab->nCol);
   850         -      sqlite3TableAffinityStr(v, pTab);
          854  +      sqlite3TableAffinity(v, pTab, regCols+1);
   851    855       }
   852    856   
   853    857       /* Fire BEFORE or INSTEAD OF triggers */
   854    858       sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_BEFORE, 
   855    859           pTab, regCols-pTab->nCol-1, onError, endOfLoop);
   856    860   
   857    861       sqlite3ReleaseTempRange(pParse, regCols, pTab->nCol+1);
................................................................................
  1149   1153     int onError;         /* Conflict resolution strategy */
  1150   1154     int j1;              /* Addresss of jump instruction */
  1151   1155     int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  1152   1156     int nPkField;        /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
  1153   1157     int ipkTop = 0;      /* Top of the rowid change constraint check */
  1154   1158     int ipkBottom = 0;   /* Bottom of the rowid change constraint check */
  1155   1159     u8 isUpdate;         /* True if this is an UPDATE operation */
         1160  +  u8 bAffinityDone = 0;  /* True if the OP_Affinity operation has been run */
  1156   1161     int regRowid = -1;   /* Register holding ROWID value */
  1157   1162   
  1158   1163     isUpdate = regOldData!=0;
  1159   1164     db = pParse->db;
  1160   1165     v = sqlite3GetVdbe(pParse);
  1161   1166     assert( v!=0 );
  1162   1167     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
................................................................................
  1360   1365     for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
  1361   1366       int regIdx;          /* Range of registers hold conent for pIdx */
  1362   1367       int regR;            /* Range of registers holding conflicting PK */
  1363   1368       int iThisCur;        /* Cursor for this UNIQUE index */
  1364   1369       int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */
  1365   1370   
  1366   1371       if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
         1372  +    if( bAffinityDone==0 ){
         1373  +      sqlite3TableAffinity(v, pTab, regNewData+1);
         1374  +      bAffinityDone = 1;
         1375  +    }
  1367   1376       iThisCur = iIdxCur+ix;
  1368   1377       addrUniqueOk = sqlite3VdbeMakeLabel(v);
  1369   1378   
  1370   1379       /* Skip partial indices for which the WHERE clause is not true */
  1371   1380       if( pIdx->pPartIdxWhere ){
  1372   1381         sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
  1373   1382         pParse->ckBase = regNewData+1;
................................................................................
  1390   1399         }else{
  1391   1400           x = iField + regNewData + 1;
  1392   1401         }
  1393   1402         sqlite3VdbeAddOp2(v, OP_SCopy, x, regIdx+i);
  1394   1403         VdbeComment((v, "%s", iField<0 ? "rowid" : pTab->aCol[iField].zName));
  1395   1404       }
  1396   1405       sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn, aRegIdx[ix]);
  1397         -    sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), P4_TRANSIENT);
  1398   1406       VdbeComment((v, "for %s", pIdx->zName));
  1399   1407       sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn);
  1400   1408   
  1401   1409       /* In an UPDATE operation, if this index is the PRIMARY KEY index 
  1402   1410       ** of a WITHOUT ROWID table and there has been no change the
  1403   1411       ** primary key, then no collision is possible.  The collision detection
  1404   1412       ** logic below can all be skipped. */
................................................................................
  1535   1543   ){
  1536   1544     Vdbe *v;            /* Prepared statements under construction */
  1537   1545     Index *pIdx;        /* An index being inserted or updated */
  1538   1546     u8 pik_flags;       /* flag values passed to the btree insert */
  1539   1547     int regData;        /* Content registers (after the rowid) */
  1540   1548     int regRec;         /* Register holding assemblied record for the table */
  1541   1549     int i;              /* Loop counter */
         1550  +  u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */
  1542   1551   
  1543   1552     v = sqlite3GetVdbe(pParse);
  1544   1553     assert( v!=0 );
  1545   1554     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1546   1555     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
  1547   1556       if( aRegIdx[i]==0 ) continue;
         1557  +    bAffinityDone = 1;
  1548   1558       if( pIdx->pPartIdxWhere ){
  1549   1559         sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
  1550   1560       }
  1551   1561       sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]);
  1552   1562       pik_flags = 0;
  1553   1563       if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT;
  1554   1564       if( pIdx->autoIndex==2 && !HasRowid(pTab) ){
................................................................................
  1557   1567       }
  1558   1568       if( pik_flags )  sqlite3VdbeChangeP5(v, pik_flags);
  1559   1569     }
  1560   1570     if( !HasRowid(pTab) ) return;
  1561   1571     regData = regNewData + 1;
  1562   1572     regRec = sqlite3GetTempReg(pParse);
  1563   1573     sqlite3VdbeAddOp3(v, OP_MakeRecord, regData, pTab->nCol, regRec);
  1564         -  sqlite3TableAffinityStr(v, pTab);
         1574  +  if( !bAffinityDone ) sqlite3TableAffinity(v, pTab, 0);
  1565   1575     sqlite3ExprCacheAffinityChange(pParse, regData, pTab->nCol);
  1566   1576     if( pParse->nested ){
  1567   1577       pik_flags = 0;
  1568   1578     }else{
  1569   1579       pik_flags = OPFLAG_NCHANGE;
  1570   1580       pik_flags |= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID);
  1571   1581     }

Changes to src/pragma.c.

  1723   1723           }else{
  1724   1724             for(j=0; j<pFK->nCol; j++){
  1725   1725               sqlite3ExprCodeGetColumnOfTable(v, pTab, 0,
  1726   1726                               aiCols ? aiCols[j] : pFK->aCol[j].iFrom, regRow+j);
  1727   1727               sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
  1728   1728             }
  1729   1729             if( pParent ){
  1730         -            sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
  1731         -            sqlite3VdbeChangeP4(v, -1,
  1732         -                     sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
         1730  +            sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, pFK->nCol, regKey,
         1731  +                              sqlite3IndexAffinityStr(v,pIdx), pFK->nCol);
  1733   1732               sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
  1734   1733             }
  1735   1734           }
  1736   1735           sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
  1737   1736           sqlite3VdbeAddOp4(v, OP_String8, 0, regResult+2, 0, 
  1738   1737                             pFK->zTo, P4_TRANSIENT);
  1739   1738           sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+3);

Changes to src/sqliteInt.h.

  3220   3220     (u8)(((u32)(B)<(u32)0x80)?(*(A)=(unsigned char)(B)),1:\
  3221   3221     sqlite3PutVarint32((A),(B)))
  3222   3222   #define getVarint    sqlite3GetVarint
  3223   3223   #define putVarint    sqlite3PutVarint
  3224   3224   
  3225   3225   
  3226   3226   const char *sqlite3IndexAffinityStr(Vdbe *, Index *);
  3227         -void sqlite3TableAffinityStr(Vdbe *, Table *);
         3227  +void sqlite3TableAffinity(Vdbe*, Table*, int);
  3228   3228   char sqlite3CompareAffinity(Expr *pExpr, char aff2);
  3229   3229   int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity);
  3230   3230   char sqlite3ExprAffinity(Expr *pExpr);
  3231   3231   int sqlite3Atoi64(const char*, i64*, int, u8);
  3232   3232   void sqlite3Error(sqlite3*, int, const char*,...);
  3233   3233   void *sqlite3HexToBlob(sqlite3*, const char *z, int n);
  3234   3234   u8 sqlite3HexToInt(int h);

Changes to src/update.c.

   386    386       }
   387    387       if( okOnePass ){
   388    388         sqlite3VdbeChangeToNoop(v, addrOpen);
   389    389         nKey = nPk;
   390    390         regKey = iPk;
   391    391       }else{
   392    392         sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey,
   393         -                        sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
          393  +                        sqlite3IndexAffinityStr(v, pPk), nPk);
   394    394         sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, regKey);
   395    395       }
   396    396       sqlite3WhereEnd(pWInfo);
   397    397     }
   398    398   
   399    399     /* Initialize the count of updated rows
   400    400     */
................................................................................
   520    520       }
   521    521     }
   522    522   
   523    523     /* Fire any BEFORE UPDATE triggers. This happens before constraints are
   524    524     ** verified. One could argue that this is wrong.
   525    525     */
   526    526     if( tmask&TRIGGER_BEFORE ){
   527         -    sqlite3VdbeAddOp2(v, OP_Affinity, regNew, pTab->nCol);
   528         -    sqlite3TableAffinityStr(v, pTab);
          527  +    sqlite3TableAffinity(v, pTab, regNew);
   529    528       sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, 
   530    529           TRIGGER_BEFORE, pTab, regOldRowid, onError, labelContinue);
   531    530   
   532    531       /* The row-trigger may have deleted the row being updated. In this
   533    532       ** case, jump to the next row. No updates or AFTER triggers are 
   534    533       ** required. This behavior - what happens when the row being updated
   535    534       ** is deleted or renamed by a BEFORE trigger - is left undefined in the

Changes to src/vdbe.c.

  2494   2494     zAffinity = pOp->p4.z;
  2495   2495     assert( zAffinity!=0 );
  2496   2496     assert( zAffinity[pOp->p2]==0 );
  2497   2497     pIn1 = &aMem[pOp->p1];
  2498   2498     while( (cAff = *(zAffinity++))!=0 ){
  2499   2499       assert( pIn1 <= &p->aMem[(p->nMem-p->nCursor)] );
  2500   2500       assert( memIsValid(pIn1) );
  2501         -    ExpandBlob(pIn1);
  2502   2501       applyAffinity(pIn1, cAff, encoding);
  2503   2502       pIn1++;
  2504   2503     }
  2505   2504     break;
  2506   2505   }
  2507   2506   
  2508   2507   /* Opcode: MakeRecord P1 P2 P3 P4 *
................................................................................
  2572   2571   
  2573   2572     /* Apply the requested affinity to all inputs
  2574   2573     */
  2575   2574     assert( pData0<=pLast );
  2576   2575     if( zAffinity ){
  2577   2576       pRec = pData0;
  2578   2577       do{
  2579         -      applyAffinity(pRec, *(zAffinity++), encoding);
  2580         -    }while( (++pRec)<=pLast );
         2578  +      applyAffinity(pRec++, *(zAffinity++), encoding);
         2579  +      assert( zAffinity[0]==0 || pRec<=pLast );
         2580  +    }while( zAffinity[0] );
  2581   2581     }
  2582   2582   
  2583   2583     /* Loop through the elements that will make up the record to figure
  2584   2584     ** out how much space is required for the new record.
  2585   2585     */
  2586   2586     pRec = pLast;
  2587   2587     do{