Consultas SQL de Longa Duração: uma exploração de exemplo

Aqui na InterSystems, frequentemente lidamos com conjuntos massivos de dados estruturados. Não é incomum ver clientes com tabelas contendo mais de 100 campos e mais de 1 bilhão de linhas, cada tabela totalizando centenas de GB de dados. Agora imagine fazer o join de duas ou três dessas tabelas, com um esquema que não foi otimizado para esse caso de uso específico. Só por diversão, vamos supor que você tenha 10 anos de dados de EMR de 20 hospitais diferentes em todo o seu estado, e você foi encarregado de encontrar….

todo clínico dentro da sua rede
   que administrou um medicamento específico
      entre os anos de 2017–2019
         para pacientes que residem fora do estado
            e possuem uma das seguintes condições [diabetes, hipertensão, asma]
               onde o custo foi coberto pelo Medicaid

Já vi nossa tecnologia lidar com esse tipo de caso sem problemas, mas a consulta ainda pode levar algum tempo para ser executada. Será que pode ser mais rápida? Deixe-me guiá-lo por uma investigação de exemplo.

///////////////////////////////////////////////////////////////////////////////////////////////

Necessidade: 
Encontrar todos os pacientes que tiveram um atendimento ambulatorial em uma unidade localizada em um desses condados nos anos de 2022 ou 2023.
A Query:
SELECT DISTINCT enc.Patient->PatientNumber 
FROM EMR.Encounter as enc
INNER JOIN State_Facility.Address as fa on enc.Facility = fa.FacilityCode
INNER JOIN State_Geography.Cities as city ON city.Zip = fa.ZipCode
WHERE enc.EncounterTime BETWEEN '2022-01-01' AND '2023-12-31'
AND enc.EncounterType IN ('OP','Outpatient','O')
AND city.County IN ('Los Angeles County', 'Orange County', 'Riverside County', 'San Bernardino County', 'Ventura County')
Performance:
A consulta estava levando mais de 24 horas para ser concluída

ETAPAS DA INVESTIGAÇÃO:

1)  Revise as tabelas que você está consultando. Quais relacionamentos ou chaves estrangeiras existem entre elas? Quais índices já existem? Sua consulta SQL está fazendo bom uso dos que já existem? Os índices possuem Status = Selectable?

Verificamos cada campo que fazia parte de um WHERE, AND ou INNER JOIN. A maioria deles possuía índices, incluindo alguns índices bitmap. [NOTA: Mais à direita na página da captura de tela, a coluna Status mostra que EncounterTypeIndex é selecionável]

2) Revise o Plano de Consulta. Ele faz sentido? Ele utiliza os índices e relacionamentos que você esperava? Caso contrário, ele parece ser mais ou menos eficiente?

            Sim, o Plano de Consulta mostrou uso efetivo dos índices em EncounterType e StartTime. [NOTA: Esta captura de tela é de uma versão simplificada da consulta que não considera o código postal da unidade de atendimento]

3) Certifique-se de que as estatísticas das tabelas estejam atualizadas executando o Tune Tables

4) Verifique se o plano de consulta real em tempo de execução corresponde ao que foi mostrado. O Plano de Consulta "Show Plan" não utiliza a otimização Runtime Plan Choice (RTPC) quando gera um plano de consulta, mas a RTPC é utilizada quando a consulta é realmente executada. É por isso que o plano de consulta do Show Plan e o Plano de Consulta em tempo de execução podem ser diferentes. O algoritmo RTPC geralmente encontra uma escolha ótima, mas às vezes pode fazer uma escolha ruim. Se verificarmos que o algoritmo RTPC está fazendo a escolha errada, é possível suprimir a RTPC em tempo de execução usando a %NORUNTIME keyword.

        Uma vez que a consulta estava em execução, verificamos a página de processos e encontramos o processo que estava executando a consulta. Encontramos a consulta em cache que ele estava executando (a Rotina). Acessamos essa consulta em cache e analisamos seu Plano de Consulta. Descobrimos que ela estava usando um plano de consulta muito diferente do que tínhamos visto antes, e que parecia muito menos eficiente.

Recomendações:

Recomendamos que o cliente tomasse as seguintes ações:

1. Usar a %NORUNTIME keyword ao executar a consulta, forçando o uso do Plano de Consulta mais eficiente
2. Criar um novo índice bitmap chamado EncounterDate baseado no campo EncounterTime. Índices baseados em data podem ser mais rápidos do que índices baseados em DateTime, e índices bitmap geralmente são significativamente mais rápidos do que índices normais

Depois que implementaram essas duas recomendações, a consulta passou a ser concluída em cerca de 6 horas, uma melhoria de 75%.

LEITURA ADICIONAL:

Confira a excelente coleção de recursos de @Ben Spead, que inclui links para documentação online, cursos de aprendizado online da InterSystems, apresentações em slides e artigos da Comunidade de Desenvolvedores.
https://community.intersystems.com/post/sql-performance-resources