SET NOCOUNT ON Performance

I was a little skeptical of the NOCOUNT ON boosting query performance so I went to test it. My new job requires me to wear both DBA and DB Developer hats so I am trying to get up to speed on the basics.

I created a customer table with 3,000 rows. I used http://www.generatedata.com/#generator to generate some data. Love that site. You can quickly generate data free (I know there are probably better pay-to-play tools out there, but this is free).

I then created two very simple Stored Procedures.

SelectCustomer

create procedure SelectCustomer as
set nocount on
 select * from customer

SelectCustomer2

create procedure SelectCustomer2 as
select * from customer

I turn on query statistics using set statistics time on, and called the procs. Execution times vary, but one constant was the fact that SelectCustomer performed a lot better than SelectCustomer2. In 3 samples SelectCustomer performed 24%, 34%, and 35% better. These numbers seem skewed, and I would not go by the drastic performance gains of this little test case. But one thing for sure the SET NO COUNT ON does provide a performance boost. I imagine the performance boost on proc that returns several million rows could be drastic.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s