Insert into Members
SELECT People.id, Names.value., Ages.value FROM People
LEFT JOIN Names on Names.id = People.id
LEFT JOIN Ages on Ages.id = People.id

It runs fine when executed in SQLite manager (couple seconds). But takes an unacceptable time to execute when I run it in my program (stopped waiting for it...). I've tried my own ExecuteNonQuery wrapper (for the class) as well as command.ExecuteNonQuery()

All other queries (joins/inserts included) work OK, it's just this part. I've tried doing the joins as views, tempTables all sorts query variations, that work in SQLite manager, but not my solution :(

My data currently only has a few thousand rows, but will be upwards of a million when finished. It's a one off query as part of a data import. I'm using System.Data.SQLite.

Are there any known issues with doing multiple joins/large inserts this way? (My other queries returned much smaller result results) Should I investigate Linq or similar (looking at the related questions).

1 Answer
1

Your code passes the query to the SQLite driver, so if running the query from your code is alot slower than SQLite Manager, you should check if there are driver updates for you.

You could try execute explain insert into members (...) (explain + your whole query) in both the SQLite Manager, and from your code, and compare the results. That way you can see if either choses a better strategy than the other.

Also make sure you have indexes on all id fields, since these provide a great performance benefit.

Thanks heaps. I just did the indexes, inserts are super fast now! I know it was obvious... I was creating all my indexes at once after I'd done all my importing, thinking I'd be maximising the insert performance... Since my import process is a effectively bulk insert, and the application is read only once imported I guess I'm overall I'm not saving any time... Thanks again, it was obvious and a noob error, but I needed somebody to tell me :)
–
DougFMar 2 '11 at 10:22

Why the reference to Linq? I don't see any linq in the question or answer.
–
Andrew ArnottSep 21 '13 at 13:34

@AndrewArnott "Should I investigate Linq or similar (looking at the related questions)." see the last line of the question.
–
C.EvenhuisSep 21 '13 at 17:51