dotnet / SqlClient

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications.
MIT License
860 stars 288 forks source link

Any way to reduce the roundtrip for BeginTransaction and Commit #2976

Closed ycymio closed 2 weeks ago

ycymio commented 2 weeks ago

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 commented 2 weeks ago

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