Consider cancelling the command in SaveChanges when an error occurs

[roji]

We currently limit the SaveChanges batch size (42 in SQL Server), among other things because if an error occurs (e.g. unique constraint), we don't want to continue sending a huge amount of useless data that will get rolled back anyway. The PG provider, by the way, has no such upper limit (see npgsql/efcore.pg#2630 for a recent complaint about this). Limiting the max batch size sacrifices performance for the good case (more roundtrips when no errors occur), so that the bad case is faster.

An alternative approach may be to simply attempt cancellation of the query when we see an error. FWIW this is what Dapper does, IIRC.

[stevendarby]

Limiting the max batch size sacrifices performance for the good case (more roundtrips when no errors occur), so that the bad case is faster.

I think 42 was picked as the default because it was around the most optimal batch size found during an investigation (and, you know, the other reason...)

[roji]

Yeah, that's a SQL Server-specific thing (the question above is general and concerns PostgreSQL as well). Importantly, there's a very good chance that if/when we manage to switch to the new batching API (#18990), the performance characteristics would change significantly and 42 would no longer be right.