CODEWITHSUNDEEP
c#sqlite
fishhead
fishhead

Reputation: 6109

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

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.

Upvotes: 5

Views: 4861

Answers (3)

Stefan
Stefan

Reputation: 12260

Consider to use the SqLiteDataAdapter. (I still have to use vb.net but following example should be ease to translate. Or have a look at the original source: http://stackoverflow.com/a/2671511)

Private Sub FillDatabaseTableWithDataTable(dataTable As DataTable)
            ' inspired by http://stackoverflow.com/a/2671511

            ' Query the destination database
            Dim query As String = $"SELECT * FROM `{dataTable.TableName}`"

            Using adapter As New SQLiteDataAdapter(query, Connection)

                Using commandBuilder = New SQLiteCommandBuilder(adapter)

                    Connection.Open()

                    commandBuilder.QuotePrefix = "["
                    commandBuilder.QuoteSuffix = "]"
                    commandBuilder.GetInsertCommand()

                    'Create an empty "destination" table for synchronization
                    ' with SqLite "source" database
                    Dim destinationTable As New DataTable()

                    'load data from SqLite "source" database to destination table, e.g.
                    'column headers
                    adapter.Fill(destinationTable)

                    'adapt "destination" table: fill data
                    For Each row As DataRow In dataTable.Rows
                        Dim destinationRow As DataRow = destinationTable.NewRow()
                        destinationRow.ItemArray = row.ItemArray
                        destinationTable.Rows.Add(destinationRow)
                    Next

                    'Update SqLite source table
                    'the Update has To be wrapped In a transaction
                    'Otherwise, SQLite would implicitly create a transaction
                    'for each line. That would slow down the writing process.
                    Using transaction = Connection.BeginTransaction()
                        adapter.Update(destinationTable)
                        transaction.Commit()
                    End Using

                    Connection.Close()
                End Using
            End Using
        End Sub

Upvotes: 0

BlueRaja - Danny Pflughoeft
BlueRaja - Danny Pflughoeft

Reputation: 86006

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.

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180788

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.

Upvotes: 3

Related Questions