0

I created the SQL for creating and filling a table in a large existing SQLite database:

CREATE TABLE ...;
INSERT INTO ...;
INSERT INTO ...;
...

Then sent it to sqlite3. Irrespective of what way I read the commands (e.g. "sqlite3 db < create.sql", or ".read create.sql" in the SQLite prompt), executing each line took close to a second, even though each line only consisted of two strings.

What is causing SQLite to work so slowly? How can I speed it up?

2 Answers2

2

inserts can be slow for a variety of reasons, such as:

  • indexes on the table need to be updated
  • triggers are on the table are executed (unlikely if you are just creating the table)
  • other activity on the server
  • the number of rows that are being passed into the server

I would recommend that you combine the inserts into a single query. You can do so using insert . . . select:

insert into table(col1, . . . coln)
    select . . .  union all
    select . . .  union all
    . . .;

If you are just declaring columns in the table, you can combine this all into one statement:

create table xxx as
        select . . .  union all
        select . . .  union all
        . . .;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    `other activity on the server` You do realize that SQLite doesn't have servers? – Colonel Thirty Two Jul 20 '14 at 15:48
  • @ColonelThirtyTwo . . . "other activity on the computer". That was meant to be a generic term that refers to the computer running the database software. – Gordon Linoff Jul 21 '14 at 01:41
  • @Geza . . . The last solution of putting the `inserts` into one statement should have similar performance to putting them into one transaction. Your original question was quite broad, which is why this answer is broad. – Gordon Linoff Aug 03 '14 at 12:41
  • @Gordon Linoff I just upvoted your answer, as it is definitely informative. I believe some of your suggestions could be excluded based on my question - but you may disagree. Anyhow, thanks again for taking the time to answer! –  Aug 03 '14 at 14:25
1

You need to put the list of INSERT statements into a transaction:

BEGIN TRANSACTION;
CREATE TABLE ...;
INSERT INTO ...;
INSERT INTO ...;
...
COMMIT;

This will dramatically reduce the execution time.