3

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?

Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
  • I am not familiar with Windows Phone development but isn't truncating the table an option some how? Something like `context.ExecuteCommand("truncate table table_name");?` See similar question here: http://stackoverflow.com/questions/1516962/linq-to-sql-how-to-quickly-clear-a-table – Icarus Mar 23 '15 at 12:15
  • Same than Isarus, but I was thinking at something like `context.Database.ExecuteSql("delete from table_name");` – IronSlug Mar 23 '15 at 12:25
  • @Icarus on Windows Phone, that method doesn't exist :( – Rowland Shaw Mar 23 '15 at 12:26
  • @IronSlug nor is the Database exposed via a property on the Windows Phone data context – Rowland Shaw Mar 23 '15 at 12:27
  • @Icarus And that was the question I'd found the other day which led me to Terry's blog post :) – Rowland Shaw Mar 23 '15 at 12:29

1 Answers1

2

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

ErikEJ
  • 40,951
  • 5
  • 75
  • 115