Usuário com melhor resposta
Diferença na execução de sentença SQL via código para Stored Procedure

Pergunta
-
Pessoal,
Preciso de ajuda para entender a relação de tempo de execução entre a execução de uma sentença SQL através do ADO para uma sentença executada por uma stored procedure que retorna um resultado para o ADO (visual basic 6.0). Fiz alguns testes utilizando o ADO e em alguns momentos fiz utilizando o management studio do sql server.
Fiquei completamente intrigado pelo fato da execução da sentença que está dentro da procedure ser executa de forma mais rápida do que a própria procedure. Utilizei para essa contatação a mesma máquina e mesmos parâmetros.
Tenho procedure que cria #Temp, achei que esse era o problema, mas a execução da sentença sql direta (incluindo a #temp) fica ainda mais rápida.
Sinceramente não entendi qual a relação de tempo e por que uma é mais rápida do que a outra e pela minha falta de argumentação estamos caminho para tirar todas as procedures do sistema e colocar diretamente dentro do código (ação que eu sou completamente contra pelo simples fato de perdemos mobilidade na atualização/correção de versão - mas está me faltando argumentos e forma de melhorar a performance)
Já tentei utilizar with (nolock) nos selects, with recompile nas procedures e mesmo assim a situação não está boa (importante salientar que tenho clientes que utilizam sql 2005/2008/2012).
Importante:
- tem uma diferença muito grande no teste é que na cláusula Where utilizo um OR para ter certeza se o parâmetro foi passado ou não. Se tiver passado eu utilizo senão eu o ignoro. Essa forma de escrita de sp veio do sql 2000, pois não conseguíamos concatenar valores. Existe alguma forma de melhorar isso? Tem como reescrever a procedure para que fique mais rápida?
- utilizo a #temp dentro das SPs por que o ADO tem um problema muito sério com as colunas identity. Observe que eu preciso converter a coluna para int para devolvê-la da mesma forma, pois se eu não fizer isso, quando incluo um registro novo pelo o recordset apresenta problema e dá erro por não reconhecer a coluna identity. Existe alguma outra saída para eliminar a #temp dentro desse conceito?
Então a dúvida é por que um é mais rápido do que o outro?
Mais uma vez estou abusando da boa vontade dos colegas, mas acredito que é uma discussão pertinente e que trará frutos a todos.
Um abraço.
Pablicio
Exemplo do código da strored procedure que utlizo
USE Banco
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='PR_Teste' AND type='P')
BEGIN
drop proc PR_Teste
END
GO
create proc DBO.PR_Teste (
@Parametro01int,
@Parametro02 int,
@Parametro03 varchar(20),
@numLinAlt int output,
@numErro int output,
@txtErro varchar(255) output
)
WITH ENCRYPTION
as
declare @CodRetorno int
SET IDENTITY_INSERT adm_aidf ON
SET NOCOUNT ON
select @numLinAlt=0, @numErro=0, @txtErro=''
SELECT
CAST(colunaidentity AS INT) AS colunaidentity,
Colunas....
INTO #TEMP FROM tabela
WHERE (coluna1 = @idaidf OR @Parametro01 is null)
AND (coluna2 = @Parametro02 OR @Parametro02 is null)
AND (coluna3 = @Parametro03 OR @Parametro03 is null)
SET IDENTITY_INSERT adm_aidf OFF
SET NOCOUNT OFF
SELECT * FROM #TEMP WITH (NOLOCK)
DROP TABLE #TEMP
SELECT @numErro = @@error, @numLinAlt = @@rowcount
if @numErro <> 0
begin
SELECT @txtErro = 'Erro ao selecionar tabela. - PR_Teste'
return(@CodRetorno)
end
return(0)
GO
GRANT EXECUTE ON PR_Teste TO PUBLIC
GO
Pablicio
- Editado Pablicio sábado, 10 de novembro de 2012 09:06
Respostas
-
Bom dia Pablicio,
Posso estar errado, mas para mim essa diferença de desempenho pode estar sendo gerada pelo "parameter sniffing", que é um comportamento que ocorre quando utilizamos stored procedures com parâmetros...
http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices-150-Parameter-Sniffing.htm
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspxat.
Rafael MElo- Marcado como Resposta Gustavo M. Guimarães segunda-feira, 17 de dezembro de 2012 13:43
Todas as Respostas
-
Pablicio,
Pelo o que entendi, você tem uma procedure que está demorando mais para ser executada do que o código implementado direto na aplicação. Para que a gente possa realizar uma análise com uma maior precisão acredito que seria interessante você postar um exemplo de procedure que você utilizou e como ficaria uma implementação dela direto no VB6. Se não acho que só podemos dar suposições para o problema.
Guilherme Costa
Email: guilerme18@hotmail.comSe esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta. -
Olá Guilherme, obrigado por ter retornado.
A ideia da SP está descrita acima. Eu não posso colocar uma SP da empresa sacou? senão estou quebrando a questão de sigilo :-)
De qualquer forma, a implementação no VB6 é a pura sentença SQL
Valeu mais uma vez!
Pablicio
-
Pablício,
Realmente é um cenário bastante estranho e ainda mais quando se trabalha com o VB6!!!
Faça o seguinte, através do SQL Server Profiler, monitore como a Procedure esta sendo chamada e executada por sua aplicação.
Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
-
Pablicio,
infelizmente já tive um problema bem parecido com o seu.
Li tanto a respeito e eu cheguei a achar que estava ficando com meu sistema maluco, ou eu maluco, mas você tem razão. Dependendo da configuração do ADO, do seu grid, até mesmo do seu servidor, só vai pegar alguma diferença na analise do tracert/profiler, plano de execução, leitura de disco, memoria, buffer.Na época usei estes argumentos:
O código fica todo centralizado no SQL, podendo ser criptografado se for o caso;
São pré-compiladas, tornando-as mais rápidas na execução. No RP, C# ou VB6, a cada chamada de uma sintaxe, ela é compilada novamente e se o banco de dados for remoto, menos dados irão trafegar na rede;
Desempenho de uma SP em relação a sintaxe pode parecer imperceptível, mas será centralizado, todas as consultas. Imagina voce colocar este seu select direto no codigo, e sabendo que vai precisar dele em outras chamadas/pagina? Como que voce vai saber quais as paginas estao com determinadas tabelas sendo usadas?
Cheguei a montar uma apresentação em PPT explanando.Se puder, poste um teste com do windows monitor com alguns ponteiros para analisarmos juntos.
[]´s
-
Advaldo,
Obrigado pelo apoio.
Gostei da ideia do PPT (se puder compartilhar será uma boa).
O que tem pegado bastante em nossas sps é que ela tem uma estrutura de validação na clausula where em relação aos parâmetros que são passados. Talvez aqui seja o que preciso trabalhar e encontrar uma sugestão, se puder ajudar será uma boa.
No select que coloquei acima (como exemplo de sp) observe que a sp recebe três parâmetros e testa na clausula where se o parâmetro foi informado ele utiliza, senão ignora o parâmetro
SELECT
CAST(colunaidentity AS INT) AS colunaidentity,
Colunas....
INTO #TEMP FROM tabela
WHERE (coluna1= @Parametro01OR @Parametro01 is null)
AND (coluna2 = @Parametro02 OR @Parametro02 is null)
AND (coluna3 = @Parametro03 OR @Parametro03 is null)Se este código ficar dentro da aplicação (VB, c# ou qualquer outra), o desenvolver testa o parâmetro e passa direto para a clausula where somente se tiver valor.
Se eu conseguir resolver isso (ainda não consegui) será mais uma argumentação, ou seja, a sp está compilada e disponível para ser usada sem testar a clausula where, ai teremos vantagens reais a respeito disso.
Se para cada parâmetro eu tiver que criar uma sp específica, terei uma projeção geométrica quanto as sps.
Você tem ideia de como resolver essa questão (tirar o OR da clausula where)?
Valeu!!!
Se puder enviar o ppt será ótimo (pabliciogr@gmail.com)
Pablicio
-
Bom dia Pablicio,
Posso estar errado, mas para mim essa diferença de desempenho pode estar sendo gerada pelo "parameter sniffing", que é um comportamento que ocorre quando utilizamos stored procedures com parâmetros...
http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices-150-Parameter-Sniffing.htm
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspxat.
Rafael MElo- Marcado como Resposta Gustavo M. Guimarães segunda-feira, 17 de dezembro de 2012 13:43
-