Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

c#

sqlite

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.

like image 340
fishhead Avatar asked Oct 14 '22 11:10

fishhead


2 Answers

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.

like image 164
Robert Harvey Avatar answered Nov 15 '22 06:11

Robert Harvey


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.

like image 31
BlueRaja - Danny Pflughoeft Avatar answered Nov 15 '22 05:11

BlueRaja - Danny Pflughoeft