Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

Clemens Ladisch writes: > Sarge Borsch wrote: >> time xzdec something.sql.xz | sqlite3 something.db > > This measures only xzdec; it does not catch anything that sqlite3 does > after xzdec has finished and closed the pipe. Nitpick: In bash or zsh, `time` is handled by shell,

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row On Tuesday, 30 May, 2017 10:33, R Smith <rsm...@rsweb.co.za> said: > Keith,

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

On Tuesday, 30 May, 2017 10:33, R Smith said: > Keith, I think the OP meant he inserted the values using one single > statement, not one single transaction, as in he did one ginormous INSERT > INTO t(v1, v2...) VALUES (1, 2),(2,3),(..),,(1, > 297829872); -

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

Keith, I think the OP meant he inserted the values using one single statement, not one single transaction, as in he did one ginormous INSERT INTO t(v1, v2...) VALUES (1, 2),(2,3),(..),,(1, 297829872); - 180MB or so worth... Probably lots of data in few rows, because he is not

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

On 2017/05/30 2:01 PM, Hick Gunter wrote: If you stuff all 18MB of your data into a single INSERT, then SQlite will need to generate a single program that contains all 18MB of your data (plus code to build rows aout of that). This will put a heavy strain on memory requirements and offset any

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

I find quite the opposite. Using a DUMP file to create a database where the first test uses the standard dump (which does the load in a single transaction) is enormously faster than the second one, where the BEGIN TRANSACTION and COMMIT have been commented out, and thus each insert is

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

If you stuff all 18MB of your data into a single INSERT, then SQlite will need to generate a single program that contains all 18MB of your data (plus code to build rows aout of that). This will put a heavy strain on memory requirements and offset any speed you hope to gain. The SOP is to put

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

Sarge Borsch wrote: > time xzdec something.sql.xz | sqlite3 something.db This measures only xzdec; it does not catch anything that sqlite3 does after xzdec has finished and closed the pipe. > IMO sqlite needs some optimisation for this case when there’s a huge > INSERT statement, because the