CODEWITHSUNDEEP
linq-to-sqlwindows-phone-7windows-phone-7.1
tomo
tomo

Reputation: 1950

Deleting rows using LINQ To SQL is extremely slow on Windows Phone 7.1

There is Windows Phone 7 app using SQL CE database. I need to delete 1000-2000 rows. There is no other choice than using LINQ To SQL on Windows Phone 7 Mango - SQL CE database.

What I do is extremaly simple (pseudocode):

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

The problem is that deleting 200 rows takes 7 seconds! All time is taken inside datacontext.SubmitChanges().

Is there any way to make it faster?

Any option to use 'batch delete'? ('batch_insert' would be also welcome)

A few technical details:

Upvotes: 2

Views: 3505

Answers (6)

user3443304
user3443304

Reputation: 1

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

Solved my problem;

Upvotes: 0

tomo
tomo

Reputation: 1950

Perhaps somebody find it usefull: Best practises for win phone

I used a few hints from article above plus redesigned algorithm that it tries to modify (recycle) records instead of delete/insert commands. It gave noticeable performance gain, but still comparing the same application on Android and WinPhone it seems WP7 is much slower.

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

Upvotes: 0

MAXE
MAXE

Reputation: 5142

I think this is a common problem, if you have to manage db synchronizations in your app.

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

So you have two solutions:

  1. override the default Linq To Sql DELETE behaviour (I do not suggest it): http://msdn.microsoft.com/en-us/library/bb882646.aspx
  2. 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:

If you put in your table a Binary column with the IsVersion attribute set to true, like this:

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

you will improve the performance for batch DELETEs and UPDATEs a lot.

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

Customers -> from 34492 ms to 3230 ms (613 records)

Products -> from 37233 ms to 7264 ms (416 records)

Expiries -> from 3713 ms to 228 ms (2386 records)

Products' measure units -> from 66347 ms to 7321 ms (808 records)

Upvotes: 1

Amy B
Amy B

Reputation: 110151

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.

LinqToSql deletes with optimistic concurrency. Although SubmitChanges is using an implicit transaction over your 200 items, each item is sent into the database individually with all the originally read values for deletion.

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: thanks for the link.

Based on this description from msdn, phone memory might be the limiting factor. Make sure to dispose your datacontext after each SubmitChanges call as the article advises.

Upvotes: 0

johnhforrest
johnhforrest

Reputation: 991

In my experience, slow SQL operations for small data sets such as this have occurred because each query is being wrapped in an individual transaction. You should be able to wrap all of the queries in a single transaction, or as you put it, a "batch".

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

http://msdn.microsoft.com/en-us/library/bb386995.aspx

Upvotes: 0

MatthieuGD
MatthieuGD

Reputation: 4592

Have you try to call SubmitChanges() outside the loop? 

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.

Upvotes: 2

Related Questions