CODEWITHSUNDEEP
c#sql-server-2008c#-4.0ado.net
souser
souser

Reputation: 80

Is there any performance impact of having transaction control implemented in ado.net code?

I am supposed to tune a .net application, where the developers have implemented transaction control in ado.net even for connections with single sql command. Personally, I avoid ado.net transaction control in such scenarios and implement it on the db side. I believe there's a call to db for each ado.net transaction control command in addition to the one for sql command, which will cause a performance hit. I understand on the backend, both are equivalent. I want to know if it's an issue and if it is, then how significant.

Upvotes: 0

Views: 1268

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062855

Whether or not it is an issue depends on how fast you need it to be. The time involved here is basically going to be an extra 2 x {latency} (one for "begin", one for "commit" / "rollback"), where {latency} is dependent on your network, but typically about 0.25ms; so - call it 0.5ms per operation. In most scenarios, by itself this 0.5ms will be irrelevant, especially compared to the actual "doing" code. If you are making enough trips to the DB that this is an important number, then the first thing I would change would be: make fewer trips to the DB.

In most cases, I wouldn't be overly concerned by whether this is implemented in the TSQL vs on the connection : either will work, and it is much easier to ensure that the connection-based approach is provably correct. I've seen lots of mistakes in TSQL rollback code...

To know for sure, you would have to profile, but again: keep in mind that the time will usually be a: isolated (i.e. not 200 of these per page, etc), and/or b: swamped by the actual "doing" code

Upvotes: 1

Related Questions