none
[WIN 8.1]How to perform a bulk insert operation using SQLite database RRS feed

  • Question

  • Im currently using following code

     using (var connection = new SQLiteConnection(this.databaseRelativePath))
                        {
                            using (var statement = connection.Prepare("BEGIN TRANSACTION"))
                            {
                                statement.Step();
                            }
    
                            using (var statement = connection.Prepare(sqlString))
                            {
                                int k = startIndex;
                                foreach (var dataObject in detailObjects)
                                {
                                    Utility.LogMessage("Inserting the objects at " + objectType.FullName + "_" + obj_Id + " ---- " + k);
                                    k++;
                                    for (int i = 1; i <= modelProps.Length; i++)
                                    {
                                        statement.Bind(i, modelProps[i - 1].GetValue(dataObject));
                                    }
                                    statement.Step();
                                    statement.Reset();
                                    statement.ClearBindings();
                                }
                                Utility.LogMessage("Detail data-Object lupdated successfully");
                            }
                            using (var statement = connection.Prepare("COMMIT TRANSACTION"))
                            {
                                statement.Step();
                            }
                        }
    Now this takes 2-3 seconds for inserting 10 thousands records(table having 14 column).

    Can anyone suggest to improve this operation , to make this perform more faster.

    Thanks in advance for quick reply.


    dinez

    Friday, December 23, 2016 5:00 AM

Answers

  • Hi Dinez,

    The SQLite can easily do more INSERT statement per second, but the speed is limited by the rotation speed of your device disk drive. By default, SQLite actually waits until the data really stored on the disk before the transaction is complete and it is more safe; and you can run PRAGMA Synchronous=OFF. It will cause SQLite to not wait on data to reach the disk surface, it will be much faster, but your database file might corrupt when your device lose power in the middle of a transaction.

    There is a similar thread, I think it may be helpful.

    http://stackoverflow.com/questions/4358838/sqlite-insert-performance 

    Best regards,
    Breeze Liu



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dinez Thursday, December 29, 2016 5:31 AM
    Monday, December 26, 2016 9:32 AM