none
Diferença na execução de sentença SQL via código para Stored Procedure RRS feed

  • 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
    sábado, 10 de novembro de 2012 09:05

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.aspx

    at.
    Rafael MElo

    terça-feira, 27 de novembro de 2012 13:19

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.com

    Se 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.

    segunda-feira, 12 de novembro de 2012 11:33
  • 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

    segunda-feira, 12 de novembro de 2012 13:36
  • 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]

    terça-feira, 13 de novembro de 2012 16:31
  • 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

    quarta-feira, 14 de novembro de 2012 14:11
  • 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

    terça-feira, 27 de novembro de 2012 12:49
  • 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.aspx

    at.
    Rafael MElo

    terça-feira, 27 de novembro de 2012 13:19
  • boa Rafael, 

    Vou estudar.

    Obrigado.


    Pablicio

    terça-feira, 27 de novembro de 2012 16:23