Any way to reduce the roundtrip for BeginTransaction and Commit

[ycymio]

Hello,

I noticed there is a similar question for Npgsql here, but I am unsure if it also applies to Azure SQL Server.

In our scenario, we call SQL Server to query data twice in one transaction for 99.5% of cases. If we use BeginTransaction() and Commit(), it will require four roundtrips, which nearly doubles the latency in the transaction part.

Are there any concerns if we choose to explicitly set transaction isolation with SNAPSHOT and use BEGIN TRAN and COMMIT TRAN within CommandText? Additionally, do you have any suggestions or best practices that could help us optimize the performance and reduce latency during these transactions? Any insights from your experience would be greatly appreciated.

[cheenamalhotra]

It truly depends on the use-case and the extent of transactions you will be doing with each query. Of course, doing them implicitly within the same query on the server side will give you performance benefits and save extra round trips when calling .NET APIs.

ref: https://learn.microsoft.com/en-us/sql/connect/ado-net/local-transactions?view=sql-server-ver16#determining-the-transaction-type

[ycymio]

Thank you @cheenamalhotra . I am afraid that it might cause some other unexpected performance issue. Are there any potential drawbacks to use this unmanaged transaction? Could it affect connection reuse? When the connection is disposed of, will the unmanaged transaction be rolled back, and will SET XACT_ABORT ON help in this case?