Does transaction slow the query execution? [closed]

It's difficult to tell what is being asked here. This ques开发者_开发技巧tion is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 9 years ago.

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?

---

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.

---

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