2

I have an old solution running on LINQ-to-SQL (DMBL), where I want to delete a large amount of rows based on id's (incl. a lot of referenced objects).

But the traditional way of using DeleteOnSubmit or DeleteAllOnSubmit is too slow (my calculation says 48 hours+):

context.Table.DeleteAllOnSubmit(entities);

I've tried to rewrite the code to use ExecuteCommand instead - it would be great to execute something like this:

DELETE dbo.Table WHERE Id in (1,2,3..)

But since LINQ-to-SQL can't handle IN statements (ref: Datacontext ExecuteCommand parameters in IN statement), I'm stuck with calling ExecuteCommand for each row like this:

var command = $"DELETE {tableName} WHERE {idColumnName} = {{0}}";
foreach (var id in idList)
{
    context.ExecuteCommand(command, id);
}

...which is close to being as slow as DeleteOnSubmit/DeleteAllOnSubmit.

The db have have been optimized with index's, so our DevOps says it's not much more that can be done there.

I know this is old-fashioned, but still hope for some useful input.

baddaydaddy
  • 604
  • 8
  • 25
  • 1
    Upload the IDs to a temp table and delete in batches eg 10k at a time using something like `DELETE x WHERE ID IN (SELECT ID FROM temptable WHERE id < @batch)` - keep bumping @batch up by N? – Caius Jard Dec 15 '21 at 17:49
  • Technically I think it's not L2S's fault it doesn't support IN like that; nothing does that I know of. Even eg Dapper that looks like it does will detect the IN and chip up the supplied array of IDs into the query so e.g. `var sql = "DELETE x WHERE ID IN("; for(int id = 0; id < ids.Length; id++) { sql += $"@p{i},"; command.parameters.Add(...).Value = ids[i]; }`. That's another optin you can try; form the string with up to 2000 parameters then exec it – Caius Jard Dec 15 '21 at 17:53
  • You don't have to use LINQ to SQL for this just because you use it for general data access. – Crowcoder Dec 15 '21 at 18:00
  • Using a TVP is a good option, see eg https://stackoverflow.com/a/66067309/14868997 – Charlieface Dec 15 '21 at 22:14
  • Since I was on a tight timeline to get this done, I went with updating "all" FK constraint with cascade delete. It took the time down from 48 hours to 14. – baddaydaddy Dec 17 '21 at 14:59

0 Answers0