Revista MSDN Magazine Edição 34 - Desvendando os segredos das Stored Procedures

Desvendando os segredos das Stored Procedures

Nilton Pinheiro

Quando você desenvolve uma aplicação que acessa e manipula dados em um servidor SQL Server, você deve utilizar a linguagem Transact-SQL para acessar e manipular esses dados. No entanto existem dois métodos que você pode utilizar para acessar e manipular dados no SQL Server: armazenar as instruções T-SQL na aplicação e deixar que a própria aplicação envie os comandos (conhecido como ad-hoc queries) ou armazenar as instruções T-SQL como Stored Procedures e fazer com que a aplicação, simplesmente executem as mesmas e processe os resultados.

Neste artigo falarei como as Stored Procedures são processadas, seus tipos e vantagens, como trabalhar com procedures aninhadas, bem como algumas boas práticas para sua utilização. 

Entendendo o processamento das procedures

Para aqueles que não conhecem Stored Procedures, posso dizer que elas são semelhantes a uma view. Um conjunto de instruções T-SQL que é nomeada e armazenada no banco de dados, fornecem acesso a dados e quando executadas retornam um conjunto de dados ou DataSet.

No entanto, as procedures podem ir muito além de um simples Select. Elas podem armazenar códigos complexos, possuir loops, variáveis, tratamentos condicionais, aceitar parâmetros de entrada e retornar Recordsets ou valores calculados, retornar valores calculados por outras procedures e muito mais. Com procedures você pode inclusive retornar erros ou mensagens customizadas para indicar o status de um processamento.

Quando criada, as procedures passam por um processo chamado Parse que faz uma análise sintática das instruções T-SQL que compõem a procedure. Depois o SQL Server armazena o nome em uma tabela de sistema chamada sysobjects e seu texto em uma outra tabela, também de sistema, chamada syscomments.

Quando a procedure é executada pela primeira vez ou é recompilada, ela passa por três estágios antes de ser efetivamente executada:

Resolução: o processador de consultas do SQL Server lê o texto na tabela syscomments e verifica se os objetos referenciados pela procedure realmente existem no banco de dados. Nesse estágio o SQL Server também executa algumas validações como, por exemplo, checar a compatibilidade do tipo de dados das colunas com possíveis variáveis criadas no texto da procedure.

Otimização: o otimizador de consultas do SQL Server analisa as instruções T-SQL da procedure e cria o seu plano de execução. O plano de execução descreve o caminho mais rápido para chegar até os dados e é criado baseando-se nas seguintes informações:

·        A quantidade de dados na tabela ou tabelas acessadas pela procedure;

·        A presença e o tipo de índice das tabelas e a distribuição dos dados nas colunas indexadas;

·        Os valores e operadores de comparação utilizados na cláusula WHERE;

·        A presença de joins e UNION, ORDER BY ou GROUP BY.

Compilação: após o estágio de resolução e otimização, o otimizador de consultas cria um plano de execução para a procedure e armazena esse plano em memória. Quando uma procedure é constantemente executada, o SQL Server reutiliza o plano de execução existente em memória, fazendo com que subseqüentes execuções sejam significativamente mais rápidas que o processo inicial. Esse plano de execução fica em memória até o SQL Server ser reiniciado ou precisar de memória para alocar outros objetos.

Embora o plano de execução fique em memória, algumas alterações no banco de dados ou ambiente de execução também podem torná-lo ineficiente ou inválido, fazendo com que precise ser novamente otimizado. Esse processo é chamado de recompilação e normalmente acontece quando:

·        Alterações são realizadas na estrutura de tabelas ou views referenciadas pela procedure;

·        Novas estatísticas de distribuição são geradas, seja automaticamente ou através da execução de comandos como UPDATE STATISTICS;

·        Um índice usado pelo plano de execução atual é excluído;

·        Um grande volume de dados é inserido ou excluído das tabelas referenciadas pela procedure;

·        O ambiente de execução não é o mesmo no que o plano foi compilado. O ambiente compreende servidor, banco de dados e configurações de conexão;

·        Objetos referenciados pela procedure requerem resolução de nomes. Isso normalmente ocorre quando objetos com mesmo nome possuem owners diferentes. Nessas situações o SQL Server deverá identificar qual tabela acessar a cada vez que a tabela for referenciada;

·        Usa-se a cláusula WITH RECOMPILE na criação da procedure;

·        Executa a procedure de sistema sp_recompile sobre as tabelas referenciadas pela procedure;

·        SQL Server é reiniciado;

·        Outros.

A Figura 1 apresenta o fluxo de criação e execução de uma Stored Procedure.

Figura 1. Fluxo de criação e execução de uma Stored Procedure

Vantagens da utilização de Stored Procedures ...