Sql Server stored procedure performance hints June 7, 2008Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
The main thing for a database, besides data integrity, is performance. We must make sure that our database system runs as fast as possible. Most of the times the database size grows rapidly and the number of users rise swiftly. So inevitable the performance slows down. There are hundreds of things one can do to achieve this. That depends on the database size and how many users use the database and other factors.
A developer when designing solutions must keep database performance issues in mind.In most applications now we use stored procedures to update or query the database. We then call our stored procedures from our code through ADO.
When using stored procedures a simple thing to improve performance is SET NOCOUNT ON.
SET NOCOUNT ON stops the message that shows the count of the number of rows affected by a stored procedure from being returned as part of the result set. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements.
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
Obviously we want to have this option set to OFF in the Query Window when we test our stored procedure but there is no reason to have all this information be passed back to the client. To recap using the above option in a stored procedure the network traffic is reduced and our database performance improves.
I know what some people already thinking “but i need to know the number of rows affected when my stored procedure runs”. You can still have this information by using “@@ROWCOUNT”. So you can have both.
If you worry that you will forget to set the SET NOCOUNT ON, then use the Template Explorer of SQL Server 2005 and 2008 to create a stored procedure. The SET NOCOUNT is set to ON by default.