Question

Windows Telephone 7 app using CEQ database. 我需要删除1000-2000年。别无选择的是使用LINQ ToQ Windows Telephone 7 Mango - CEQ数据库。

我所做的是极其简单的(假装):

Entity[] tmp = null;
do
{
    tmp = datacontext.mytable.Where( ...expression here ...).Take(200).ToArray();
    if (temp.Length > 0)
    {
        mytable.DeleteAllOnSubmit(tmp);
        datacontext.SubmitChanges();
    }
} while (temp.Length > 0);

问题是,删除200个牢房需要7秒钟! 所有时间均在<条码>数据基文内。

是否有什么办法可以加快速度?

使用批量删除的任何选择? (欢迎批量_插入)

一些技术细节:

DataContext was created by SqlMetal tool and it looks rather heavy.
Entity has 2 foreign key relations.

Answer 1

Perhaps somebody find it usefull: Best practises for win phone

我从上述条款中使用了几个方面加上重新设计的算法,试图修改(循环)记录,而不是删除/插入指挥。它带来了明显的绩效收益,但是仍然比较了对Anderson和WinPhone的同一申请,看来WP7的进度要慢得多。

Unfortunatelly I suspect it s way better to use SqlLite for database heavy apps.

Answer 2

您是否试图在休息室外打电话?

using(var datacontext = new MyDataContext())
{
var entities = datacontext.mytable.Where( ...expression here ...);
datacontext.DeleteAllOnSubmit(entities);
datacontext.SubmitChanges();
}

But you mention a tool to create your datacontext. Is it your schema / object graph is complex? I m not using any tool for my datacontext because my schema is really simple: no relation.

Answer 3

我认为这是一个共同的问题,如果你必须管理你的同步。

The real problem is related to Linq To Sql, and the way it constructs DELETE statements.

因此,你有两个解决办法:

override the default Linq To Sql DELETE behaviour (I do not suggest it): http://msdn.microsoft.com/en-us/library/bb882646.aspx
use a ROWVERSION column in your table (more affordable as a solution), that helps Linq To Sql to manage large batch updates/deletes.

I will give you details about the second solution, much more easier to implement:

如果你在表格中用IsVersion属性一栏写成:

[Column(IsVersion = true)]
private Binary _version;

您将大大改进批量和定点性能。

BUT, BE CAREFUL!!! If you use a ROWVERSION column in a table, you must remove any other Unique Index that involves your Primary Key, otherwise your app will crash without any exception thrown!! This will be very annoying, believe me!!

See this example:

[Table(Name = "_customers")]
[Index(Columns = "Uid", IsUnique = true)] // <- THIS WILL GIVE YOU AN UNEXPECTED CRASH!
[Index(Columns = "Code", IsUnique = true)]
public class Customer : BaseModel, IBaseTable
{    
    [Column(IsVersion = true)]
    private Binary _version;

    [Column(IsPrimaryKey = true, IsDbGenerated = false, DbType = "UNIQUEIDENTIFIER NOT NULL", CanBeNull = false)]
    public Guid Uid {...}

    ...
}

Just a few home-made statistics for my tables (I know that I do not show you the schema, but believe me: I have all foreign keys that you can imagine, and it works perfectly!)...

客户——和;从34492个ms到3230个ms(613个记录)

产品——和;从37233个ms到7264个ms(416个记录)

影印——和;从3713个ms到228个ms(2386个记录)

产品计量单位——和;从6 6347 ms到7321 ms(808 记录)

Answer 4

在我的经验中,由于每个查询都在单独交易中消化,因此对小型数据集(如此类)的突然运行缓慢。您应能在一次交易中或当你认为“批量”时提出所有问题。

The link below will have some more information for you on LINQ to SQL transactions:

rel=“nofollow”>http://msdn.microsoft.com/en-us/library/bb386995.aspx

Answer 5

The problem might not be on the client side. Check the table for triggers. Check any cascade-deleted tables for triggers. If those are causing the issue, no amount of change on the client can fix it.

Linq ToSql以乐观的一致意见删除。虽然SubmitChanges正在对你200个项目使用默示交易,但每个项目都单独输入数据库,所有原为删除值。

If you want to go around optimistic concurrency, the most straight-forward path is DataContext.ExecuteCommand. Using this, you can delete without even loading those rows on the client.

Edit:感谢链接。

以

Answer 6

[Column(IsVersion = true)] private Binary _version;

解决我的问题;

友情链接