Social Icons

17 de set. de 2011

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

13 comments:

  1. Olá Fabio !

    Bacana o artigo, parabéns !

    Att,

    Sakamoto

    MyTracelog - Registro de um DBA
    http://mytracelog.blogspot.com

    ResponderExcluir
  2. Fábio, acredito que a escolha entre a lógica estar na aplicação ou no BD deva levar em conta qual o SGDB utilizado. Nâo conheço muitos, mas como o Oracle armazena na área cache de SQL e a partir do 11g no cache PLSQL o código, o resultado e metadados dos objetos envolvidos, ganhamos muito em performance, já que a análise por parse não precisa ser executada sempre que a SP for executada. É uma boa disccussão, e os recursos do BD escolhido também são fundamentais na decisão. Abraços.

    ResponderExcluir
  3. Olá Christiano,

    Desenvolver com SPs é uma boa opção para otimizar performance em qq BD. Já desenvolvi aplicações c/ SPs em MySQL, SQL Server e Oracle. Em todos eles as SPs podem otimizar performance. No Oracle 11G fica melhor ainda devido ao recurso de cache de SQL/PLSQL que existe nesta versão do BD e q vc mesmo comentou!

    A discussão, qdo o pessoal opta por não usar SPs gira mais em torno da arquitetura, mas se vc pesquisar pessoas q já trabalharam com SPs, verá q estes sempre afirmam que a performance fica melhor. Para sair da teoria e comprovar a melhor performance, criei a aplicação deste artigo para q qq um possa ver o código e testar.

    []s

    ResponderExcluir
  4. Parabéns pela matéria. Com certeza, tudo o que é executado no banco usando pl/sql (functions/s.proc/pkgs) aumenta a velocidade de acesso, e se ainda usar acesso direto ao banco sem o uso de odbcs e sim drivers de acesso nativo o desempenho tende a aumentar ainda mais. (Adriano Boller)

    ResponderExcluir
    Respostas
    1. Obrigado Adriano. Na aplicação que eu desenvolvi para este artigo estou usando bibliotecas da Oracle para fazer acesso nativo.

      Excluir
  5. Outro ponto que o sr poderia abordar é o armazenamento de dados em tabelas particionadas, sendo um recurso que veio na versao 8 do oracle, que ajuda ainda mais a aumentar a performance de banco de dados. Onde é possível colocar faixas de dados (semestres/bimestres/mes/anos) em diferentes hds, quando os dados de apenas uma tabela ultrapassam a capacidade fisica e quando se observa uma lentidão em um módulo de sistema, especificamente numa tela q esta deixando a desejar. Falando em performance e particionamento, é possivel colocar os dados e os indices em discos diferentes. Poderia dar o exemplo de performance comparativa da gravação de dados linha a linha com commit, a gravação em bloco com um só commit e a gravação de dados com transaction, verificação de sql erro e hardware erro, com totalização de erros e fechamento com rollback ou commit final em outra matéria. Tudo isso, pode ajudar a melhorar os sistemas desenvolvidos hoje e entregar a informação solicitada em um curto espaço de tempo. (Adriano Boller)

    ResponderExcluir
    Respostas
    1. Adriano,
      Publiquei em 17/02/2011 um artigo em meu blog, que demonstra como criar tabelas particionadas e a performance delas: http://www.fabioprado.net/2011/02/criando-tabelas-particionadas-para.html.

      Excluir
  6. Boa tarde meus caros.
    Corrijam-me se estiver errada:
    Ouvi dizer que quando muitos usuários acessam o banco ao mesmo tempo, causa mais lentidão na rede quando é em Store Procedures(pois os comandos rodam no servidor). Agora, se tu coloca o comando sql na aplicação, o comando roda na máquina, e não no servidor, evitando com que cada máquina de cada usuário congestione a rede.
    É isso mesmo que ocorre?
    Obrigada.

    ResponderExcluir
    Respostas
    1. Bibi,
      É ímpossível causar mais lentidão na rede usando SPs ao invés de SQL na aplicação. Quando vc executa uma SP a aplicação faz um único round trip pela rede. Se vc tirar por exemplo, 2 instruções SQL de uma SP e executar elas direto na aplicação, vc terá 2 round trips, ao invés de apenas 1. Quanto mais round trips, maior gargalo e tempo de espera pelos dados na rede.

      Teoricamente o que pode melhorar quando vc tem um servidor de aplicação e não executa stored procedure é q quando vc executa regras de negócio complexas, vc elimina a sobrecarga do servidor de BD, executando-as diretamente em um servidor de aplicação dedicado. Isso é bonito teoricamente, na prática, sempre vejo ficar mais rápido executando diretamente no BD, pois além de ter menos round trips, o servidor de BD normalmente tem um hardware muito superior ao servidor de aplicação!

      Quem nunca usou stored procedures discrimina o seu uso pelo fato de não acreditar que isso pode otimizar performance. Pergunte a quem já usou e comparou os 2 cenários! Todos que eu conheço testemunham que com stored procedures vc terá melhor performance. Vc mesma poderá testemunhar isso executando a aplicação que estou compartilhando neste artigo. Eu disponibilizo inclusive o código-fonte para vc ver que não há nenhum truque no ganho de performance usando stored procedure.

      []s

      Excluir
  7. Ola fabio,

    Li em um debate abordava a questao de deixar as regras de negocio armazenadas em SP's pode levar a desorganizacao, pois, qualquer desenvolvedor poderia "meter a mao" , entretanto, acho que o DBA possui o poder de restringir acesso a essas SP'S para nao deixar as regras desorganizadas, pensar da minha maneira estaria incorreto? Tambem tenho outra questao a apontar, É mais facil desenvolver regras utilizando SP's ou outros recursos como linguagem de programacao SQL ,java , etc... ? E por que as SP's possuim o poder de diminuir o trafego de rede? Obrigado!

    ResponderExcluir
    Respostas
    1. Tiago, a questão das regras de negócio dentro da SP tem vantagens e desvantagens. O que eu gosto de ressaltar é a vantagem do desempenho que isso proporciona dentro de uma transação.

      Qto à sua 2a. pergunta, sim eu acho mais fácil escrever código em SPs do que em JAVA , Dot Net ou outras linguagens de programação mais avançadas. Por que acho isso? Porque PL/SQL é uma linguaguem muito mais simples!

      Qto à sua última pergunta, quando vc executa uma transação que tem por exemplo 10 SQLs, a aplicação chama a SP, aguarda a execução de tudo e no final o BD retorna uma mensagem para a aplicação. Quando esses 10 SQLs estão fora da SP, cada SQL faz uma viagem (round trip) na rede. Agora eu lhe pergunto, o que é mais rápido? Vc ir ao supermercado uma única vez, encher um pacote com 10 produtos e voltar para casa ou ir 10 X ao supermercado e trazer 1 produto por vez?

      []s

      Excluir
  8. Vim do futuro pra acrescentar algo aqui. O que costumo ouvir é que pra manter uma equipe capacitada a desenvolver procs com qualidade o custo do profissa é alto demais. Preferem deixa o JPQL (maldito) criar qualquer coisa lá e arriscar fazendo uso de um programador meia boca. Dai o custo vai pro upgrade de server de banco.... Complicado, pois sou especialista em performance de dados e explicar pros clientes que as querys ad-hocs e JPQS da vida são os maiores culpados pela lentidão torna-se repetitivo... sempre escuto essa ladainha.

    ResponderExcluir
    Respostas
    1. Acredito no seu depoimento, e acho que o problema geral é esse mesmo. Infelizmente isso é como construir um prédio que não suporta remotos (para economizar) ao lado de um vulcão. Se o vulcão entrar em erupção, tudo tremer, e aparecerem rachaduras, a construtora sai remendando e pintando tudo novamente depois! Aí é só rezar para o terremoto não ser muito grande e não derrubar o prédio!

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)