Set NOCOUNT On in Stored procedures - ADO.NET

"SET NOCOUNT ON" in all stored procedures - it reduces the network traffic for queries. It's probably a good general practice, but the network traffic savings would be pretty minimal in most cases. This would be more important in a case where a very high volume of SQL statements with small result sets were being run, because the rowcount information would comprise a more significant portion of the network traffic. NOCOUNT doesn't make any difference on the database load, since @@ROWCOUNT is still populated regardless of the NOCOUNT setting.

NOCOUNT can make a significant performance difference if you are executing a large number of SQL statements, such as in the example Scott Whigham points out in his comment or the example in this article. That's because each executed SQL statement reports completion, which can cause a lot of network traffic just to report "1 row inserted.1 row inserted. 1 row inserted."

Previously, in Classic ASP and ADO, NOCOUNT was most commonly a factor if you were checking RecordsAffected on Command.Execute(). SqlDataReader does have a RecordsAffected property, but it's rarely used since it's not populated until the datareader has iterated all the rows and the datareader is closed. There are some possible implications if you're using DataAdapters to submit changes to the database, since it uses the rows affected result to determine if the update succeeded. Probably the easiest way to check for that case is to search the codebase for SqlDataAdapter and see if the Update() method is being called.

Get more information

Can't find what you're looking for? Try Google Search!

	Web eshwar123.blogspot.com