Otimizando aplicações com o uso de Stored Procedures
Introdução
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 Bancos de Dados relacionais (só tenho experiência com SGBDs relacionais, por isso não posso afirmar que outros tipos de BDs, como por exemplo, BDs orientados a objetos, também possam ter o mesmo benefício). 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.
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. 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 em um componente separado, que por sua vez, deve ser armazenado em um servidor de aplicação, e não no BD.
Já desenvolvi muitas 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. 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 SKY DRIVE do (ver painel direito da página principal do blog www.fabioprado.net), pasta Oracle -> Scripts, arquivo TesteTransacao.zip. Para efetuar a instalação e utilizá-la, siga os passos abaixo:
1- Mande um e-mail para fbifabio@gmail.com para solicitar a senha de descompactação. Com a senha em mãos, descompacte o arquivo TesteTransacao.zip;
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 do MEU SKY DRIVE 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 passos. Em transações maiores, o ganho de desempenho proporcionalmente também pode ser bem 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 meus treinamentos!
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 em stored procedures, sugiro a leitura da discussão Regras de Negócio em Stored Procedures ou Desenvolvimento em Camadas, no Linkedin, Grupo NET Framework – Brasil.
Referências