Performance Effects of NOCOUNT
-
Comments posted here are about the content posted at temp
-
David,
Quite an interesting twist on some things you got used to.
In fact I put this statement in all my Stored Procedures.
However I do it (at least did) because of different reason. Back in time of ADO (NOT .Net) the returned message sometimes was treated as a Recordset along with the result you expected. Technically it was a collection of Recordsets, so you needed to fetch through in order to get what you really wanted. The message always was the first in that collection and could easily break the front end logic if you tried to refer some field by name, because message did not have any named fields. I remember several times I had a hard time troubleshooting the problem when someone "optimized" SP by removing/commenting "set nocount on".
I guess it is not a problem with ADO.Net though never put it in test if message adds any table in output Dataset. Indeed it is good to know that performance is better, though 3% boost can easily be eaten in a network environment. Also it is marginaly close to the "experiment error" - the difference in 8 and 9 (with ON) is about 1.5% itself.
Anyway it is not probably bad sometimes to re-evaluate some "good practice" recommendations just to get sense of them and understand a bit better the meaning and origines.
Thanks
-
I would be interested to know if the discussions relative to specifying a default owner id are valid in a SS2005 environment. In SS2005 Logins are mapped to default schemas via a User Mapping. Seems like specifying default owners in the stored procs could actually cause problems if you want a Login to access tables with the schema specified in the User Mapping.
Can anyone clarify the SS2005 logic?
-
Excellent article!
I knew it increased performance setting nocount off; the fact that the performance increase is so slight is interesting.... I would have expected more of a performance increase.
Mark
-
Great article with interesting points. I'm going to have to run tests to see what impact this has on SQL Moblie.
David, as far as the owner thing goes I see one possible hole in how you set up your experiments. You did the creates and tests with the same user. More interesting would be to log on as dbo and do the create, then log on as a user and do the tests. Often owner based object searches do the local logon first and then work up the tree. Some start at the top of the tree and work down so that globally available objects come up first.
ATBCharles Kincaid
-
we had a situation where a job on a server was performing in a couple of minutes while on a remote pc (wan connected) it took 30+ minutes. changing this setting made the performance comparable with running on a server. the exact timings i dont remember but it was over 50% time reduction.
-
Hi David,
That's a good article. Thank you very much for it - its good to have theories be tried and tested.
A couple of comments.
1) Is there there a reason you didn't run
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
on each iteration? I don't know if it would have made a difference or not - perhaps its worth trying out.
2) You've created four sprocs there. In the interests of a proper scientific experiment where one should control variables properly - you should make sure there is only one difference between the things you are testing, should you not? Well AddTestWithNoCount & AddTestWithoutNoCount have TWO differences. The former specifies NOCOUNT and does not specify a schema qualifier for the table. The second does not specify NOCOUNT but also DOES specify a schema qualifier. Hence - two differences. If you are testing the effect of NOCOUNT then shouldn't all other things be equal?
Minor points though. its a good article.
Thanks
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
When first glancing at the performance chart, it appears that SET NOCOUNT ON is giving a 2-3x performance increase (that would be incredible news). Its not a good idea to "zoom" in to the top of a graph in these types of performance based articles. I would suggest showing the graph in its true form where the vertical range is from 0 to 4000 (instead of 3400-3700). The results of only a 3% increase will be alot more obvious then.
-
Agreed - the graph is misleading in the extreme. That completely passed me by so thank you to Michael for point it out.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
It increases slightly mostly because of the messages produced by
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
themselves. Looks like it adds a little more than a mintue to each run.
Also even thou it is fractional the savings wasn't just about time it has also been stated as reducing network overhead. But either way when you are trying to streamline your process and keep interaction speed high doing this is still a bennefit overall. Especially on servers with high loads.
-
would this be a good practice inside triggers?
CREATE TRIGGER tr_X ON X
FOR INSERT
SET ROWCOUNT OFF
...
GO
SET ROWCOUNT ON
INSERT INTO X
...
-
Back in time of ADO (NOT .Net) the returned message sometimes was treated as a Recordset...
I didn't come across this problem with ADO but at one place I worked they had a series of libraries based on DBLIB where this occurred.
Different versions of DBLIB produced different results.
It caused a lot of problems in development because development was still taking place on legacy systems over and above that being done on the newer systems. It was easy to get mixed up as to which system could cope with which coding method.
-
I use SET NOCOUNT ON wherever possible. There seems little point gain in stored procedures only to lose in triggers.
-
Got to be honest i am slightly confused here.
The graph and the text below seem to state that turning it OFF will slow the response, yet then it says the savings of turning it on.
Then people on this forum seem to say turning it on is speeding it up. What is going on? I am getting REALLY confused.
If someone could explain that would be great!
Dan
-
Before you re-engineer any procs to use 'SET NOCOUNT ON', check your developers aren't using the built in optimistic concurrency checks in ADO.Net.
This requires 'SET NOCOUNT OFF' to function correctly.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply