jump to navigation

Sql Server stored procedure performance hints June 7, 2008

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
trackback

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.

Comments»

1. Sql Server stored procedure performance hints - June 7, 2008

[…] unknown wrote an interesting post today onHere’s a quick excerptThe 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. … […]

2. Pawan Srivastava - March 12, 2010

i WANT TO KNOW,HOW TO USE STORED PROCEDURE IN DOTNET.

3. Pawan Srivastava - March 12, 2010

give me simple example and use of stored procedure in dot net.

4. Pawan Srivastava - March 12, 2010

I have already good knowledge of dotnet. but i have never used stored procedure ikn dotnet.

5. fofo - March 12, 2010

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: