/ Check-in [e3f368cd]
Login

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

Overview
Comment:Update the xfer optimization code so that the xfer optimization can be used with INTEGER PRIMARY KEY ON CONFLICT ... as long as the destination table is initially empty. Improvements to the comments on the xfer optimization. New test cases added.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:e3f368cd5ef66a56fd4bd05a77276039e26b9e0e
User & Date: drh 2011-11-04 14:36:02
Context
2011-11-06
05:06
Move function sqlite3PagerClearCache() out of the "ifndef SQLITE_OMIT_WAL" block and into an "ifndef SQLITE_OMIT_VACUUM" block. check-in: 5dbfaed8 user: dan tags: trunk
2011-11-04
14:36
Update the xfer optimization code so that the xfer optimization can be used with INTEGER PRIMARY KEY ON CONFLICT ... as long as the destination table is initially empty. Improvements to the comments on the xfer optimization. New test cases added. check-in: e3f368cd user: drh tags: trunk
12:05
Change a memcpy() in sqlite3FileSuffix() to memmove() on the grounds that the source and destination may overlap. check-in: 5e1d247e user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585

1586
1587
1588
1589

1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605

1606
1607
1608
1609
1610
1611
1612
....
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
....
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776

1777



1778
1779
1780
1781
1782







1783
1784
1785
1786
1787
1788
1789
}

/*
** Attempt the transfer optimization on INSERTs of the form
**
**     INSERT INTO tab1 SELECT * FROM tab2;
**
** This optimization is only attempted if
**
**    (1)  tab1 and tab2 have identical schemas including all the
**         same indices and constraints
**

**    (2)  tab1 and tab2 are different tables
**
**    (3)  There must be no triggers on tab1
**

**    (4)  The result set of the SELECT statement is "*"
**
**    (5)  The SELECT statement has no WHERE, HAVING, ORDER BY, GROUP BY,
**         or LIMIT clause.
**
**    (6)  The SELECT statement is a simple (not a compound) select that
**         contains only tab2 in its FROM clause
**
** This method for implementing the INSERT transfers raw records from
** tab2 over to tab1.  The columns are not decoded.  Raw records from
** the indices of tab2 are transfered to tab1 as well.  In so doing,
** the resulting tab1 has much less fragmentation.
**
** This routine returns TRUE if the optimization is attempted.  If any
** of the conditions above fail so that the optimization should not
** be attempted, then this routine returns FALSE.

*/
static int xferOptimization(
  Parse *pParse,        /* Parser context */
  Table *pDest,         /* The table we are inserting into */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  int onError,          /* How to handle constraint errors */
  int iDbDest           /* The database of pDest */
................................................................................
    return 0;   /* tab1 must not be a virtual table */
  }
#endif
  if( onError==OE_Default ){
    if( pDest->iPKey>=0 ) onError = pDest->keyConf;
    if( onError==OE_Default ) onError = OE_Abort;
  }
  if( onError!=OE_Abort && onError!=OE_Rollback ){
    return 0;   /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
  }
  assert(pSelect->pSrc);   /* allocated even if there is no FROM clause */
  if( pSelect->pSrc->nSrc!=1 ){
    return 0;   /* FROM clause must have exactly one term */
  }
  if( pSelect->pSrc->a[0].pSelect ){
    return 0;   /* FROM clause cannot contain a subquery */
  }
................................................................................
  ** not worth the effort.  Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
  */
  if( (pParse->db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){
    return 0;
  }
#endif
  if( (pParse->db->flags & SQLITE_CountRows)!=0 ){
    return 0;
  }

  /* If we get this far, it means either:
  **
  **    *   We can always do the transfer if the table contains an
  **        an integer primary key
  **
  **    *   We can conditionally do the transfer if the destination
  **        table is empty.
  */
#ifdef SQLITE_TEST
  sqlite3_xferopt_count++;
#endif
  iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
  v = sqlite3GetVdbe(pParse);
  sqlite3CodeVerifySchema(pParse, iDbSrc);
  iSrc = pParse->nTab++;
  iDest = pParse->nTab++;
  regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
  sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
  if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ){
    /* If tables do not have an INTEGER PRIMARY KEY and there
    ** are indices to be copied and the destination is not empty,
    ** we have to disallow the transfer optimization because the

    ** the rowids might change which will mess up indexing.



    **
    ** Or if the destination has a UNIQUE index and is not empty,
    ** we also disallow the transfer optimization because we cannot
    ** insure that all entries in the union of DEST and SRC will be
    ** unique.







    */
    addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
    emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
    sqlite3VdbeJumpHere(v, addr1);
  }else{
    emptyDestTest = 0;
  }







|
|
|
<

>
|
|
<

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

<
<
<
>







 







<
<
<







 







|


|
|
|
<
<
<
<











|
|
|
<
>
|
>
>
>

<
<
<
|
>
>
>
>
>
>
>







1574
1575
1576
1577
1578
1579
1580
1581
1582
1583

1584
1585
1586
1587

1588
1589
1590
1591
1592
1593
1594
1595
1596
1597




1598



1599
1600
1601
1602
1603
1604
1605
1606
....
1632
1633
1634
1635
1636
1637
1638



1639
1640
1641
1642
1643
1644
1645
....
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748




1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762

1763
1764
1765
1766
1767
1768



1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
}

/*
** Attempt the transfer optimization on INSERTs of the form
**
**     INSERT INTO tab1 SELECT * FROM tab2;
**
** The xfer optimization transfers raw records from tab2 over to tab1.  
** Columns are not decoded and reassemblied, which greatly improves
** performance.  Raw index records are transferred in the same way.

**
** The xfer optimization is only attempted if tab1 and tab2 are compatible.
** There are lots of rules for determining compatibility - see comments
** embedded in the code for details.

**
** This routine returns TRUE if the optimization is guaranteed to be used.
** Sometimes the xfer optimization will only work if the destination table
** is empty - a factor that can only be determined at run-time.  In that
** case, this routine generates code for the xfer optimization but also
** does a test to see if the destination table is empty and jumps over the
** xfer optimization code if the test fails.  In that case, this routine
** returns FALSE so that the caller will know to go ahead and generate
** an unoptimized transfer.  This routine also returns FALSE if there
** is no chance that the xfer optimization can be applied.




**



** This optimization is particularly useful at making VACUUM run faster.
*/
static int xferOptimization(
  Parse *pParse,        /* Parser context */
  Table *pDest,         /* The table we are inserting into */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  int onError,          /* How to handle constraint errors */
  int iDbDest           /* The database of pDest */
................................................................................
    return 0;   /* tab1 must not be a virtual table */
  }
#endif
  if( onError==OE_Default ){
    if( pDest->iPKey>=0 ) onError = pDest->keyConf;
    if( onError==OE_Default ) onError = OE_Abort;
  }



  assert(pSelect->pSrc);   /* allocated even if there is no FROM clause */
  if( pSelect->pSrc->nSrc!=1 ){
    return 0;   /* FROM clause must have exactly one term */
  }
  if( pSelect->pSrc->a[0].pSelect ){
    return 0;   /* FROM clause cannot contain a subquery */
  }
................................................................................
  ** not worth the effort.  Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
  */
  if( (pParse->db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){
    return 0;
  }
#endif
  if( (pParse->db->flags & SQLITE_CountRows)!=0 ){
    return 0;  /* xfer opt does not play well with PRAGMA count_changes */
  }

  /* If we get this far, it means that the xfer optimization is at
  ** least a possibility, though it might only work if the destination
  ** table (tab1) is initially empty.




  */
#ifdef SQLITE_TEST
  sqlite3_xferopt_count++;
#endif
  iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
  v = sqlite3GetVdbe(pParse);
  sqlite3CodeVerifySchema(pParse, iDbSrc);
  iSrc = pParse->nTab++;
  iDest = pParse->nTab++;
  regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
  sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
  if( (pDest->iPKey<0 && pDest->pIndex!=0)          /* (1) */
   || destHasUniqueIdx                              /* (2) */
   || (onError!=OE_Abort && onError!=OE_Rollback)   /* (3) */

  ){
    /* In some circumstances, we are able to run the xfer optimization
    ** only if the destination table is initially empty.  This code makes
    ** that determination.  Conditions under which the destination must
    ** be empty:
    **



    ** (1) There is no INTEGER PRIMARY KEY but there are indices.
    **     (If the destination is not initially empty, the rowid fields
    **     of index entries might need to change.)
    **
    ** (2) The destination has a unique index.  (The xfer optimization 
    **     is unable to test uniqueness.)
    **
    ** (3) onError is something other than OE_Abort and OE_Rollback.
    */
    addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
    emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
    sqlite3VdbeJumpHere(v, addr1);
  }else{
    emptyDestTest = 0;
  }

Changes to test/insert4.test.

499
500
501
502
503
504
505
506









507















































508
  catchsql {COMMIT}
} {1 {cannot commit - no transaction is active}}
do_test insert4-8.11 {
  execsql {
    SELECT * FROM t1;
  }
} {1 2} 


























































finish_test








>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
  catchsql {COMMIT}
} {1 {cannot commit - no transaction is active}}
do_test insert4-8.11 {
  execsql {
    SELECT * FROM t1;
  }
} {1 2} 

do_test insert4-8.21 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
    INSERT INTO t2 VALUES(1,3);
    INSERT INTO t1 SELECT * FROM t2;
    SELECT * FROM t1;
  }
} {1 3}
do_test insert4-8.22 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
    INSERT INTO t2 VALUES(1,3);
    INSERT INTO t1 SELECT * FROM t2;
    SELECT * FROM t1;
  }
} {1 3}
do_test insert4-8.23 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
    INSERT INTO t2 VALUES(1,3);
    INSERT INTO t1 SELECT * FROM t2;
    SELECT * FROM t1;
  }
} {1 3}
do_test insert4-8.24 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
    INSERT INTO t2 VALUES(1,3);
    INSERT INTO t1 SELECT * FROM t2;
    SELECT * FROM t1;
  }
} {1 3}
do_test insert4-8.25 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
    INSERT INTO t2 VALUES(1,3);
    INSERT INTO t1 SELECT * FROM t2;
    SELECT * FROM t1;
  }
} {1 3}


finish_test