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 performed in its own transaction. The structure does have the indexes created while loading ...
>wc -l xmltv.sql 5425040 xmltv.sql 2017-05-30 09:43 446,700,424 xmltv.sql With the commands all being loaded in a single transaction: >timethis sqlite test1.db < xmltv.sql TimeThis : Command Line : sqlite test1.db TimeThis : Start Time : Tue May 30 09:41:12 2017 TimeThis : End Time : Tue May 30 09:42:14 2017 TimeThis : Elapsed Time : 00:01:02.005 With the commands being in their own individual autocommit transactions: --- still running after 10 minutes --- still running after 20 minutes --- gave up after 30 minutes (and it was only 1/100th through inserting all the data) The fact of the matter is, that you are much better running larger transactions than smaller ones. The hardware limits the transaction rate (max 60 transactions/second on spinning rust -- unless you have lying hardware that does not flush properly). Apparently somewhat limited on a machine with a 5GB/s SSD as well...since the write rate was hugely less than the channel limit, but the CPU was fully consumed. And just to be clear, putting multiple statements in a transaction does not mean they are executed as a single VDBE program. They are still executed one at a time. The only difference is the number of trnsactions (and hence the commit/flush to disk count). -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Sarge Borsch > Sent: Sunday, 28 May, 2017 04:58 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Importing from single-insert-statement SQL dump is 61 > times slower than importing from SQL dump with one statement per row > > I compared speed of importing (into an empty SQLite DB) from 2 kinds of > SQL dumps. Data is exactly the same in both cases, and xz-compressed size > of SQL dump is near 18MB in both cases. > First SQL dump has single big INSERT statement in single transaction. > Second SQL dump has one INSERT statement for each row. > > Schema is 1 table with these columns: INTEGER PRIMARY KEY, TEXT x2 (2 > columns), INTEGER x4, TEXT x10 > There is nothing else besides the data, no indexes, etc. in both cases. > > In both cases I am importing from compressed file, using command like > this: > time xzdec something.sql.xz | sqlite3 something.db > > Time of import from single-insert SQL dump: > real 2m13.884s > user 2m13.791s > sys 0m1.052s > > Time of import from multiple-insert SQL dump: > real 0m2.192s > user 0m3.266s > sys 0m0.347s > > IMO sqlite needs some optimisation for this case when there’s a huge > INSERT statement, because the speed difference is enormous. > _______________________________________________ > 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
