Does transaction slow the query execution?

Question

I want to know that does Apply transaction in stored procedure slow the execution of query? if yes the why? i want to know the actual internal processing of sql server when we apply transaction on query?

Answer 1

On the performance front, I guess that there will be a slight performance degradtion when using transactions, but that is negligible.

For the transaction processing, got two links below. Hope they will help you -

http://www.informit.com/articles/article.aspx?p=26657

http://sqlserverpedia.com/wiki/Database-Transaction

Answer 2

Consider that there are different types of Transaction within SQL Server and that the default setting for the Database Engine is "Autocommit Transaction", that is to say that each individual Transact-SQL statement is committed when it completes. You do not have to specify statements to control transactions, unless you wish to explicitly manage them with more refined control.

See: Controlling Transactions (Database Engine)

Are you perhaps therefore asking if there is any additional overhead when explicitly controlling transactions?

The short answer is yes, as to what exactly is that overhead, well it depends. It depends on multiple factors such as the method used, i.e. Transactions managed through an API or direct via T-SQL, as well as the performance of your specific hardware.

Answer 3

It's a fairly pointless question. If you need a certain level of concurrency you have to pay whatever it costs. You don't have the choice.