CODEWITHSUNDEEP
c#sqlitewindows-8microsoft-metroasync-await
Brian Kalski
Brian Kalski

Reputation: 957

Inserting large amount of records into Sqlite database

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.

Upvotes: 2

Views: 1229

Answers (2)

Farhan Ghumra
Farhan Ghumra

Reputation: 15296

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++;
    });
}

Upvotes: 0

Chad Brockman
Chad Brockman

Reputation: 1406

--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?

Upvotes: 3

Related Questions