SQL Server

Pensa na seguinte situação: uma rotina de exibição de histórico que une várias fontes, em tabelas distintas. Para facilitar as coisas, você resolve fazer uma stored procedure no banco de dados que retorna os dados e exibe na aplicação.

Fiz uma rotina do gênero, utilizando o operador UNION do SQL Server para me retornar os dados, assim:

create procedure spRetornaHistorico(@IdCliente int)
AS
BEGIN
	select
		c.IdCliente,
		c.Nome,
		p.CodigoProcedimento
	from
		Tabela1 p
		join Cliente C on p.IdCliente = c.IdCliente
	where
		p.IdCliente = @IdCliente
	union all
	select
		c.IdCliente,
		c.Nome,
		p.CodigoProcedimento2 as CodigoProcedimento
	from
		Tabela2 p
		join Cliente C on p.IdCliente = c.IdCliente
	where
		p.IdCliente = @IdCliente
	...
END

Claro, na rotina real existem N outras tabelas com vários joins cada, com alguns tipos de dados distintos para uma mesma coluna de retorno, devido a algumas das tabelas estarem em um BD de um sistema legado, valores ausentes, etc.

Uma consulta assim demora um certo tempo, prendendo as tabelas até retornar. Imagine vários usuários executando a mesma consulta! E não à toa, ela era uma das mais demoradas quando vista pelo monitor de atividades do banco de dados.

Chegou uma hora que os usuários estavam reclamando muito de deadlocks no banco de dados. Reiniciar o serviço do BD no servidor amenizava, mas já estava na hora de otimizar essa que era a consulta mais pesada do sistema.

A solução que encontrei para deixá-la menos pesada foi abolir o comando UNION e fazer os selects separados, inserindo-os em uma tabela temporária e retornando a mesma. Veja:

create procedure spRetornaHistorico(@IdCliente int)
AS
BEGIN
	create table #TEMPORARIA (
		IdCliente int,
		Nome varchar(100),
		CodigoProcedimento int
	);
	
	insert into #TEMPORARIA (IdCliente, Nome, CodigoProcedimento)
	select
		c.IdCliente,
		c.Nome,
		p.CodigoProcedimento
	from
		Tabela1 p
		join Cliente C on p.IdCliente = c.IdCliente
	where
		p.IdCliente = @IdCliente
	
	insert into #TEMPORARIA (IdCliente, Nome, CodigoProcedimento)
	select
		c.IdCliente,
		c.Nome,
		p.CodigoProcedimento2 as CodigoProcedimento
	from
		Tabela2 p
		join Cliente C on p.IdCliente = c.IdCliente
	where
		p.IdCliente = @IdCliente
	...
	
	select IdCliente, Nome, CodigoProcedimento from #TEMPORARIA order by CodigoProcedimento asc;
	drop table #TEMPORARIA
END

Como o resultado seria todo junto em um mesmo resultset, criei essa tabela temporária e inseri os selects que compunham o UNION nela. No final ordenei-a por um critério adequado, e por último descartando-a. Embora pelo cronômetro do Management Studio o tempo da consulta em ambas as versões fossem equivalentes principalmente depois da segunda execução, o monitor de atividades mostrou exatamente a parte que demora mais, que é a consulta feita em um outro banco de dados, embora no mesmo servidor e na mesma instância do banco.

Não sei se teria outra solução mais adequada para melhorar o tempo de resposta dessa consulta, porém com a solução que adotei os chamados por deadlock cessaram…

Fica a dica!