Using multiple joins in a sqlite query in C#

I have been trying to run a query like:

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).

Thanks.

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.

Linq should generally not be faster than regular queries.