Otimizando a performance de aplicações com o uso de stored procedures

Olá pessoal,
    

     No artigo de hoje vou comentar sobre o ganho de performance com o uso de stored procedures (SPs), nas aplicações que acessam e/ou atualizam dados em Sistema Gerenciadores de Bancos de Dados Relacionais. As stored procedures, bem como, as functions, packages e triggers, foram implementadas na versão 7 do Oracle Database (1992) e seu uso (em substituição ao uso de instruções SQL submetidas individualmente) pode otimizar drásticamente a performance das aplicações que utilizam SGBDs relacionais, principais aquelas que executam grandes transações.
    

     Apresentarei neste artigo, uma aplicação bem simples que chama uma stored procedure (SP), que   por sua vez, realiza uma transação de transferência bancária entre 2 contas correntes e que contém uma regra de negócio, também simples, para verificar se existe saldo na conta de origem. Sei que muitos profissionais de TI são contra codificar regras de negócios dentro de SPs, pois aprendemos em Engenharia de Software moderna, que as regras de negócio devem ficar na aplicação, em um componente separado, que por sua vez, deve ser armazenado em um servidor de aplicação (e nunca no BD), mas tenho certeza de que você vai gostar do resultado do final deste artigo, se sua prioridade for performance!

     Já desenvolvi várias aplicações com regras de negócio tanto no servidor de aplicação quanto em stored procedures, por isso, posso afirmar que sempre consegui melhor performance naquelas aplicações que chamavam stored procedures. Porém, é importante avaliar e testar cada caso. Para quem nunca codificou regras de negócio em stored procedures, isso pode parecer estranho ou errado, mas a vantagem do ganho de performance, justifica (e muito) a prática. Grandes transações que envolvem manipulação condicional de dados podem ficar mais rápidas com SPs. Entre diversos benefícios de segurança e performance que as stored procedures proporcionam, o principal e que eu acho mais fácil de explicar, é que elas reduzem o tráfego de dados pela rede e o tempo de espera destes dados pela aplicação.

     É muito simples o raciocínio: se uma transação realiza 10 operações no Banco de Dados, o que é mais rápido: sair do servidor de aplicação e ir ao BD 10X (trafegando 10X pela rede), ou ir uma 1X só, executar as 10 operações e retornar o resultado para o servidor de aplicação? Quando você tem que comprar 10 produtos no mercadinho da esquina, o que é mais rápido? Ir 10X trazendo cada vez 1 único produto ou levar uma sacola e trazer os 10 produtos de uma só vez?

     Em aplicações críticas, que exigem alta performance, o que é mais importante: seguir a regra de criar aplicações N camadas (com regras de negócios SEMPRE em um servidor de aplicação) ou usar recursos alternativos (neste caso, as stored procedures) para desenvolver uma aplicação mais rápida? 
    

    Vou demonstrar a seguir, um exemplo de uma aplicação que eu desenvolvi com o Dot Net Framework 3.5, para provar o conceito de que SPs podem otimizar a performance das aplicações. A aplicação, chamada Teste de performance de transações (ver Figura 1), poderá ser baixada para testes e é totalmente parametrizável (ver arquivo TesteTransacao.exe.config). Ela simula a realização de simples transferências entre contas bancárias, retirando o valor de uma conta corrente (conta origem) e depositando o respectivo valor em outra conta corrente (conta destino).

  

Figura 1 - Tela principal da aplicação "Testes de performance de Transações"

    

     A operação de transferência ocorre em modo transacional (deve fazer tudo ou nada, se 1 passo falhar, desfaz os passos anteriores) e é composta por 3 passos sequenciais:

          1) Verificar se a conta origem possui saldo para efetuar a transferência;

          2) Retirar (sacar) valor da conta origem;

          3) Depositar valor na conta destino;

        

             Obs.: Os passos 2 e 3 são executados somente se a conta origem possuir saldo (verificado no passo 1).

     A aplicação está disponível para download no MEU ONE DRIVE (ver final do painel direito das páginas do meu blog), pasta Oracle -> Scripts, arquivo TesteTransacao.zip. Para efetuar a instalação e utilizá-la, siga os passos abaixo:

        1- Descompacte o arquivo TesteTransacao.zip informando uma senha que deverá ser obtida assinando a newsletter que encontra-se no painel direito deste blog.

        2- Conecte-se no BD desejado e instale os objetos de BD (tabela CONTA e package PKG_CONTA) que estão no arquivo Script_BD_Teste_Transacao.sql. Instale os objetos no schema de um usuário que será utilizado pela aplicação para conectar-se no BD.

        3- Configure os valores (values) dos parâmetros (keys) do arquivo TesteTransacao.exe.config, conforme indicações abaixo:

             a) instance_name = Nome da instância do BD onde os objetos foram criados. Especificar nome de uma instância cadastrada no arquivo tnsnames.ora da máquina em que a aplicação irá ser executada;

             b) user_name = Nome do usuário que a aplicação utilizará para conectar-se no BD;

             c) pwd_user = Senha do usuário que a aplicação utilizará para conectar-se no BD;

             d) idContaOrigem = Número da conta origem;
             e) idContaDestino = Número da conta destino;

             f) vlInicialContaOrigem = Valor inicial da conta origem;

             g) vlInicialContaDestino = Valor inicial da conta destino;
             h) intTotalInteracoes = Valor indicando qtde. de operações de transferências que serão realizadas;
             i) vlTransferencia = Valor da transferência.

     Observações: São pré-requisitos para executar esta aplicação, ter o Dot Net Framework 3.5 instalado (SOs Windows mais recentes já possuem) e o Oracle Data Provider for .Net.

  

    Para efetuar os testes de performance, basta clicar nos 2 botões existentes na tela principal da aplicação: Transferência SQL e Transferência com SP. O botão Transferência SQL submete instruções SQL para o BD, enquanto que, o botão Transferência com SP, executa uma stored procedure no BD (executando dentro de uma procedure os 3 passos em uma única chamada ao BD).

 

     Vejam abaixo, a performance de testes que eu fiz:

  

TESTE executando 1000 transferências bancárias

    a) Tempo de Transferência do botão Transferência SQL (ver Figura 2): 3,8s

Figura 2 - Teste de 1000 transferências com SQL (ad hoc)

     b) Tempo de Transferência do botão Transferência com SP (ver Figura 3): 1,7s

Figura 3 - Teste de 1000 transferências com SP

Observações:

    Também disponibilizei o código-fonte da aplicação deste artigo (arquivo Fontes_TesteTransacao.zip), na mesma pasta que disponibilizei a aplicação. A senha para descompactação deste arquivo é a mesma do arquivo da aplicação.

CONCLUSÃO:

     Nos testes realizando 1000 transações conseguimos verificar que a performance delas utilizando SP foi bem superior, apresentando um desempenho 220% mais rápido. A transação bancária deste artigo é bem simples e possui apenas 3 operações. É importante ressaltar que em transações maiores, o ganho de desempenho também será maior!

     Mais detalhes, sobre a estrutura da aplicação, o porquê da stored procedure apresentar um desempenho superior neste exemplo e sobre a implementação de regras de negócio na aplicação ou na stored procedure, eu deixo para explicar nos seguintes treinamentos:
          - SQL Tuning (presencial);
          - PL/SQL Essentials and Tuning (presencial);
          - PL/SQL Tuning (videoaulas).

  Para aqueles que quiserem verificar a opinião de mais profissionais sobre o desempenho das stored procedures e a questão polêmica das regras de negócios dentro delas, sugiro a leitura da discussão Regras de Negócio em Stored Procedures ou Desenvolvimento em Camadas, no Linkedin, Grupo NET Framework - Brasil.

Bom pessoal, por hoje é só!
Espero que tenham gostado. Qualquer dúvida, é só deixar um comentário.

[]s