CODEWITHSUNDEEP
c#linq-to-sqlwindows-phonesql-server-ce
Rowland Shaw
Rowland Shaw

Reputation: 38130

Is it possible to improve the performance of batch deletions when using Linq to SQL on Windows Phone?

I'm using Linq-to-SQL in a Windows Phone application to act as a cache when out of network/Wi-Fi coverage. This all works great, but I've found that removing stale entries from the cache appears to use a lot of separate of separate statements.

Considering the (relatively trivial) case to clear a table (for example, if the end user chooses to log out):

context.CacheChecks.DeleteAllOnSubmit(context.CacheChecks);

You'd hope that the generated SQL would be of the form (or something equivelant):

DELETE FROM CacheChecks

However it turns into something more akin to:

SELECT [t0].[LastChecked], [t0].[EntityType]
FROM [CacheChecks] AS [t0]
-- @p0: Input DateTime
-- (Size = 0; Prec = 0; Scale = 0) [3/13/2008 12:00:00 AM]

DELETE FROM [CacheChecks] WHERE [EntityType] = @p0
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [{GUID}]

DELETE FROM [CacheChecks] WHERE [EntityType] = @p0
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [{GUID}]

-- Individual DELETE statements for each row

When on the desktop, I've seen various extension methods to allow for batch updates, which seem to be based on this article by Terry Aney, but this approach doesn't work in the windows Phone world, as there is no way to create an arbitary DbCommand for a given context.

Is there another way to handle a batch deletion of many rows (1,000s) in a more efficient manner than row by row?

Upvotes: 3

Views: 101

Answers (1)

ErikEJ
ErikEJ

Reputation: 41799

Yes, it is possible to perform deletes that bypass the Query processor, in my test improving time to delete 100 rows from 320 ms to 70 ms. You can do so by simply adding a rowversion column to your table:

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

More info here: http://erikej.blogspot.dk/2012/04/windows-phone-local-database-tip.html

Upvotes: 2

Related Questions