Vou compartilhar uma técnica para extrairmos máximo desempenho na elaboração das nossas procedures com parâmetros opcionais no Oracle e SQL Server.
Quantas vezes você elaborou uma procedure para retornar um cursor (recordset / resultset) que recebe parâmetros opcionais, ou seja apenas as condições na cláusula WHERE se alteram e o restante da query permanece intocado?
Penso que a solução mais adotada seja semelhante a esta abaixo, certo?
Nesta abordagem, o otimizador aplica o filtro de predicados desnecessariamente em qualquer combinação – Observe abaixo o plano de acesso de uma consulta sem filtros informados:
Há uma abordagem muito superior que permite que o código referencie qualquer combinação de variáveis informadas, mas de uma forma que o otimizador do banco de dados trabalhe para nós com máximo desempenho!
Vamos a prática:
Note que sempre fazemos o Bind das variáveis na consulta. Esta é a primeira parte do truque—Isto permite usar SQL dinâmico nativo (da maneira correta!), porque nós temos um conjunto estático de variáveis para realizar bind, em tempo de execução.
Supondo que eu dispare uma consulta sem filtros – Observe que o plano de acesso da consulta está sem filtro de predicados desnecessários:
Se eu informar o parâmetro p_hiredate – Observe que o otimizador avaliou apenas o filtro informado – novamente ignorando os filtros desnecessários:
O otimizador ignora os demais filtros de predicados quando identifica o “1=1 or …” – segunda parte do truque!
Para consultas em tabelas pouco densas, você não perceberá a diferença no custo e no tempo de resposta. Porém, na vida real é possível que você se depare com estruturas contendo milhares de registros e a necessidade de muitos filtros opcionais – E é ai que você verá a diferença!
getting the right answer: good!
getting the right answer right now: best!
Dica para construir um PL/SQL disciplinado: Largue o vício em querer resolver tudo no banco de dados – desconfie de procedures com mais de 50 linhas!
Compartilhe isso:
Twitter
Facebook
E-mail
LinkedIn
Mais
WhatsApp
Tumblr
Skype
Curtir isso:
Curtir Carregando...
#Oracle	#SQL server	#Tuning	
Workflow
Votorantim Energia investe em sistema de automação e aumenta assertividade no pagamento de notas	
Custom, SharePoint
Criando Web Services clássicos (.asmx) para SharePoint 2007/2010/2013	
Posts relacionados	
Outros
São as equipes que entregam projetos e não os gerentes!	
0 1306
Agilidade e Gestão, Outros
Como seria a Avaliação de Desempenho dos personagens de “Stranger Things”	
0 1879
Agilidade e Gestão, Outros
E se você fosse o gestor de Frank Underwood (House of Cards)?	
0 2116
Desenvolvimento WEB, DevOps, Outros
Continuos Delivery – AWS Code Pipeline + Jenkins	
0 2590
Outros
O que não te contaram sobre a carreira em TI	
0 3441
Outros
O que é DevOps? Descubra como é possível entregar versões de sistemas com agilidade	
0 2844
Deixe uma resposta	Cancelar resposta
Receba conteúdos exclusivos em primeira mão!
Δ
Sobre a Iteris
Nossos talentos são o maior ativo da Iteris. Trabalhamos diariamente para aprimorar os conhecimentos técnicos e gerenciais de nossos colaboradores, buscando a excelência em cada aspecto do serviço que prestamos.
Nosso corpo de consultores é formado por especialistas nas tecnologias que utilizamos. Cada equipe é liderada por gerentes de projetos especialmente treinados para garantir o sucesso dos projetos e das iniciativas de nossos clientes.