Re: [sqlite] Inserting Multiple Rows in a single statement

[Alaric Snell-Pym]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/30/2011 03:41 PM, Pavel Ivanov wrote:
>> I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at
>> possibilities of making the insertions faster.
>
> Prepare your statement in the form
>
> INSERT INTO table_name (val1, val2, ...) VALUES (?1, ?2, ...)
>
> Then for each row you want to insert you will bind necessary values,
> execute statement and reset. With all that wrapped in transaction
> there's no way to make insertions any faster.

If you have lots of indexes, then for really big inserts (where the
ratio of rows added to rows already existing is high), it might be
quicker to drop the indexes, do the inserts, then put them back
(especially with the new faster CREATE INDEX).

Failing that, if you have a block of inserts to do, sorting them by an
indexed column (especially primary key) before insertion might boost
throughput a bit by increasing locality of reference in the B-Trees.

Aside: I was talking about B-Trees with some colleagues when my six year
old daughter (then five, I think, actually) butted in and said they'd
learnt about B-Trees in school that day. Sadly, it was some spelling
game, rather than the school getting a bit of computer science in early...

There are index structures that support faster insertions than B-Trees,
at the cost of slightly slower selects: streaming merge trees in
particular have come to my attention:

http://www.acunu.com/blogs/tom-wilkie/castle-storage-engine-oscon/

Perhaps future versions of SQLite might support some of this crazy stuff :-)

ABS

- --
Alaric Snell-Pym
http://www.snell-pym.org.uk/alaric/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk7WUZwACgkQRgz/WHNxCGoSkQCcCwZ7WzAPsUKMGnN2ZiTJ5AoB
xicAn1sgb60s40XQPlYIXdMNmfRoOBxk
=DOWQ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting Multiple Rows in a single statement

[Pavel Ivanov]

> I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at
> possibilities of making the insertions faster.

Prepare your statement in the form

INSERT INTO table_name (val1, val2, ...) VALUES (?1, ?2, ...)

Then for each row you want to insert you will bind necessary values,
execute statement and reset. With all that wrapped in transaction
there's no way to make insertions any faster. Even if SQLite supported
the multi-row insert syntax it would do exactly the same operations -
bind values, insert row, reset, bind values, insert row, reset.


Pavel


On Wed, Nov 30, 2011 at 2:52 AM, Sreekumar TP  wrote:
> Hi,
>
> I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at
> possibilities of making the insertions faster.
>
> -Sreekumar
>
> On Tue, Nov 29, 2011 at 4:36 PM, Donald Griggs  wrote:
>
>> Sreekumar,
>>
>> Regarding:
>> >
>> > Is it possible to insert multiple rows using a single statement ?
>> >
>>
>> You might want to let us know your reasons for requesting this.
>>
>> If it's speed of insertion you're after, then be sure to put many INSERT's
>> into each transaction. That is, be sure to surround a batch of, say, 1000
>> INSERT's with BEGIN and END statements.
>>
>> http://sqlite.org/lang_transaction.html
>>  ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting Multiple Rows in a single statement

[Sreekumar TP]

Hi,

I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at
possibilities of making the insertions faster.

-Sreekumar

On Tue, Nov 29, 2011 at 4:36 PM, Donald Griggs  wrote:

> Sreekumar,
>
> Regarding:
> >
> > Is it possible to insert multiple rows using a single statement ?
> >
>
> You might want to let us know your reasons for requesting this.
>
> If it's speed of insertion you're after, then be sure to put many INSERT's
> into each transaction. That is, be sure to surround a batch of, say, 1000
> INSERT's with BEGIN and END statements.
>
> http://sqlite.org/lang_transaction.html
>  ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting Multiple Rows in a single statement

[Donald Griggs]

Sreekumar,

Regarding:
>
> Is it possible to insert multiple rows using a single statement ?
>

You might want to let us know your reasons for requesting this.

If it's speed of insertion you're after, then be sure to put many INSERT's
into each transaction. That is, be sure to surround a batch of, say, 1000
INSERT's with BEGIN and END statements.

http://sqlite.org/lang_transaction.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting Multiple Rows in a single statement

[Petite Abeille]

On Nov 29, 2011, at 10:47 AM, Darren Duncan wrote:

>> Is it possible to insert multiple rows using a single statement ?
> 
> Yes.
> 
> INSERT INTO foo (x, y)
> VALUES (1,2), (3,4), (5,6),...;

I don't think this syntax is supported by SQLite:

http://www.sqlite.org/lang_insert.html

> 
> INSERT INTO foo (x,y)
> SELECT x, y FROM bar;
> 
> That's at least 2 ways.

Additionally, as variation of that option, without a source table (SQLite 
specific syntax):

insert
intofoo
(
  x,
  y
)

select  1 as x,
2 as y

union all
select  3 as x,
4 as y

union all
select  5 as x,
6 as y



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting Multiple Rows in a single statement

[Darren Duncan]

Sreekumar TP wrote:

Is it possible to insert multiple rows using a single statement ?


Yes.

INSERT INTO foo (x, y)
VALUES (1,2), (3,4), (5,6),...;

INSERT INTO foo (x,y)
SELECT x, y FROM bar;

That's at least 2 ways.

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users