/ Check-in [e3c3f4d7]
Login

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

Overview
Comment:Performance optimization on sqlite3GenerateConstraintChecks() - bypass the loop that checks each column for NOT NULL constraints if it is known in advance that the table has no NOT NULL constraints.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e3c3f4d7872f431a95627d52553101388c1e39458cc7e7f93fc81255f49a89a5
User & Date: drh 2019-10-26 17:08:06
Context
2019-10-26
18:47
Add support for generated columns. check-in: b855acf1 user: drh tags: trunk
17:08
Performance optimization on sqlite3GenerateConstraintChecks() - bypass the loop that checks each column for NOT NULL constraints if it is known in advance that the table has no NOT NULL constraints. check-in: e3c3f4d7 user: drh tags: trunk
16:38
Clarify some comments and add assert() and testcase() macros to the replace-trigger recheck logic for ticket [c1e19e12046d23fe] check-in: 8c0042bd user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

1832
1833
1834
1835
1836
1837
1838

1839
1840
1841
1842
1843
1844
1845
  */
  if( !db->init.imposterTable ){
    for(i=0; i<pTab->nCol; i++){
      if( (pTab->aCol[i].colFlags & COLFLAG_PRIMKEY)!=0 ){
        pTab->aCol[i].notNull = OE_Abort;
      }
    }

  }

  /* Convert the P3 operand of the OP_CreateBtree opcode from BTREE_INTKEY
  ** into BTREE_BLOBKEY.
  */
  if( pParse->addrCrTab ){
    assert( v );







>







1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
  */
  if( !db->init.imposterTable ){
    for(i=0; i<pTab->nCol; i++){
      if( (pTab->aCol[i].colFlags & COLFLAG_PRIMKEY)!=0 ){
        pTab->aCol[i].notNull = OE_Abort;
      }
    }
    pTab->tabFlags |= TF_HasNotNull;
  }

  /* Convert the P3 operand of the OP_CreateBtree opcode from BTREE_INTKEY
  ** into BTREE_BLOBKEY.
  */
  if( pParse->addrCrTab ){
    assert( v );

Changes to src/insert.c.

1348
1349
1350
1351
1352
1353
1354

1355



1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412

1413
1414
1415
1416
1417
1418
1419

  /* Record that this module has started */
  VdbeModuleComment((v, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
                     iDataCur, iIdxCur, regNewData, regOldData, pkChng));

  /* Test all NOT NULL constraints.
  */

  for(i=0; i<nCol; i++){



    if( i==pTab->iPKey ){
      continue;        /* ROWID is never NULL */
    }
    if( aiChng && aiChng[i]<0 ){
      /* Don't bother checking for NOT NULL on columns that do not change */
      continue;
    }
    onError = pTab->aCol[i].notNull;
    if( onError==OE_None ) continue;  /* This column is allowed to be NULL */
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
      onError = OE_Abort;
    }
    assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
        || onError==OE_Ignore || onError==OE_Replace );
    addr1 = 0;
    switch( onError ){
      case OE_Replace: {
        assert( onError==OE_Replace );
        addr1 = sqlite3VdbeMakeLabel(pParse);
        sqlite3VdbeAddOp2(v, OP_NotNull, regNewData+1+i, addr1);
          VdbeCoverage(v);
        sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regNewData+1+i);
        sqlite3VdbeAddOp2(v, OP_NotNull, regNewData+1+i, addr1);
          VdbeCoverage(v);
        onError = OE_Abort;
        /* Fall through into the OE_Abort case to generate code that runs
        ** if both the input and the default value are NULL */
      }
      case OE_Abort:
        sqlite3MayAbort(pParse);
        /* Fall through */
      case OE_Rollback:
      case OE_Fail: {
        char *zMsg = sqlite3MPrintf(db, "%s.%s", pTab->zName,
                                    pTab->aCol[i].zName);
        sqlite3VdbeAddOp3(v, OP_HaltIfNull, SQLITE_CONSTRAINT_NOTNULL, onError,
                          regNewData+1+i);
        sqlite3VdbeAppendP4(v, zMsg, P4_DYNAMIC);
        sqlite3VdbeChangeP5(v, P5_ConstraintNotNull);
        VdbeCoverage(v);
        if( addr1 ) sqlite3VdbeResolveLabel(v, addr1);
        break;
      }
      default: {
        assert( onError==OE_Ignore );
        sqlite3VdbeAddOp2(v, OP_IsNull, regNewData+1+i, ignoreDest);
        VdbeCoverage(v);
        break;
      }
    }
  }


  /* Test all CHECK constraints
  */
#ifndef SQLITE_OMIT_CHECK
  if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
    ExprList *pCheck = pTab->pCheck;
    pParse->iSelfTab = -(regNewData+1);
    onError = overrideError!=OE_Default ? overrideError : OE_Abort;







>
|
>
>
>
|
|
|
|
|
|
|
<
<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366


1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422

  /* Record that this module has started */
  VdbeModuleComment((v, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
                     iDataCur, iIdxCur, regNewData, regOldData, pkChng));

  /* Test all NOT NULL constraints.
  */
  if( pTab->tabFlags & TF_HasNotNull ){
    for(i=0; i<nCol; i++){
      onError = pTab->aCol[i].notNull;
      if( onError==OE_None ) continue; /* No NOT NULL on this column */
      assert( pTab->tabFlags & TF_HasNotNull );
      if( i==pTab->iPKey ){
        continue;        /* ROWID is never NULL */
      }
      if( aiChng && aiChng[i]<0 ){
        /* Don't bother checking for NOT NULL on columns that do not change */
        continue;
      }


      if( overrideError!=OE_Default ){
        onError = overrideError;
      }else if( onError==OE_Default ){
        onError = OE_Abort;
      }
      if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
        onError = OE_Abort;
      }
      assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
          || onError==OE_Ignore || onError==OE_Replace );
      addr1 = 0;
      switch( onError ){
        case OE_Replace: {
          assert( onError==OE_Replace );
          addr1 = sqlite3VdbeMakeLabel(pParse);
          sqlite3VdbeAddOp2(v, OP_NotNull, regNewData+1+i, addr1);
            VdbeCoverage(v);
          sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regNewData+1+i);
          sqlite3VdbeAddOp2(v, OP_NotNull, regNewData+1+i, addr1);
            VdbeCoverage(v);
          onError = OE_Abort;
          /* Fall through into the OE_Abort case to generate code that runs
          ** if both the input and the default value are NULL */
        }
        case OE_Abort:
          sqlite3MayAbort(pParse);
          /* Fall through */
        case OE_Rollback:
        case OE_Fail: {
          char *zMsg = sqlite3MPrintf(db, "%s.%s", pTab->zName,
                                      pTab->aCol[i].zName);
          sqlite3VdbeAddOp3(v, OP_HaltIfNull, SQLITE_CONSTRAINT_NOTNULL,
                            onError, regNewData+1+i);
          sqlite3VdbeAppendP4(v, zMsg, P4_DYNAMIC);
          sqlite3VdbeChangeP5(v, P5_ConstraintNotNull);
          VdbeCoverage(v);
          if( addr1 ) sqlite3VdbeResolveLabel(v, addr1);
          break;
        }
        default: {
          assert( onError==OE_Ignore );
          sqlite3VdbeAddOp2(v, OP_IsNull, regNewData+1+i, ignoreDest);
          VdbeCoverage(v);
          break;
        }
      }
    }
  }
  
  /* Test all CHECK constraints
  */
#ifndef SQLITE_OMIT_CHECK
  if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
    ExprList *pCheck = pTab->pCheck;
    pParse->iSelfTab = -(regNewData+1);
    onError = overrideError!=OE_Default ? overrideError : OE_Abort;