Improve Stored Procedure Performance

by Behlul 9. September 2009 16:34

Sometimes even the simplest things can make a difference.  One of these simple items that should be part of every stored procedure is SET NOCOUNT ON.  This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.  This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.  By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

Tags: , ,

Coding | Hint

Microsoft Tag

Behlül Behram Kimdir?

2004 yılında Doğu Akdeniz Üniversitesi Bilgisayar Mühendisliği Bölümünden mezun oldu. 2000 yılından günümüze kadar C, C++, VB, VB.NET, C# dilleri ile bir çok proje yaptı. Halen Türkiyenin önde gelen bilişim firmalarının birinde   "Yazılım Geliştirme Uzmanı" olarak çalışmaktadır...