C# System.Data.SQLite Designer Code

Question

I've been messing around with the SQLite Designer in Visual Studio 2008 and I have noticed that when I use the generated Insert/Update statements they run extremely slow.

Example: I have a data table with four columns and 5700 rows it took ~5 mins to insert the data into the database table

However, I wrote my own database connection and insert methods using parameters and a single transaction and the same 5700 rows were inserted in under 1 second.

Why is the generated code so slow and what is benefit to even using it?

Thanks.

Nathan

Answer 1

if i nderstood correctly that insert statement is built in code-behind and it's normal that it would take a long time to execute... it has to be compiled, processed turned into CLR etc. If you use a stored procedure on the database it takes less time cause that procedure when you call it from code-behind has already been through these steps. It's always better and faster to use procedures directly from the databae than wright them in code-behind.

Answer 2

I've read that the default behavior in SQLite is to wrap each Insert statement in a Transaction, which I think explains the behavior you are seeing. When the SQLite Engine creates the Insert statements it takes a long time and when you write the sql it runs very quickly.

http://www.sqlite.org/faq.html#q19