Slightly faster INSERTs from a SELECT by avoiding an intermediate table.
But it didn't make nearly as much difference as I had hoped. (CVS 732)
FossilOrigin-Name: 723362e74f79c784314d042e3a8c8a9bf07cbd5e
diff --git a/src/insert.c b/src/insert.c
index b5d74f1..fc2d5cc 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -12,7 +12,7 @@
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
-** $Id: insert.c,v 1.65 2002/07/31 00:32:50 drh Exp $
+** $Id: insert.c,v 1.66 2002/08/28 03:00:58 drh Exp $
*/
#include "sqliteInt.h"
@@ -30,6 +30,58 @@
** statement above, and pSelect is NULL. For the second form, pList is
** NULL and pSelect is a pointer to the select statement used to generate
** data for the insert.
+**
+** The code generated follows one of three templates. For a simple
+** select with data coming from a VALUES clause, the code executes
+** once straight down through. The template looks like this:
+**
+** open write cursor to <table> and its indices
+** puts VALUES clause expressions onto the stack
+** write the resulting record into <table>
+** cleanup
+**
+** If the statement is of the form
+**
+** INSERT INTO <table> SELECT ...
+**
+** And the SELECT clause does not read from <table> at any time, then
+** the generated code follows this template:
+**
+** goto B
+** A: setup for the SELECT
+** loop over the tables in the SELECT
+** gosub C
+** end loop
+** cleanup after the SELECT
+** goto D
+** B: open write cursor to <table> and its indices
+** goto A
+** C: insert the select result into <table>
+** return
+** D: cleanup
+**
+** The third template is used if the insert statement takes its
+** values from a SELECT but the data is being inserted into a table
+** that is also read as part of the SELECT. In the third form,
+** we have to use a intermediate table to store the results of
+** the select. The template is like this:
+**
+** goto B
+** A: setup for the SELECT
+** loop over the tables in the SELECT
+** gosub C
+** end loop
+** cleanup after the SELECT
+** goto D
+** C: insert the select result into the intermediate table
+** return
+** B: open a cursor to an intermediate table
+** goto A
+** D: open write cursor to <table> and its indices
+** loop over the intermediate table
+** transfer values form intermediate table into <table>
+** end the loop
+** cleanup
*/
void sqliteInsert(
Parse *pParse, /* Parser context */
@@ -44,7 +96,6 @@
int i, j, idx; /* Loop counters */
Vdbe *v; /* Generate code into this virtual machine */
Index *pIdx; /* For looping over indices of the table */
- int srcTab; /* Date comes from this temporary cursor if >=0 */
int nColumn; /* Number of columns in the data */
int base; /* First available cursor */
int iCont, iBreak; /* Beginning and end of the loop over srcTab */
@@ -52,6 +103,12 @@
int openOp; /* Opcode used to open cursors */
int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
int endOfLoop; /* Label for the end of the insertion loop */
+ int useTempTable; /* Store SELECT results in intermediate table */
+ int srcTab; /* Data comes from this temporary cursor if >=0 */
+ int iSelectLoop; /* Address of code that implements the SELECT */
+ int iCleanup; /* Address of the cleanup code */
+ int iInsertBlock; /* Address of the subroutine used to insert data */
+ int iCntMem; /* Memory cell used for the row counter */
int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
int newIdx = -1;
@@ -111,23 +168,66 @@
}
/* Figure out how many columns of data are supplied. If the data
- ** is coming from a SELECT statement, then this step has to generate
- ** all the code to implement the SELECT statement and leave the data
- ** in a temporary table. If data is coming from an expression list,
- ** then we just have to count the number of expressions.
+ ** is coming from a SELECT statement, then this step also generates
+ ** all the code to implement the SELECT statement and invoke a subroutine
+ ** to process each row of the result. (Template 2.) If the SELECT
+ ** statement uses the the table that is being inserted into, then the
+ ** subroutine is also coded here. That subroutine stores the SELECT
+ ** results in a temporary table. (Template 3.)
*/
if( pSelect ){
- int rc;
- srcTab = pParse->nTab++;
- sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0);
- rc = sqliteSelect(pParse, pSelect, SRT_Table, srcTab, 0,0,0);
+ /* Data is coming from a SELECT. Generate code to implement that SELECT
+ */
+ int rc, iInitCode;
+ int opCode;
+ iInitCode = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
+ iSelectLoop = sqliteVdbeCurrentAddr(v);
+ iInsertBlock = sqliteVdbeMakeLabel(v);
+ rc = sqliteSelect(pParse, pSelect, SRT_Subroutine, iInsertBlock, 0,0,0);
if( rc || pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
+ iCleanup = sqliteVdbeMakeLabel(v);
+ sqliteVdbeAddOp(v, OP_Goto, 0, iCleanup);
assert( pSelect->pEList );
nColumn = pSelect->pEList->nExpr;
+
+ /* Set useTempTable to TRUE if the result of the SELECT statement
+ ** should be written into a temporary table. Set to FALSE if each
+ ** row of the SELECT can be written directly into the result table.
+ */
+ opCode = pTab->isTemp ? OP_OpenTemp : OP_Open;
+ useTempTable = row_triggers_exist || sqliteVdbeFindOp(v,opCode,pTab->tnum);
+
+ if( useTempTable ){
+ /* Generate the subroutine that SELECT calls to process each row of
+ ** the result. Store the result in a temporary table
+ */
+ srcTab = pParse->nTab++;
+ sqliteVdbeResolveLabel(v, iInsertBlock);
+ sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
+ sqliteVdbeAddOp(v, OP_NewRecno, srcTab, 0);
+ sqliteVdbeAddOp(v, OP_Pull, 1, 0);
+ sqliteVdbeAddOp(v, OP_PutIntKey, srcTab, 0);
+ sqliteVdbeAddOp(v, OP_Return, 0, 0);
+
+ /* The following code runs first because the GOTO at the very top
+ ** of the program jumps to it. Create the temporary table, then jump
+ ** back up and execute the SELECT code above.
+ */
+ sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
+ sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0);
+ sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
+ sqliteVdbeResolveLabel(v, iCleanup);
+ }else{
+ sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
+ }
}else{
+ /* This is the case if the data for the INSERT is coming from a VALUES
+ ** clause
+ */
SrcList dummy;
assert( pList!=0 );
srcTab = -1;
+ useTempTable = 0;
assert( pList );
nColumn = pList->nExpr;
dummy.nSrc = 0;
@@ -208,15 +308,18 @@
keyColumn = pTab->iPKey;
}
- /* Open the temp table for FOR EACH ROW triggers */
+ /* Open the temp table for FOR EACH ROW triggers
+ */
if( row_triggers_exist ){
sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0);
}
/* Initialize the count of rows to be inserted
*/
- if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
- sqliteVdbeAddOp(v, OP_Integer, 0, 0); /* Initialize the row count */
+ if( db->flags & SQLITE_CountRows ){
+ iCntMem = pParse->nMem++;
+ sqliteVdbeAddOp(v, OP_Integer, 0, 0);
+ sqliteVdbeAddOp(v, OP_MemStore, iCntMem, 1);
}
/* Open tables and indices if there are no row triggers */
@@ -232,15 +335,18 @@
pParse->nTab += idx;
}
- /* If the data source is a SELECT statement, then we have to create
+ /* If the data source is a temporary table, then we have to create
** a loop because there might be multiple rows of data. If the data
- ** source is an expression list, then exactly one row will be inserted
- ** and the loop is not used.
+ ** source is a subroutine call from the SELECT statement, then we need
+ ** to launch the SELECT statement processing.
*/
- if( srcTab>=0 ){
+ if( useTempTable ){
iBreak = sqliteVdbeMakeLabel(v);
sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
iCont = sqliteVdbeCurrentAddr(v);
+ }else if( pSelect ){
+ sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
+ sqliteVdbeResolveLabel(v, iInsertBlock);
}
endOfLoop = sqliteVdbeMakeLabel(v);
@@ -259,8 +365,10 @@
if( pColumn && j>=pColumn->nId ){
sqliteVdbeAddOp(v, OP_String, 0, 0);
sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
- }else if( srcTab>=0 ){
+ }else if( useTempTable ){
sqliteVdbeAddOp(v, OP_Column, srcTab, j);
+ }else if( pSelect ){
+ sqliteVdbeAddOp(v, OP_Dup, nColumn-j-1, 1);
}else{
sqliteExprCode(pParse, pList->a[j].pExpr);
}
@@ -296,8 +404,10 @@
*/
if( !pTab->pSelect ){
if( keyColumn>=0 ){
- if( srcTab>=0 ){
+ if( useTempTable ){
sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
+ }else if( pSelect ){
+ sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
}else{
sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
}
@@ -334,8 +444,10 @@
if( pColumn && j>=pColumn->nId ){
sqliteVdbeAddOp(v, OP_String, 0, 0);
sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
- }else if( srcTab>=0 ){
+ }else if( useTempTable ){
sqliteVdbeAddOp(v, OP_Column, srcTab, j);
+ }else if( pSelect ){
+ sqliteVdbeAddOp(v, OP_Dup, i+nColumn-j, 1);
}else{
sqliteExprCode(pParse, pList->a[j].pExpr);
}
@@ -349,8 +461,8 @@
/* Update the count of rows that are inserted
*/
- if( (db->flags & SQLITE_CountRows)!=0 && !pParse->trigStack){
- sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
+ if( (db->flags & SQLITE_CountRows)!=0 ){
+ sqliteVdbeAddOp(v, OP_MemIncr, iCntMem, 0);
}
}
@@ -373,10 +485,14 @@
/* The bottom of the loop, if the data source is a SELECT statement
*/
sqliteVdbeResolveLabel(v, endOfLoop);
- if( srcTab>=0 ){
+ if( useTempTable ){
sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
sqliteVdbeResolveLabel(v, iBreak);
sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
+ }else if( pSelect ){
+ sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
+ sqliteVdbeAddOp(v, OP_Return, 0, 0);
+ sqliteVdbeResolveLabel(v, iCleanup);
}
if( !row_triggers_exist ){
@@ -392,10 +508,11 @@
/*
** Return the number of rows inserted.
*/
- if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
+ if( db->flags & SQLITE_CountRows ){
sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0);
sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
sqliteVdbeChangeP3(v, -1, "rows inserted", P3_STATIC);
+ sqliteVdbeAddOp(v, OP_MemLoad, iCntMem, 0);
sqliteVdbeAddOp(v, OP_Callback, 1, 0);
}
diff --git a/src/select.c b/src/select.c
index cc9a7b5..4cbf034 100644
--- a/src/select.c
+++ b/src/select.c
@@ -12,7 +12,7 @@
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
-** $Id: select.c,v 1.110 2002/08/25 19:20:40 drh Exp $
+** $Id: select.c,v 1.111 2002/08/28 03:00:59 drh Exp $
*/
#include "sqliteInt.h"
@@ -520,6 +520,14 @@
break;
}
+ /* Invoke a subroutine to handle the results. The subroutine itself
+ ** is responsible for popping the results off of the stack.
+ */
+ case SRT_Subroutine: {
+ sqliteVdbeAddOp(v, OP_Gosub, 0, iParm);
+ break;
+ }
+
/* Discard the results. This is used for SELECT statements inside
** the body of a TRIGGER. The purpose of such selects is to call
** user-defined functions that have side effects. We do not care
@@ -1075,7 +1083,6 @@
int rc; /* Success code from a subroutine */
Select *pPrior; /* Another SELECT immediately to our left */
Vdbe *v; /* Generate code to this VDBE */
- int base; /* Baseline value for pParse->nTab */
/* Make sure there is no ORDER BY clause on prior SELECTs. Only the
** last SELECT in the series may have an ORDER BY.
@@ -1103,7 +1110,6 @@
/* Generate code for the left and right SELECT statements.
*/
- base = pParse->nTab;
switch( p->op ){
case TK_ALL: {
if( p->pOrderBy==0 ){
@@ -1258,7 +1264,6 @@
pParse->nErr++;
return 1;
}
- pParse->nTab = base;
return 0;
}
@@ -2080,7 +2085,7 @@
** successful coding of the SELECT.
*/
select_end:
- pParse->nTab = base;
+ /* pParse->nTab = base; */
sqliteAggregateInfoReset(pParse);
return rc;
}
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 912ef83..3ab867c 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
*************************************************************************
** Internal interface definitions for SQLite.
**
-** @(#) $Id: sqliteInt.h,v 1.143 2002/08/24 18:24:55 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.144 2002/08/28 03:00:59 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
@@ -610,6 +610,7 @@
#define SRT_TempTable 8 /* Store result in a trasient table */
#define SRT_Discard 9 /* Do not save the results anywhere */
#define SRT_Sorter 10 /* Store results in the sorter */
+#define SRT_Subroutine 11 /* Call a subroutine to handle results */
/*
** When a SELECT uses aggregate functions (like "count(*)" or "avg(f1)")
diff --git a/src/vdbe.c b/src/vdbe.c
index dd6c973..e7831f2 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -30,7 +30,7 @@
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
-** $Id: vdbe.c,v 1.172 2002/08/26 19:55:08 drh Exp $
+** $Id: vdbe.c,v 1.173 2002/08/28 03:01:00 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -540,6 +540,18 @@
}
/*
+** Search for the current program for the given opcode and P2
+** value. Return 1 if found and 0 if not found.
+*/
+int sqliteVdbeFindOp(Vdbe *p, int op, int p2){
+ int i;
+ for(i=0; i<p->nOp; i++){
+ if( p->aOp[i].opcode==op && p->aOp[i].p2==p2 ) return 1;
+ }
+ return 0;
+}
+
+/*
** The following group or routines are employed by installable functions
** to return their results.
**
@@ -5231,7 +5243,7 @@
}
sqliteBtreeCommitCkpt(pBt);
if( db->pBeTemp ) sqliteBtreeCommitCkpt(db->pBeTemp);
- assert( p->tos<pc );
+ assert( p->tos<pc || sqlite_malloc_failed==1 );
return rc;
/* Jump to here if a malloc() fails. It's hard to get a malloc()
diff --git a/src/vdbe.h b/src/vdbe.h
index e35bf2c..39a2aa5 100644
--- a/src/vdbe.h
+++ b/src/vdbe.h
@@ -15,7 +15,7 @@
** or VDBE. The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
-** $Id: vdbe.h,v 1.58 2002/08/25 19:20:42 drh Exp $
+** $Id: vdbe.h,v 1.59 2002/08/28 03:01:01 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
@@ -231,6 +231,7 @@
void sqliteVdbeChangeP2(Vdbe*, int addr, int P2);
void sqliteVdbeChangeP3(Vdbe*, int addr, const char *zP1, int N);
void sqliteVdbeDequoteP3(Vdbe*, int addr);
+int sqliteVdbeFindOp(Vdbe*, int, int);
int sqliteVdbeMakeLabel(Vdbe*);
void sqliteVdbeDelete(Vdbe*);
int sqliteVdbeOpcode(const char *zName);
diff --git a/src/where.c b/src/where.c
index c7ea568..96d476e 100644
--- a/src/where.c
+++ b/src/where.c
@@ -13,7 +13,7 @@
** the WHERE clause of SQL statements. Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
-** $Id: where.c,v 1.63 2002/08/15 13:50:50 drh Exp $
+** $Id: where.c,v 1.64 2002/08/28 03:01:01 drh Exp $
*/
#include "sqliteInt.h"
@@ -1102,9 +1102,11 @@
sqliteVdbeAddOp(v, OP_Close, pLevel->iCur, 0);
}
}
+#if 0 /* Never reuse a cursor */
if( pWInfo->pParse->nTab==pWInfo->peakNTab ){
pWInfo->pParse->nTab = pWInfo->savedNTab;
}
+#endif
sqliteFree(pWInfo);
return;
}