Click here to Skip to main content
Click here to Skip to main content

Tagged as

SQL Optimization Tip

, 16 Nov 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Use SET NOCOUNT ONWhenever we write any procedure and execute it a message appears in message window that shows no of rows affected with the statement written in the procedure and we become very happy to see that our procedure is working. But do you know that this message creates an extra overhead o

Use SET NOCOUNT ON

Whenever we write any procedure and execute it a message appears in message window that shows no of rows affected with the statement written in the procedure and we become very happy to see that our procedure is working. 

But do you know that this message creates an extra overhead on the network? Yes it does.

By removing this extra overhead from the network, we can actually improve the performance of our database and our application.

How should we do it?

Solution 

When you create any procedure then first line of your procedure should be

SET NOCOUNT ON; 

This one line of code turns off the message that SQL server sends back to front end after every T-SQL statement is executed. This is applied for all SELECT, INSERT, UPDATE and DELETE statements. As when stored procedures are executed there is no need to pass this information back to front end.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.If we still need to get the count of no of rows affected, we can still use @@ROWCOUNT option. Because The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005. 

Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Thanks,
Virendra Dugar 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

jQuery By Example
Technical Lead
India India
I am an experienced Software Developer with 6+ years of hands-on experience working with Microsoft .NET technology (ASP.NET, C#, VB.NET,SQL Server).
 
Visit my blog and you can also follow me on Facebook
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralThank You PinmemberChrisHar4-Dec-09 6:31 
GeneralRe: Thank You PinmemberVirendra Dugar4-Dec-09 6:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 16 Nov 2009
Article Copyright 2009 by jQuery By Example
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid