Muitas inserções ou consultas? Melhore a performance com SET NOCOUNT ON

Olá pessoal, aqui vai um post mais simples mas bastante útil. Caso você tenha uma stored procedure ou function que executa muitos INSERTs, UPDATEs, DELETEs e SELECTs no banco de dados e você precisa retornar estes dados para outra máquina e nem usa as messages do servidor nesta outra máquina para contar o número de registros afetados, coloque um SET NOCOUNT ON no início da procedure e veja se há melhora no desempenho.

Estou conectado em um servidor SQL Server 2008 via TCP/IP na Internet, link de 10Mbit e executando os scripts via Management Studio. Então caso você tenha uma situação similar e queira comprovar os benefícios do SET NOCOUNT ON de uma maneira bem simples, execute primeiro esta rotina:

create proc TESTE_NOCOUNT_OFF as

begin

      create table #temp (

            ID int not null primary key,

            CAMPOTESTE varchar(10)

      )

      set nocount off

      declare @contador int

      set @contador = 1

      while @contador < 100000

      begin

            insert into #temp (ID, CAMPOTESTE) values (@contador, 'TESTETESTE')

            set @contador = @contador + 1

      end

      drop table #temp

end

GO

exec TESTE_NOCOUNT_OFF

GO

drop proc TESTE_NOCOUNT_OFF

GO

Anote o tempo de execução com o NOCOUNT desligado (aqui rodou em 44 segundos) e depois execute esta rotina, com o NOCOUNT ligado:

create proc TESTE_NOCOUNT_ON as

begin

      create table #temp (

            ID int not null primary key,

            CAMPOTESTE varchar(10)

      )

      set nocount on

      declare @contador int

      set @contador = 1

      while @contador < 100000

      begin

            insert into #temp (ID, CAMPOTESTE) values (@contador, 'TESTETESTE')

            set @contador = @contador + 1

      end

      drop table #temp

      set nocount off

end

GO

exec TESTE_NOCOUNT_ON

GO

drop proc TESTE_NOCOUNT_ON

GO

Agora sem as mensagens rodou em 1 segundo. Legal, não? O motivo da demora com as mensagens não é o consumo de CPU ou I/O, mas sim rede. Como o SQL Server transmite por padrão as mensagens com os contadores, independente destas serem usadas pelo cliente, a performance da rotina sofre um grande impacto. Eliminando as mensagens, é eliminado este overhead e há uma melhoria sensível na execução.

Qualquer dúvida, avisem. Obrigado por visitar o blog!