Search code examples
c#winformsbulkinsertnpoco

NPoco InsertBulk() took 45 *minutes* to insert 20,000 records

I'm developing a Winforms app that currently uses DataTables/DataAdapters with emphasis on importing/processing delimted text into an embedded SQL Server Compact database.

It works, but I'm not crazy about passing DataRows around my app rather than POCOs. I thought I'd peek into the world of ORMs for comparison. NPoco looked good because it had explicit support for SQL Server Compact and simple syntax. But...

        List<dummy> many = new List<dummy>(20000);

        for (int i = 0; i < 20000; i++)
        {
            dummy newdummy = new dummy();
            newdummy.dummytext = $"many-{i}";
            many.Add(newdummy);
        }

        using (Database db = new Database(CONNECTIONSTRING, DatabaseType.SQLCe))
        {
            System.Diagnostics.Debug.Print(DateTime.Now.ToString());
            db.InsertBulk(many);
            System.Diagnostics.Debug.Print(DateTime.Now.ToString());
        }

I was blown away by how slow this was! 45 minutes wall clock time. (Fetch time for the resulting 20,000 records is acceptable, but still...)

For comparison I can import 20,000+ records with a prepared command & updating parameter values in a loop in about 2.5 seconds. DataAdapter.Update is 8 seconds.

Is an ORM just not suitable for my use case, or should I try a different ORM, or...?

Solution

  • Per @John, the workaround for this problem is to wrap the bulk insert in a transaction.

        using (Database db = new Database(CONNECTIONSTRING, DatabaseType.SQLCe))
    {
        System.Diagnostics.Debug.Print(DateTime.Now.ToString());
        db.BeginTransaction();     // <------
        db.InsertBulk(many);
        db.CompleteTransaction();  // <------
        System.Diagnostics.Debug.Print(DateTime.Now.ToString());
    }