How nocount affects ADO. Net (set nocount on performance)

How nocount affects ADO. Net mentioned in the article:

Previusly, 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. probablyEasiestWay to check for that case is to search the codebase for sqldataadapter and seeIf the update () method is being called.

Also:

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.

For insurance purposes, it seems unreliable to call Update () by the author. Many steps may cause problems and affect the final result. At the same time, debugging is also troublesome. After SQL debug is embeddedProgramIt is very useful, and if the debug functions are all removed as mentioned by the author. And separated the database and application.

Someone in the comment wrote a simple test program and gave his test results. He thought it was still set nocount on.

Stored Procedure for testing
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

I tested it on XP + SQL 2005 ent with SP1 in different environments.Opposite conclusion, Share the result data to everyone:

Nocountoff | nocounton | xrate

7106 5893 20.58%
6156 6890-10.61%
7093 7343-3.41%
5780 6406-9.77%
5563 6860-18.91%
5610 7390-24.09%
5810 6923-21.38%
6296 7436-15.33%

-- Insert 1
46 0
16 0
0 0
0 0
16 0

-- Insert 1000
420 393 6.88%
423 390 8.47%
966 423 128.37%

-- Insert 100,000
43250 42860 0.91%

-- Delete all then insert 100,000
42076 39826 5.65%

If you test it yourself, you can see that the result has little effect on the proof.