Re: [sqlite] INSERT vs BEGIN
Rob Richardson wrote: > I didn't know it is possible to insert multiple rows into a table using a > command like this. Is this just an SQLite feature, or is this part of the > SQL standard? This is defined since SQL-92, but only at the Full SQL conformance level. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT vs BEGIN
On 9/3/19 7:18 AM, Dominique Devienne wrote: > On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson > wrote: > >> I didn't know it is possible to insert multiple rows into a table using a >> command like this. > > Added over 7 years ago: See > https://www.sqlite.org/changes.html#version_3_7_11 #1 > > >> Is this just an SQLite feature, or is this part of the SQL standard? > > I suspect it's non-standard, since Oracle does not support it. But it's > just a guess on my part. --DD Many databases I have used include it, so I thought it was standard (if not universally supported, but that is somewhat common with SQL) -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT vs BEGIN
On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson wrote: > I didn't know it is possible to insert multiple rows into a table using a > command like this. Added over 7 years ago: See https://www.sqlite.org/changes.html#version_3_7_11 #1 > Is this just an SQLite feature, or is this part of the SQL standard? I suspect it's non-standard, since Oracle does not support it. But it's just a guess on my part. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT vs BEGIN
I didn't know it is possible to insert multiple rows into a table using a command like this. Is this just an SQLite feature, or is this part of the SQL standard? RobR On Mon, Sep 2, 2019 at 8:14 AM Dominique Devienne wrote: > On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin wrote: > > > > One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" > > because others threads needs to access to tables. > > SQLite copes very well when you have one connection writing to the > > database and other connections reading. The problems come when you have > > two connections writing to the database at once. > > > > In WAL mode only! Otherwise readers are blocked when the writer is active, > and readers prevent the writer from proceeding. --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT vs BEGIN
On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin wrote: > > One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" > because others threads needs to access to tables. > SQLite copes very well when you have one connection writing to the > database and other connections reading. The problems come when you have > two connections writing to the database at once. > In WAL mode only! Otherwise readers are blocked when the writer is active, and readers prevent the writer from proceeding. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT vs BEGIN
On Mon, Sep 2, 2019 at 12:04 PM Grincheux <51...@protonmail.ch> wrote:
> What is the best ?
>
> INSERT INTO artists (name) VALUES
> ("Gene Vincent") ...
> ("Moi _ Me");
>
You're missing commas.
And you should not use double-quotes but single-quotes for string-literals.
> I want to insert 1 000 000 records.
> The other manner tot do is creating a transaction with one insert command
> by line.
>
In all cases, you should have as few transactions as possible IMHO,
typically a single one.
Whether to have one statement per-row or one-statement for multiple-rows is
separate from transactions.
The multi-row insert statement might hit the parser limit if too large.
I've converted in the past a very large
insert-per-row SQL file, to one-insert-per-table (for all that table's row)
and ran into that limit. So I gave up,
since hard to know how many rows to put per statement.
And as Gunter wrote, SQLite will need to parse each statement in full in
memory, so the larger the statement
the more memory used.
If you're doing massive inserts from text files, maybe from a CSV rather
than SQL file might be faster.
You're trading one parser (SQL) for another (CSV), but since DRH wrote
both, and CSV is significantly
simpler than SQL (for parsing), it's possible CSV might have an edge. But
that remains to be seen.
> My question is what is the best thing to do ?
> Or having a transaction for the first sample?
>
> One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because
> others threads needs to access to tables.
>
Unless you're using WAL, other threads will be blocked during inserts,
whether you use a
single transactions, or not. They might be able to "sneak-in" betweeb
inserts TX if using multiple TXs,
but then you might block the "inserter".
> Please help me.
>
Programmatically, prepare() and bind() as Gunter wrote again. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT vs BEGIN
On 1 Sep 2019, at 7:27am, Grincheux <51...@protonmail.ch> wrote:
> INSERT INTO artists (name) VALUES
> ("Gene Vincent")
> ("John Lennon")
> ("Ringo Starr")
> ("Paul McCartney")
> .
> .
> .
> ("Moi _ Me");
>
> I want to insert 1 000 000 records.
SQLite has to parse the entire command line before it can execute any of it.
If you use a single long line, SQLite will use up a lot of memory to store that
entire line until it can start to execute it. Also, once SQLite begins to
execute that line the database will be locked for a very long time, since one
INSDRT command is executed in one lock.
It is probably better to do your insertions as lots of short commands instead.
If you are binding parameters, you can prepare one INSERT command and just
rebind the text each time, which will save you a little time and a lot of
processing.
> One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because
> others threads needs to access to tables.
SQLite copes very well when you have one connection writing to the database and
other connections reading. The problems come when you have two connections
writing to the database at once.
If you have a million rows to insert at once, it is normal to put them in
transaction batches of, say, a hundred or a thousand. So you start with BEGIN;
, then every thousand you do END;BEGIN; again, then end with END .
Remember to set a timeout of a few minutes on /every/ connection to the
database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT vs BEGIN
What is the best ?
INSERT INTO artists (name) VALUES
("Gene Vincent")
("John Lennon")
("Ringo Starr")
("Paul McCartney")
.
.
.
("Moi _ Me");
I want to insert 1 000 000 records.
The other manner tot do is creating a transaction with one insert command by
line.
My question is what is the best thing to do ?
Or having a transaction for the first sample?
One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because
others threads needs to access to tables.
Please help me.
Grincheux
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
