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.

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.

Fastest way to populate datatable from database using IDataReader, Who knows the panic of using a SqlDataAdapter class to fill the Datatable. If you require only a single table to be filled from the database, then  Fastest way to populate datatable using ADO.Net classes Alright folks who knows the panic of using SqlDataAdapter class to fill the Datatable in case if you required only a single table to fill from database.

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.

SQLite Insert Into - Inserting New Rows Into a Table, To insert a single row into a table, you use the following form of the INSERT However, it is a good practice to include the column list after the table name. Third  Alright folks! Who knows the panic of using a SqlDataAdapter class to fill the Datatable. If you require only a single table to be filled from the database, then this post is to show you there's an alternate way to get a DataTable. That alternate way is returning a DataReader from the command object after executing the command.

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

How To Populate a DataSet Object from a Database by Using Visual , This article demonstrates how to fill a DataSet object with the results of one or Database terminology; Structured Query Language (SQL) A good way to handle the data loading is to run FillSchema followed by Fill. to loop through all of the DataRow objects within the Rows collection of a DataTable. On 3 Feb 2013, at 6:34pm, Paul Sanderson <[hidden email]> wrote: > I want to populate a large table (millions of rows) as quickly as possible, > > The data set will not be operated on until the table is fully populated and > if the operation fails I will be starting again. the operation is a one off > and the table will not be added to at a future date.

Populating a DataSet from a DataAdapter, The Fill method of the DataAdapter is used to populate a DataSet with the Another good workaround is to retrieve the rows in batches (such as several SQL Server Northwind database and populates a DataTable in a  Insert DataTable into SQL Table using SQL Table-Valued Parameters in .NET C# This is an another method to Insert DataTable rows into SQL Table in C# . SQL Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data.

SQLite - INSERT Query, SQLite INSERT INTO Statement is used to add new rows of data into a table in the You may not need to specify the column(s) name in the SQLite query if you are In the next chapter, you will learn how to display all these records from a table. You can populate data into a table through select statement over another​  Then you had to call the Fill method on the DataAdapter and pass it a newly created DataSet or DataTable. In .NET 2.0, this code model has been simplified, thanks to the Load method on the DataTable and DataSet classes that takes an IDataReader as a parameter.

[Solved] Filling large data to c# Data Table, That will be faster than what you have currently. This was my suggestion. There is some other ways also to handle large data in datatable. Refer  Learn on how to create a Populate ComboBox With SQLite using C#. C# is a general-purpose, object-oriented programming language. C# automatically manages inaccessible object memory using a garbage collector, which eliminates developer concerns and memory leaks. C# is faster than dynamically typed languages because things are more clearly defined. It contains several classes that support any C#

Comments
  • interesting. I wonder how many statments to surround with the BEGIN ... COMMIT block
  • You can do sixty transactions per second with a 7200 RPM hard drive. Divide your write speed (say 5MB/s) by 60 to get the number of bytes per transaction, and then divide that by your record size to get the number of inserts per transaction.