Oracle: otimizando a performance de aplicações com stored procedures
12 de janeiro de 2012 Fábio Prado Banco de Dados, Desenvolvimento de Software
No artigo de hoje vou escrever sobre um assunto que eu comento e demonstro em mais detalhes nos meus treinamentos de Tuning de SQL em Oracle 10G/11G, que é a performance das stored procedures (SPs) nas aplicações. O uso de stored procedures, em substituição ao uso de instruções SQL, pode otimizar drásticamente a performance de aplicações que acessam/atualizam bancos de dados (BDs).
No exemplo deste artigo as regras de negócio estão dentro da SPs. Muita gente é contra esta prática, pois aprendemos em arquitetura de sistemas que as regras de negócio devem ficar na aplicação e não no BD. Concordo em partes, pois em aplicações críticas, que exigem alta performance, o que é mais importante: uma aplicação filosóficamente melhor (com regras de negócio na aplicação) ou uma aplicação mais rápida?
Vou demonstrar a seguir, um exemplo de uma aplicação bem simples 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, 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), composto por 3 passos:
a) Verificar se a conta origem possui saldo para efetuar a transferência;
b) Retirar (sacar) valor da conta origem;
c) Depositar valor na conta destino;
A aplicação está disponível para download no MEU SKY DRIVE (ver painel direito da página principal do meu blog), pasta Oracle -> Scripts, arquivo TesteTransacao.zip. Para efetuar a instalação e utilizá-la, siga os passos abaixo:
1- Descompactar o arquivo TesteTransacao.zip;
2- Conectar-se no BD desejado e instalar 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- Configurar os valores (values) dos parâmetros (keys) do arquivo TesteTransacao.exe.config, conforme indicações abaixo:
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;
user_name = Nome do usuário que a aplicação utilizará para conectar-se no BD;
pwd_user = Senha do usuário que a aplicação utilizará para conectar-se no BD;
idContaOrigem = Número da conta origem;
idContaDestino = Número da conta destino;
vlInicialContaOrigem = Valor inicial da conta origem;
vlInicialContaDestino = Valor inicial da conta destino;
intTotalInteracoes = Valor indicando qtde. de operações de transferências que serão realizadas;
vlTransferencia = Valor da transferência.
Para efetuar os testes, basta clicar nos 2 botões existentes na tela principal da aplicação: Transferência Ad hoc e Transferência com SP. O botão Transferência Ad hoc submete instruções SQL para o BD, enquanto que, o botão Transferência com SP, executa uma stored procedure no BD para executar as mesmas instruções SQL.
TESTE 1: 1 transferência bancária
a) Tempo de Transferência do botão Transferência Ad hoc: 0,015s
Figura 2 – Teste de 1 transferência com SQL (ad hoc)
b) Tempo de Transferência do botão Transferência com SP: 0,00s
Figura 3 – Teste de 1 transferência com SP
TESTE 2: 1000 transferências bancárias
a) Tempo de Transferência do botão Transferência Ad hoc: 9,90s
Figura 4 – Teste de 1000 transferências com SQL (ad hoc)
b) Tempo de Transferência do botão Transferência com SP: 0,23s
Figura 5 – Teste de 1000 transferências com SP
No Teste 1, de acordo com o tempo de execução da transferência SQL e transferência com SP, pudemos verificar que a 2ª (com SP) foi mais rápida, mas o teste com 1 só transferência não foi não tão eficiente para demonstrar o ganho em usar SP (a execução foi tão rápida que não foi possível computar o tempo (0,00s) através da aplicação).
No Teste 2, realizando 1000 transações, conseguimos verificar que o ganho de performance utilizando SP foi brutal. Com SP, pudemos verificar que o tempo das 1000 transferências foi 43 vezes mais rápido que as 1000 transferências submetendo SQL para a aplicação.
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 explico em meus treinamentos!