随着时间的流逝，使用LINQPad将数据插入SQL CE数据库Linq-to-sql变得越来越慢，我能做些什么? [英] Using LINQPad to insert data into a SQL CE Database, Linq-to-sql, gets slower over time, anything I can do about it?

问题描述

我在磁盘上有一个SQL CE 4.0数据库，还有一个我想用来填充该数据库的 LINQPad 程序.

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

我正在使用内置的(到LINQPad)linq2sql系统.我注意到随着时间的流逝，插入一批行所花费的时间越来越多，并且最终看起来该程序已经变慢了.

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
)

和我的测试程序:

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

这是示例输出:

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

如您所见，调用SubmitChanges所花费的时间越来越多，但是我每次迭代都插入相同数量的实体.

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).

推荐答案

听起来您想从LINQPad中将数据批量复制到SQL Server.这是我用来执行此操作的方法:

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

这避免了对象关系映射器(例如LINQ-to-SQL)在利用后者的元数据时的缓存内存开销.

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

这篇关于随着时间的流逝，使用LINQPad将数据插入SQL CE数据库Linq-to-sql变得越来越慢，我能做些什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！