Otimização de consulta SQL em IRIS

Olá a todos os estimados membros da comunidade de desenvolvedores InterSystems em espanhol!

Embora eu consulte frequentemente a comunidade de desenvolvedores e ocasionalmente tenha deixado alguma pergunta aqui, este será o meu primeiro artigo, e que melhor ocasião para fazê-lo do que participando no 3º concurso de artigos técnicos.

O tópico que abordarei neste artigo são as diferentes ferramentas que o InterSystems IRIS nos fornece para otimizar tanto os bancos de dados quanto as consultas que executamos, bem como os novos tipos de armazenamento e dados.

Otimizando nossas consultas SQL

Para este artigo, configurei um pequeno exemplo em uma instância InterSystems IRIS Community, importando um arquivo CSV com 5 milhões de registros de vendas hipotéticas feitas ao longo dos anos em diferentes cidades.

A estrutura da tabela será muito simples, mas suficiente para os testes que precisamos. Por favor, desculpem os nomes, mas vocês sabem que nada é mais permanente do que uma solução temporária.

Sales.Record

Sales.Record

Concurso.Localidad

Tentaremos extrair a média histórica de vendas para uma cidade como Valladolid em um determinado ano:

Vejamos quanto tempo nossa consulta leva para retornar a média mensal de vendas:

O resultado foi de pouco mais de 8 segundos para retornar o valor médio das vendas mensais. Como você pode ver, temos uma opção que nos permite ver o plano de execução da consulta ( Show plan ). Vamos ver passo a passo o que nossa consulta fará:

Vejamos o módulo H, que contém algumas informações bastante interessantes:

• Divide master map Concurso.Localidad(L).IDKEY into subranges of IDs.

• Call module A in parallel on each subrange, piping results into temp-file D.
 

Essas duas linhas nos dizem que primeiro dividiremos a tabela Concurso.Localidad por intervalos de ID e chamaremos o módulo A em paralelo para cada intervalo obtido. Este processamento paralelo, implementado por padrão no IRIS, acelerará nossa consulta sem a necessidade de especificá-lo.

Como você pode ver, nossa consulta é bastante custosa. Por que isso acontece? Como você pode observar, nossa consulta está percorrendo toda a tabela Concurso.Ventas com suas 5 milhões de entradas e, para cada linha, está aplicando a condição de que a cidade seja "Valladolid" e o ano seja um valor específico. Como você pode imaginar, esta não é a abordagem mais eficiente. Como poderíamos melhorar o desempenho?

Adicionando índices

Como você deve saber, indexar uma coluna nos permitirá melhorar o desempenho de consultas que contenham uma condição sobre essa coluna, então tentaremos incluir um índice na nossa coluna Anyo na nossa tabela Sales.Record:

Criamos nosso índice como um BITMAP porque ele conterá um conjunto bastante pequeno de valores diferentes e melhorará o desempenho de nossas consultas em relação a um índice normal (você pode ler a documentação associada aqui ).

Este índice não mudará o plano de execução, mas acelerará a consulta. Vamos ver o resultado:

Como podemos ver, nosso desempenho melhorou. Vamos dar uma olhada no plano de consulta com o índice incluído:

Como vocês podem ver, no módulo B não lemos mais toda a tabela Concurso.Ventas com seus 5 milhões de registros, mas sim, usando o índice, extraímos primeiro todos os valores com o Ano 1996, excluindo assim os que não coincidem da leitura.

Uma boa melhoria, não é? Mas não vamos parar por aqui, podemos melhorar ainda mais com o...

Otimizador de consultas

O InterSystems IRIS nos fornece por padrão um otimizador de consultas que utilizará certas ferramentas para definir o plano da consulta; não é o mesmo realizar uma consulta diretamente na tabela com 5 milhões de registros de vendas que fazê-la primeiro na tabela de localidades e depois na tabela de vendas excluindo os registros que não pertencem à localidade solicitada.

A maneira mais simples de otimizar tabelas é executando um simples comando TUNE TABLE na tabela que você deseja otimizar. Este comando configurará os seguintes parâmetros na definição da classe da sua tabela:

• ExtentSize : Este parâmetro conterá o número de linhas na tabela. Nós mesmos podemos defini-lo colocando qualquer valor que quisermos, desde que mantenha uma proporção com os valores reais comparados a outras tabelas.
• Selectivity : a porcentagem que cada valor diferente na coluna representa em relação ao total, ou seja, se tivermos apenas 2 tipos de valores, sua Seletividade será de 50%, se tivermos 64 como no caso das localidades será de 1,56% se não tivermos valores repetidos.
• BlockCount : com o valor estimado de blocos de mapa que serão usados para cada mapa SQL baseado no ExtentSize.

Vamos dar uma olhada na seção Storage da classe Contest.Sales antes de aplicar o tuning à tabela:

Storage Default
{
<Data name="VentasDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>SumaVentasMensuales</Value>
</Value>
<Value name="3">
<Value>%Source</Value>
</Value>
<Value name="4">
<Value>Mes</Value>
</Value>
<Value name="5">
<Value>Año</Value>
</Value>
<Value name="6">
<Value>CodigoCiudad</Value>
</Value>
</Data>
<DataLocation>^Concurso.VentasD</DataLocation>
<DefaultData>VentasDefaultData</DefaultData>
<ExtentSize>2000000</ExtentSize>
<IdLocation>^Concurso.VentasD</IdLocation>
<IndexLocation>^Concurso.VentasI</IndexLocation>
<SQLMap name="AñoIdx">
<BlockCount>-10634</BlockCount>
</SQLMap>
<StreamLocation>^Concurso.VentasS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

Como vocês podem ver, possui uma série de valores padrão que não ajudarão a melhorar nossas consultas. Vamos executar o comando TUNE TABLE para ambas as tabelas e ver quais consequências isso tem tanto na nossa classe quanto na nossa consulta.

Vamos dar uma olhada na nossa seção Storage:

Storage Default
{
...
<ExtentSize>6468826</ExtentSize>
<Property name="Año">
<AverageFieldSize>4</AverageFieldSize>
<Histogram>...</Histogram>
<Selectivity>2.9412%</Selectivity>
</Property>
<Property name="CodigoCiudad">
<AverageFieldSize>6.7</AverageFieldSize>
<Histogram>...</Histogram>
<Selectivity>3.3333%</Selectivity>
</Property>
<Property name="Mes">
<AverageFieldSize>3</AverageFieldSize>
<Histogram>...</Histogram>
<Selectivity>8.3333%</Selectivity>
</Property>
<Property name="SumaVentasMensuales">
<AverageFieldSize>5</AverageFieldSize>
<Histogram>...</Histogram>
<OutlierSelectivity>.000393:794574</OutlierSelectivity>
<Selectivity>0.0002%</Selectivity>
</Property>
<SQLMap name="$Ventas">
<BlockCount>-48</BlockCount>
</SQLMap>
...
}

Agora que tanto Concurso.Localidad quanto Concurso.Ventas estão configuradas corretamente, vejamos seu impacto no plano de execução da consulta: 

A melhoria é de 31% em comparação com a consulta anterior, e seu custo é menos da metade da consulta original. Vejamos quanto tempo nossa consulta leva agora:

Isso mesmo, como você pode ver, passamos de 8 segundos inicialmente para 4,6 incluindo os índices apropriados para nossas tabelas, e para pouco menos de 1 segundo aplicando TUNE TABLES.

Você pode encontrar mais otimizações possíveis para suas consultas em esta página , como %FirstTable, que forçará o otimizador a ler a tabela selecionada primeiro, o que pode ser útil para limitar leituras em tabelas grandes filtradas por valores da selecionada.

Armazenamento Colunar

O InterSystems IRIS não apenas nos fornece as ferramentas acima para obter o máximo de nossas consultas, mas também nos dá acesso à funcionalidade de armazenamento colunar destinada a casos como o exemplo usado para este artigo: grandes volumes de dados que excedem um milhão de registros sobre os quais queremos realizar operações de agregação, como SUM ou AVG.

Este armazenamento colunar pode ser definido no nível da tabela ou no nível da coluna, conforme for mais conveniente. Para entender melhor o que é o armazenamento colunar, usarei uma imagem da documentação da ISC.

Seguindo a explicação, você verá como, para o nosso exemplo, pode ser interessante definir a coluna de vendas mensais como um tipo de armazenamento colunar. Para ver as diferenças, recriei as tabelas do zero sem índices e sem executar o TUNE TABLE.

A única mudança necessária em Concurso.Ventas para que nossa coluna SumaVentasMensuales utilize armazenamento colunar será definir a seguinte propriedade:

Property SumaVentasMensuales As %Integer(STORAGEDEFAULT = "columnar");

Vamos agora verificar o plano de consulta.

O custo da nossa consulta com armazenamento colunar é 65.487.882, enquanto com o armazenamento baseado em linhas era 100.529.682 — uma melhoria substancial simplesmente mudando o tipo de armazenamento. Vamos ver quanto tempo a consulta leva para ser executada:

Pouco mais de 1 segundo em comparação com os mais de 8 segundos que levava com o armazenamento vetorial.

Considerações finais e despedida

Assim concluímos nosso artigo sobre otimização de consultas SQL com o InterSystems IRIS, que espero que tenha sido útil para você.