Using LINQPad to insert data into a SQL CE Database, Linq-to-sql, gets slower over time, anything I can do about it?

Question

I have a SQL CE 4.0 database on disk, and a LINQPad program that I wanted to use to fill that database.

I am using the built-in (to LINQPad) linq2sql system. I am noticing that as time goes on, the time it takes to insert a batch of rows takes more and more time and eventually it looks like the program has slowed to a crawl.

Is there some kind of caching or similar that is slowing it down? What, if anything, can I do to avoid this slow-down?

Here's my simple test-table that I used to test this with:

CREATE TABLE dummy
(
    id int not null primary key identity (1, 1),
    value nvarchar(20) not null
)

and my test program:

void Main()
{
    for (int iteration = 1; iteration <= 1000; iteration++)
    {
        Stopwatch sw = Stopwatch.StartNew();
        for (int row = 0; row < 100; row++)
            dummy.InsertOnSubmit(new dummy { value = "row#" + row });
        var create = sw.ElapsedMilliseconds;
        SubmitChanges();
        sw.Stop();
        var total = sw.ElapsedMilliseconds;
        Debug.WriteLine("iteration " + iteration + ", create=" + create + ", total=" + total);
    }
}

here's sample output:

iteration 1, create=1, total=52
iteration 2, create=0, total=41
iteration 3, create=0, total=53
iteration 4, create=0, total=45
...
iteration 86, create=0, total=181
iteration 87, create=0, total=218
iteration 88, create=0, total=172
iteration 89, create=4, total=192
...
iteration 167, create=0, total=387
iteration 168, create=0, total=427
iteration 169, create=0, total=496
iteration 170, create=0, total=425

As you can see, the time it takes to call SubmitChanges takes more and more time, yet I'm inserting the same number of entities each iteration.

Please note that I am fully aware that I won't get speeds like bulk insert or what not, but I still would like to avoid having this slowdown if possible.

Also note that if I re-run the program now, having a lot of rows already in the database, the time starts low again:

iteration 1, create=1, total=51
iteration 2, create=0, total=50
iteration 3, create=0, total=45
iteration 4, create=0, total=45

So it seems to me that there is something that keeps growing in memory.

Is there anything I can turn off, reset, dump, whatever, to fix this, other than just stop the program periodically and restart it (which for what I'm really doing can be done, but I'd like to avoid it).

Answer 1

It sounds like you want to bulk-copy data to SQL Server from within LINQPad. Here's a method I use to do that:

void BCP<TRow> (IEnumerable<TRow> rows)
{
    if (rows.Count() == 0) return;
    var dt = new DataTable ();
    var metaTable = _db.Mapping.GetTable (typeof (TRow));
    var columns = metaTable.RowType.DataMembers.Where (dm => dm.Association == null);
    var transformers = new List<Func<TRow, object>>();
    foreach (var columnX in columns)
    {
        var column = columnX;
        dt.Columns.Add (column.Name, L2sToDataTable (column.Type));
        transformers.Add (row => L2sToDataTable (row.GetType().GetField (column.Name).GetValue (row)));
    }
    foreach (var row in rows)
        dt.Rows.Add (transformers.Select (t => t (row)).ToArray());

    _db.Connection.Open();
    Console.Write ("Bulk copying " + metaTable.TableName + "... ");
    var bcp = new SqlBulkCopy ((SqlConnection)_db.Connection) { DestinationTableName = metaTable.TableName, BulkCopyTimeout = 300 };
    bcp.BatchSize = 20;
    bcp.NotifyAfter = 20;
    bcp.SqlRowsCopied += (sender, args) => Console.Write (args.RowsCopied + " rows... ");
    bcp.WriteToServer (dt);
    _db.Connection.Close();
    Console.WriteLine ("Done");
}

Type L2sToDataTable (Type l2sType)
{
    if (l2sType == typeof (Binary)) return typeof (byte[]);
    if (l2sType.IsGenericType && l2sType.GetGenericTypeDefinition() == typeof (Nullable<>)) return l2sType.GetGenericArguments()[0];
    return l2sType;
}

object L2sToDataTable (object l2sValue)
{
    if (l2sValue == null) return DBNull.Value;
    if (l2sValue is Binary) return ((Binary) l2sValue).ToArray();
    return l2sValue;
}

This avoids the caching-memory-overhead of an object-relational mapper, such as LINQ-to-SQL, while utilizing the latter's metadata.