FYI: When this multi-row-insert patch is combined with today's CVS OP_Real and OP_Int64 fix for Tickets #2733 and #2731 (Check-in [4507]), the multi-row ASCII INSERT statements are as much as 25% faster than the equivalent number of traditional individual ASCII INSERT statements within a transaction.
i.e., -- this is 25% faster... INSERT INTO BIGTABLE VALUES (-334712687065.09, -334712687065.12, -334712687065.13), (-334712687065.09, -334712687065.12, -334712687065.13), ... 9996 similar rows ... (-334712687065.09, -334712687065.12, -334712687065.13), (-334712687065.09, -334712687065.12, -334712687065.13); -- ...than this BEGIN INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13); INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13); ... 9996 similar rows ... INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13); INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13); COMMIT; I also had to bump up the SQLITE_MAX_SQL_LENGTH define to 50000000 to get this to work. The caveat is that you can't have more than approximately 10,000 to 15,000 rows per multi-insert statement due to sqlite3Select -> multiSelect -> sqlite3Select recursion leading to stack overflow. This compound select recursion limit could be increased by a factor of 100 or more by rewriting these 2 functions to use heap-allocated stack frame structs to store all function local variables. Or with more work, eliminate the compound select stack limitation altogether by using a loop instead of recursion for multiSelect compound select chain vdbe code generation. --- Joe Wilson <[EMAIL PROTECTED]> wrote: > The attached patch implements the compound (multi-row) INSERT statement > syntax against the SQLite 3.5.1 source tree. Both named and unnamed > INSERT column syntax with DEFAULT column values are supported. > > The patch transforms multi-row INSERT statements into compound SELECT > statements separated by UNION ALLs. It should be fairly efficient, > as all rows within the compound INSERT are inserted within a single > implicit transaction. > > SQLITE_MAX_COMPOUND_SELECT in sqliteLimit.h also serves as the maximum > number of rows in a multi-row INSERT. > > No regressions in "make test". Post any problems to the mailing list, > and I'll try to keep this patch up to date. > > This patch is hereby placed in the public domain. > > Examples: > > SQLite version 3.5.1 > Enter ".help" for instructions > sqlite> CREATE TABLE foo(a DEFAULT 123, b DEFAULT 'Bob'); > sqlite> insert into foo values (1,'Ace'), (2,'Two'), (3,'Three'); > sqlite> select * from foo; > 1|Ace > 2|Two > 3|Three > sqlite> delete from foo; > sqlite> insert into foo(b,a) values (1,'Ace'), (2,'Two'), (3,'Three'); > sqlite> select * from foo; > Ace|1 > Two|2 > Three|3 > sqlite> delete from foo; > sqlite> insert into foo(a) values (10), (20), (30); > sqlite> select * from foo; > 10|Bob > 20|Bob > 30|Bob > > > Index: src/parse.y > =================================================================== > RCS file: /sqlite/sqlite/src/parse.y,v > retrieving revision 1.234 > diff -u -3 -p -r1.234 parse.y > --- src/parse.y 21 Aug 2007 10:44:16 -0000 1.234 > +++ src/parse.y 14 Oct 2007 07:29:32 -0000 > @@ -624,6 +624,42 @@ inscollist(A) ::= inscollist(X) COMMA nm > inscollist(A) ::= nm(Y). > {A = sqlite3IdListAppend(pParse->db,0,&Y);} > > +%ifndef SQLITE_OMIT_COMPOUND_INSERT > +%ifndef SQLITE_OMIT_COMPOUND_SELECT > + > +%type inslistItem {Select*} > +%destructor inslistItem {sqlite3SelectDelete($$);} > + > +inslistItem(A) ::= LP itemlist(W) RP. { > + SrcList *pSrc = sqlite3DbMallocZero(pParse->db, sizeof(SrcList)); > + A = sqlite3SelectNew(pParse,W,pSrc,0,0,0,0,0,0,0); > +} > + > +%type inslist {Select*} > +%destructor inslist {sqlite3SelectDelete($$);} > + > +%type comma_opt {int} > +comma_opt(X) ::= . {X = 0;} > +comma_opt(X) ::= COMMA. {X = 1;} > + > +inslist(A) ::= inslistItem(Z) COMMA. {A = Z;} > +inslist(A) ::= inslist(X) inslistItem(Z) comma_opt(C). { > + C = C; > + if( Z ){ > + Z->op = TK_ALL; > + Z->pPrior = X; > + }else{ > + sqlite3SelectDelete(X); > + } > + A = Z; > +} > + > +cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) VALUES inslist(S). > + {sqlite3Insert(pParse, X, 0, S, F, R);} > + > +%endif SQLITE_OMIT_COMPOUND_SELECT > +%endif SQLITE_OMIT_COMPOUND_INSERT > + > /////////////////////////// Expression Processing > ///////////////////////////// > // > > Index: src/sqliteLimit.h > =================================================================== > RCS file: /sqlite/sqlite/src/sqliteLimit.h,v > retrieving revision 1.2 > diff -u -3 -p -r1.2 sqliteLimit.h > --- src/sqliteLimit.h 24 Aug 2007 11:52:29 -0000 1.2 > +++ src/sqliteLimit.h 14 Oct 2007 07:29:33 -0000 > @@ -75,7 +75,7 @@ > ** any limit on the number of terms in a compount SELECT. > */ > #ifndef SQLITE_MAX_COMPOUND_SELECT > -# define SQLITE_MAX_COMPOUND_SELECT 500 > +# define SQLITE_MAX_COMPOUND_SELECT 5000000 > #endif > > /* __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------