Inserting large amount of records into Sqlite database

Question

In my Windows 8 metro app I have a table in a SQLite database on occasion will have to be updated with a large amount of records (around 500 to 600). I get the records from a web service and I loop through the records and insert them into a table. The problem is that this operation is taking about 10 to 15 seconds and it is locking the UI including the progress bar. The code is below.

await this.Dispatcher.RunAsync(Windows.UI.Core.CoreDispatcherPriority.Normal, () =>
{
    foreach (var item in body3)
    {
        db.Insert(new Site
           {
              siteName = item.siteName,
              siteAddress = item.siteAddress,
              siteCity = item.siteCity,
              siteState = item.siteState,
              siteID = item.siteID
           });
        progressBar.Value = i;                                
        i++;
    }
});

I'm thinking it's a problem with SQLite. I can't seem to catch exceptions from it either.

I think my real question here is how to get this to run correctly in another thread so it doesn't affect the UI. I'm not that concerned that it takes a while to insert the records. I just want the UI to stay responsive.

score 3 · Answer 1 · edited May 23 '17 at 12:05

3

--don't update your UI from every insert in the loop... maybe every 20% if it's needed at all

--Use a transaction -- http://www.sqlite.org/faq.html #19 and here Improve INSERT-per-second performance of SQLite?

edited May 23 '17 at 12:05

Community

1
1

answered Jun 06 '13 at 21:45

Chad Brockman

1,176
3
10
19

Those are great articles. They should help a lot. Thanks. – Brian Kalski Jun 07 '13 at 02:58

score 0 · Accepted Answer · answered Jun 07 '13 at 02:14

0

Try in this way.

foreach (var item in body3)
{
    db.RunInTransaction(() =>
    {
        db.Insert(new Site
        {
            siteName = item.siteName,
            siteAddress = item.siteAddress,
            siteCity = item.siteCity,
            siteState = item.siteState,
            siteID = item.siteID
        });
    });
    await this.Dispatcher.RunAsync(Windows.UI.Core.CoreDispatcherPriority.Normal, () =>
    {
        progressBar.Value = i;                                
        i++;
    });
}

answered Jun 07 '13 at 02:14

Farhan Ghumra

15,180
6
50
115

2

Actually what worked better is wrapping the foreach loop in a transaction. Now the process is so fast I don't need the progress bar anymore. It went from around 10 to 15 seconds to less than a second. – Brian Kalski Jun 07 '13 at 03:22
Downvoted... it makes no sense to have your Transaction inside the Foreach loop! This will create a new connection / transaction per row and won't be any faster than what the OP did. Per Brian's comment - the foreach goes inside the transaction. Or, simply use SQLiteConnection.InsertAll(Collection). – maplemale Jan 21 '18 at 01:32

Inserting large amount of records into Sqlite database

2 Answers2