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
