Nocount

Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
SELECT...
--
Please, check my theSpoke:
http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspxPaperback Writer wrote:
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only
> for SELECT...
It good practice to have a SET NOCOUNT ON at the top of every stored
procedure and also to execute upon initial connection if there is a
chance that ad-hoc SQL will be sent from the application to the server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Its not needed really.
Bojidar Alexandrov
"Paperback Writer" <newsgroupms@.gmail.com> wrote in message
news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> SELECT...
> --
> Please, check my theSpoke:
> http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>|||hahahahahahahahah
Classic.
In our environment the single biggest performance gain was obtained by using
this set option but don't take anybodys word for it. Test for yourself.
When push came to shove and I had to convice the managers of our development
team I used this simple example.
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount on
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount off
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
results from first select
Elapsed Time, Msec
--
7516
results from second select
(99999 row(s) affected)
Elapsed Time, Msec
--
7780
"Bojidar Alexandrov" wrote:
> Its not needed really.
> Bojidar Alexandrov
> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> > SELECT...
> >
> > --
> > Please, check my theSpoke:
> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> >
> >
>|||I had a loop with
BEGIN TRAN
UPDATE
COMMIT
And counter transaction per second. With the setting off, I has 800, with on, I had 2000...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our development
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
>> Its not needed really.
>> Bojidar Alexandrov
>> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
>> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
>> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
>> > SELECT...
>> >
>> > --
>> > Please, check my theSpoke:
>> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>> >
>> >
>>|||Tibor, the thing with update (or delete statements) is very clear that it
takes too much time to transfer these (x rows affected) as another
recordset, but original question was for Select statements only. When there
are staments returning stats like update and delete you are very right.
Im not sure that the example from Joe is right, so will test myself.
Bojidar Alexandrov
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23fBUhFaFHA.616@.TK2MSFTNGP12.phx.gbl...
> I had a loop with
> BEGIN TRAN
> UPDATE
> COMMIT
> And counter transaction per second. With the setting off, I has 800, with
on, I had 2000...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> > hahahahahahahahah
> > Classic.
> > In our environment the single biggest performance gain was obtained by
using
> > this set option but don't take anybodys word for it. Test for yourself.
> > When push came to shove and I had to convice the managers of our
development
> > team I used this simple example.
> > dbcc dropcleanbuffers
> > go
> > dbcc freeproccache
> > go
> > set nocount on
> > go
> > DECLARE @.start_time DATETIME
> > SELECT @.start_time = GETDATE()
> > select * from BIG_SALES2
> > SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> > GO
> > dbcc dropcleanbuffers
> > go
> > dbcc freeproccache
> > go
> > set nocount off
> > go
> > DECLARE @.start_time DATETIME
> > SELECT @.start_time = GETDATE()
> > select * from BIG_SALES2
> > SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> > GO
> >
> > results from first select
> >
> > Elapsed Time, Msec
> > --
> > 7516
> >
> > results from second select
> > (99999 row(s) affected)
> > Elapsed Time, Msec
> > --
> > 7780
> >
> >
> > "Bojidar Alexandrov" wrote:
> >
> >> Its not needed really.
> >>
> >> Bojidar Alexandrov
> >>
> >> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> >> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> >> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only
for
> >> > SELECT...
> >> >
> >> > --
> >> > Please, check my theSpoke:
> >> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> >> >
> >> >
> >>
> >>
>|||I've just tested out and do not think you are right. Mine results from 10
trys on table with about 100k rows are on the average same.
Bojidar Alexandrov
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by
using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our
development
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
> > Its not needed really.
> >
> > Bojidar Alexandrov
> >
> > "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> > news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> > > Hi, I'd like to know if i need to set Nocount On/Off in a proc only
for
> > > SELECT...
> > >
> > > --
> > > Please, check my theSpoke:
> > > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> > >
> > >
> >
> >