Dia desses, eu estava ajudando a equipe de desenvolvimento da empresa a melhorar o código de um procedimento armazenado e encontrei uma situação que me fez parar para pensar.

Os desenvolvedores estão cientes da necessidade de usar índices nas suas principais consultas, mas a boa intenção de seguir uma diretriz nem sempre leva aos resultados desejados. No caso do procedimento em questão, encontrei uma tabela temporária com um índice clusterizado.

Para quem não está familiarizado, índices clusterizados obrigam que as páginas de dados da tabela sejam organizadas de tal maneira que respeite a sequência de valores dos campos que fazem parte do índice. Eles são muito úteis nas consultas de dados, mas causam um impacto significativo na inserção de dados, exatamente porque o SGBD precisa identificar em qual das páginas de dados cada novo registro será escrito.

E não adianta tentar bancar o esperto e criar o índice clusterizado depois que a tabela já estiver populada. Isso talvez seja ainda pior em termos de performance, porque as páginas de dados já existentes terão que ser reorganizadas durante a construção do índice.

De todo modo, o caso que eu analisei envolvia um índice clusterizado em uma temporária. No meu entendimento, o custo de criação de um índice desse tipo é alto demais para uma tabela temporária em qualquer circunstância.

Uma tabela temporária grava os dados no TEMPDB, assim como os índices que ela contiver. Cada vez que o procedimento armazenado for executado, mais log e consequentemente mais espaço em disco vai ser usado pela base TEMPDB.

Por essas e outras, eu considero que mesmo índices não-clusterizados precisam ser muito bem estudados antes de serem implementados em tabelas temporárias. Em primeiro lugar, deve-se avaliar qual benefício é oferecido por eles. Índices ajudam muito nas pesquisas em tabelas com grande número de registros quando os campos indexados fazem parte das cláusulas WHERE ou GROUP BY das declarações SQL. Se a tabela temporária for “pequena” (alguns milhares de registros) e/ou se não há muitas consultas que usarão o índice, então é melhor nem criá-lo.

Na dúvida, a melhor coisa a se fazer é rodar um script que crie a tabela temporária e execute as consultas desejadas e então verificar o plano de execução das consultas para se certificar de que os índices são usados de fato. Se os índices não aparecem no plano de execução das consultas, então eles podem ser descartados.

Mas se se concluir que os índices são úteis, é hora de fazer outro tipo de pergunta.

Existem casos em que precisamos criar índices em tabelas temporárias grandes, com centenas de milhares ou até milhões de registros. Caso não haja alterações de dados dentro das tabelas temporárias (INSERT, DELETE, UPDATE), pode ser vantajoso criar uma visão indexada no seu banco de dados e eliminar o código de criação de tabelas temporárias nos seus procedimentos armazenados.

Pense como isso iria funcionar num ambiente compartilhado, por exemplo: todos os acessos ao procedimento armazenado vão consultar a mesma visão indexada em vez de criar uma tabela temporária e mais um índice para cada uma das sessões que são abertas.

Exemplo: 100 acessos simultâneos ao procedimento vão criar 100 tabelas temporárias e 100 índices, se essa for a abordagem escolhida. Caso se use uma visão indexada, todas as 100 sessões vão acessar uma única visão que já está indexada pelo SGBD. Claro que eventualmente pode haver problemas de concorrência,  mas vantagens são óbvias.

Portanto, tome cuidado com suas tabelas temporárias. É preciso avaliar bem o  que vai se fazer para conseguir um bom resultado.