Geek Answers Handbook

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

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

I am currently creating inserts for each row of the table. I tried the dataadaptor, but the speed did not seem to be faster. It currently takes 5 minutes to go through 20,000 rows and write them to the database. Any sugestions?

decision:

I found that around insertion blocks of inserts 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 insertion statements were about 500 bytes each, so I limited the number of statements to 100 per transaction.

+4
source share
2 answers

See this FAQ section on 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 inserts are grouped into a single transaction. The time required to complete the transaction is amortized over all attached insert operators, and therefore the time for the insert instruction is significantly reduced.

+3
source

Watch this stream .

The best way is to use ExecuteNonQuery() , which automatically captures all inserts, and should not contain line highlighting. 20,000 lines should take much less than a minute.

+1
source

More articles:


All Articles