How NOCOUNT affects ADO.NET

A co-worker recommended we include "SET NOCOUNT ON" in all stored procedures, since it reduces the network traffic for queries. I did a bit of digging; here's my take on it:

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.

UPDATE: 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." That's a very valid point, and while I do stand corrected I'd point out that it's a bit of a contrived countercase. I definitely wouldn't recommend looping inserts if there's any way you can perform a bulk or batch insert. I've recently trimmed SQL execution time for a single proc from 8 hours a few minutes by converting from looping inserts to an INSERT...SELECT statement (no, I didn't write the original proc...). The point is well taken, though - if the SQL call will execute a significant number of operations, by all means SET NOCOUNT ON.

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.

In our case, we determined that it would be good to use on a going forward basis, but the minimal savings in network traffic didn't justify the risk of modifying existing procedures.

Tech notes if you're interested in tracing how RecordsAffected is set and used by pointing Reflector at System.Data:
System.Data.SqlClient.TdsParser.ProcessDone() sets RecordsAffected.
System.Data.Common.DbDataAdapter.UpdateRow() uses reader.RecordsAffected in determining whether to call AcceptChanges and ApplyToDataRow.

Please let me know if I got any of this wrong or you disagree with my conclusions.

5 Comments

  • What does SQL Server's Management Studio use to connect? I'm guessing ADO.NET but I really don't know.

    Anyway, I was skeptical of your post since it goes against the logic I've always heard/used - no offense :) Anyway, I ran a quick test:

    DECLARE @Start DATETIME, @End DATETIME, @Counter INT, @NoCountOff INT, @NoCountOn INT

    CREATE TABLE #MyTable (
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , FirstName VARCHAR(128) NOT NULL
    , LastName VARCHAR(128) NOT NULL
    )

    SET NOCOUNT OFF
    SET @Counter = 1
    SET @Start = GETDATE()
    WHILE @Counter <= 150000
    BEGIN
    INSERT #MyTable (FirstName, LastName) VALUES ('Scott', 'Whigham')
    SET @Counter = @Counter + 1
    END
    SET @End = GETDATE()
    SELECT @NoCountOff = DATEDIFF(ms, @Start, @End)

    SET NOCOUNT ON
    SET @Counter = 1
    SET @Start = GETDATE()
    WHILE @Counter <= 150000
    BEGIN
    INSERT #MyTable (FirstName, LastName) VALUES ('Scott', 'Whigham')
    SET @Counter = @Counter + 1
    END
    SET @End = GETDATE()
    SELECT @NoCountOn = DATEDIFF(ms, @Start, @End)

    SELECT @NoCountOff AS 'NoCountOff', @NoCountOn AS 'NoCountOn', COUNT(*) AS NumberOfRows FROM #MyTable

    DROP TABLE #MyTable

    When I run it on a Windows 2003 machine with SQL Server 2005 DE, SET NOCOUNT ON is way faster, particularly over a large number of rows. On average, I think it was ~20% faster.

    The script isn't precise since I'm using DATETIME but, as a proof of concept script, I think it shows the difference in speed. Do you have any examples that show SET NOCOUNT OFF performs as fast as SET NOCOUNT ON? I'm interested to see if I can eliminate that pesky line of code :)

    BTW - I was running a test on (local); I would expect to see the difference grow if you test across a network.

  • Scott, thanks for your comment. I updated my post based on that information.

    By the way, I wish your blog allowed comments. I tried to leave one but you only allow comments from users with Blogger.com accounts.

  • I have run the script and here's my results:

    NoCountOff NoCountOn NumberOfRows
    16830 16810 300000

    Or even more cuirious:

    NoCountOff NoCountOn NumberOfRows
    18596 20170 300000

    Run on remote server in Management Studio.

  • Aftre trying few combinations I put Set nocount off at the beginning and the Set nocount off at the very end before the last query, before the output of SP, and that solved the problem. Before this solution ADO took 10 time resources thae management studio

  • Great point!
    I'm not using SET NOCOUNT ON if I want to use the recordsaffected property.
    For other cases I'll do some tests to see how it goes.

Comments have been disabled for this content.