Re: INSERT vs BEGIN

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: INSERT vs BEGIN

Simon Slavin-3
On 1 Sep 2019, at 7:27am, Grincheux <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT vs BEGIN

Dominique Devienne
On Mon, Sep 2, 2019 at 12:04 PM Grincheux <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT vs BEGIN

Dominique Devienne
In reply to this post by Simon Slavin-3
On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT vs BEGIN

CedricCicada
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 <[hidden email]>
wrote:

> On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT vs BEGIN

Dominique Devienne
On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT vs BEGIN

Richard Damon
On 9/3/19 7:18 AM, Dominique Devienne wrote:

> On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson <[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT vs BEGIN

Clemens Ladisch
In reply to this post by CedricCicada
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users