SQL Server: Possible perf optimization leveraging autocommit transactions in SaveChanges()

[divega]

By default every Transact-SQL statements is executed in autocommit mode, meaning that it will be automatically committed if it succeeds and rolled back if it fails. In theory when SaveChanges() gets invoked with only one changed object which doesn't have database generated values, we shouldn't need to wrap the statement in a transaction.

[gdoron]

Nice idea.
I guess it would be an opt-in feature since you don't really know whether the DB default transactions mode has been changed.
Right

[divega]

Triage: we want to understand the actual benefit of this before we prioritize it.

[divega]

I guess it would be an opt-in feature since you don't really know whether the DB default transactions mode has been changed.

@gdoron are you talking about SET IMPLICIT_TRANSACTIONS ON or is there any other way to affect the default transaction mode? We should try it but incidentally I believe things would work similarly for most cases as long as the DbContext controls connection opening and closing (maybe we only apply the optimization if that is the case).

[AndriySvyryd]

There was no measurable difference, SQL Client probably already optimizes this

[divega]

@AndriySvyryd thanks for the prompt investigation. Closing.