1

I'm currently trying to add a lot of data to a SQLite database in my Windows 8 App using C# and System.Data.SQLite. I have all the objects (MediaItem) in a list and I'm adding each one to the database using a foreach-loop. Unfortunatley this is pretty slow, so I'm wondering if there's a way to speed things up. Can I for example hand over the complete list to the database? Here's my code so far:

List<MediaItem> items;

// adding lots of onbjects to the list...

using (var db = new SQLiteConnection(dbPath))
{
     foreach (var item in items)
     {
         db.Insert(item);
     }
 }
Thomas
  • 3,399
  • 4
  • 33
  • 66
  • http://stackoverflow.com/q/1579201/62576 and http://stackoverflow.com/q/364017/62576 might provide some insight. – Ken White Mar 27 '13 at 23:44

1 Answers1

6

From my experience, wrapping as many database calls as possible into a transaction speeds things up quite a bit:

using (var db = new SQLiteConnection(dbPath))
{
    db.RunInTransaction(() =>
    {
        foreach (var item in items)
        {
            db.Insert(item);
        }
    });
}
chue x
  • 17,865
  • 6
  • 52
  • 67
  • works like a charm, thanks so much. Adding 50.000 items now is done in less than a second when earlier it took minutes. – Thomas Mar 28 '13 at 00:32