what is the fastest way to populate SQLite table from a datatable

Question

What is the fastest way to populate a SQLite database from a DataTable in C# .net 2.

Currently I am building insert statments for each row in the table. I have tried a dataadaptor but the speed didn't seem to be any faster. It currently takes 5 min to loop through 20,000 rows and write them to the database. Any sugestions?

solution:

I found that surounding blocks of insert statments with BEGIN...COMMIT worked for me with a remarkable speed improvement:

BEGIN;
INSERT INTO friends (name1,name2) VALUES  ('john','smith');
INSERT INTO friends (name1,name2) VALUES  ('jane','doe');
COMMIT;

my insert statements were around 500 byte each, so I limited the number of statements to 100 per transaction.

Answer 1

See this FAQ entry from the SQLite website:

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

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

Answer 2

See this thread.

The best way is to use ExecuteNonQuery(), which commits all the inserts at once, and doesn't have to keep allocating strings. 20,000 rows should take much less than a minute.