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 many (1000 magnitude) INSERT statements into one transaction to save disk IO on commit. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Sarge Borsch Gesendet: Sonntag, 28. Mai 2017 12:58 An: sqlite-users@mailinglists.sqlite.org Betreff: [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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
