Supercharge Your SQL Queries for Production Databases (Português)

Artem Yevtushenko | Builder Education |7 min read |April 24, 2019

SQL é uma das mais poderosas ferramentas de tratamento de dados existentes. Em SQL Superstar, damos-lhe conselhos accionáveis para o ajudar a tirar o máximo partido desta linguagem versátil e criar consultas bonitas e eficazes.

Se estiver a funcionar sem um armazém de dados ou base de dados analítica separada para elaboração de relatórios, a base de dados de produção ao vivo é provavelmente a sua única fonte para os dados mais recentes e actualizados. Ao consultar uma base de dados de produção, a optimização é fundamental. Uma consulta ineficiente irá drenar os recursos da base de dados de produção, e causar um desempenho lento ou perda de serviço para outros utilizadores, se a consulta contiver erros. É vital optimizar as suas consultas para um impacto mínimo no desempenho da base de dados.

Definir primeiro os requisitos de negócio

Cobrimos as melhores práticas para definir requisitos de negócio para BI noutros locais. Certifique-se definitivamente de que está a aplicar essas práticas ao optimizar as consultas SQL, incluindo:

• Identificar as partes interessadas relevantes. Assegure-se de que todas as partes necessárias estão na discussão para desenvolver a sua consulta. Ao consultar bases de dados de produção, certifique-se de que a equipa DBA está incluída.
• Concentre-se nos resultados do negócio. Dê à consulta um objectivo definido e único. Tributar a base de dados de produção para relatórios exploratórios ou duplicados é um risco desnecessário.
• Enquadre a discussão para optimizar os requisitos. Definir a função e o âmbito do relatório, identificando o seu público-alvo. Isto centrará a consulta nas tabelas com o nível de detalhe correcto.
• Fazer grandes perguntas. Seguir os 5 W’s: Quem? Quem? Onde? Quando? Porquê?
• Escreve requisitos muito específicos e confirma-os com as partes interessadas. O desempenho da base de dados de produção é demasiado crítico para ter requisitos pouco claros ou ambíguos. Certifique-se de que os requisitos são tão específicos quanto possível e confirme os requisitos com todos os interessados antes de executar a consulta.

camposSELECT em vez de utilizar SELECT *

Ao executar consultas exploratórias, muitos programadores SQL utilizam SELECT * (lido como “select all”) como abreviatura para consultar todos os dados disponíveis de uma tabela. No entanto, se uma tabela tiver muitos campos e muitas linhas, esta taxa os recursos da base de dados consultando muitos dados desnecessários.

Usar a declaração SELECT apontará a base de dados para consultar apenas os dados de que necessita para satisfazer os requisitos do negócio. Aqui está um exemplo onde os requisitos da empresa solicitam endereços de correio para clientes.

Ineficiente:

SELECT *
FROM Customers

Esta consulta pode incluir outros dados também armazenados na tabela de clientes, tais como números de telefone, datas de actividade, e notas de vendas e serviço ao cliente.

Eficiente:

SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers

Esta consulta é muito mais limpa e apenas puxa a informação necessária para endereços postais.

Para manter um índice de todas as tabelas e nomes de campos, executar uma consulta a partir de uma tabela de sistema como INFORMATION_SCHEMA ou ALL_TAB_COLUMNS (para MS SQL Server, leia isto).

P>Comece hoje com SQL:

Evite SELECT DISTINCT

SELECT DISTINCT é uma forma útil de remover duplicados de uma consulta. SELECT DISTINCT funciona agrupando todos os campos da consulta para criar resultados distintos. Contudo, para atingir este objectivo, é necessária uma grande quantidade de poder de processamento. Além disso, os dados podem ser agrupados ao ponto de serem imprecisos. Para evitar usar SELECT DISTINCT, seleccionar mais campos para criar resultados únicos.

Ineficiente e impreciso:

SELECT DISTINCT FirstName, LastName, State
FROM Customers

Esta consulta não conta para múltiplas pessoas no mesmo estado tendo o mesmo nome e apelido. Nomes populares tais como David Smith ou Diane Johnson serão agrupados, causando um número impreciso de registos. Em bases de dados maiores, um grande número de David Smiths e Diane Johnson fará com que esta consulta corra lentamente.

Eficiente e precisa:

SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers

Ao adicionar mais campos, os registos não duplicados foram devolvidos sem utilizar SELECT DISTINCT. A base de dados não tem de agrupar quaisquer campos, e o número de registos é exacto.

Ver Sisense em acção:

Explorar Painel de Controlo

Criar inscrições com INNER JOIN (não WHERE)

alguns programadores SQL preferem fazer inscrições com cláusulas WHERE, tais como as seguintes:

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID

Este tipo de join cria um Join Cartesiano, também chamado Produto Cartesiano ou CROSS JOIN.

Num Join cartesiano, todas as combinações possíveis das variáveis são criadas. Neste exemplo, se tivéssemos 1.000 clientes com 1.000 vendas totais, a consulta iria primeiro gerar 1.000.000 de resultados, depois filtrar para os 1.000 registos onde o CustomerID é correctamente unido. Esta é uma utilização ineficiente dos recursos da base de dados, uma vez que a base de dados tem feito 100x mais trabalho do que o necessário. As uniões cartesianas são especialmente problemáticas em bases de dados de grande escala, porque uma união cartesiana de duas grandes tabelas poderia criar milhares de milhões ou triliões de resultados.

Para evitar a criação de uma Junção Cartesiana, usar INNER JOIN em vez disso:

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID

A base de dados só geraria os 1.000 registos desejados onde o CustomerID é igual.

alguns sistemas de SGBD são capazes de reconhecer ONDE se junta e executá-los automaticamente como INNER JOINs em vez disso. Nesses sistemas de SGBD, não haverá diferença no desempenho entre um INNER JOIN e um INNER JOIN. No entanto, o INNER JOIN é reconhecido por todos os sistemas de SGBD. O seu DBA irá aconselhá-lo sobre qual é o melhor no seu ambiente.

Utilizar WHERE em vez de HAVING para definir filtros

O objectivo de uma consulta eficiente é extrair apenas os registos necessários da base de dados. Pela Ordem de Operações SQL, as instruções HAVING são calculadas após as instruções WHERE. Se a intenção é filtrar uma consulta baseada em condições, uma declaração WHERE é mais eficiente.

Por exemplo, vamos assumir que foram feitas 200 vendas no ano 2016, e queremos consultar o número de vendas por cliente em 2016.

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.CustomerID, Customers.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#

Esta consulta retiraria 1.000 registos de vendas da tabela de Vendas, depois filtraria para os 200 registos gerados no ano 2016, e finalmente contaria os registos no conjunto de dados.

Em comparação, as cláusulas WHERE limitam o número de registos puxados:

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name

Esta consulta puxaria os 200 registos do ano 2016, e depois contaria os registos no conjunto de dados. O primeiro passo da cláusula HAVING foi completamente eliminado.

HAVING só deve ser utilizado quando se filtrar num campo agregado. Na consulta acima, poderíamos filtrar adicionalmente para clientes com mais de 5 vendas usando uma declaração HAVING.

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
HAVING Count(Sales.SalesID) > 5

>>Free Starter Kit: Comece hoje com o nosso kit básico SQL gratuito

Utilizar wildcards no fim de uma frase apenas

Ao pesquisar dados em texto simples, tais como cidades ou nomes, os wildcards criam a pesquisa mais ampla possível. Contudo, a pesquisa mais ampla é também a pesquisa mais ineficiente.

Quando se utiliza um wildcard principal, especialmente em combinação com um wildcard final, a base de dados é encarregada de pesquisar todos os registos para uma correspondência em qualquer lugar dentro do campo seleccionado.

Considerar esta pesquisa para puxar cidades começando por ‘Char’:

SELECT City FROM Customers
WHERE City LIKE ‘%Char%’

Esta pesquisa puxará os resultados esperados de Charleston, Charlotte, e Charlton. No entanto, também irá puxar resultados inesperados, tais como Cape Charles, Crab Orchard, e Richardson.

Uma consulta mais eficiente seria:

SELECT City FROM Customers
WHERE City LIKE ‘Char%’

Esta consulta irá puxar apenas os resultados esperados de Charleston, Charlotte, e Charlton.

Utilizar LIMIT para amostrar os resultados da consulta

Antes de executar uma consulta pela primeira vez, assegure-se de que os resultados serão desejáveis e significativos, utilizando uma declaração LIMIT. (Em alguns sistemas de SGBD, a palavra TOP é usada alternadamente com LIMIT.) A declaração LIMIT retorna apenas o número de registos especificado. A utilização de uma declaração LIMIT impede a tributação da base de dados de produção com uma grande consulta, apenas para descobrir que a consulta necessita de edição ou refinamento.

Na consulta de vendas de 2016 a partir de cima, examinaremos um limite de 10 registos:

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
LIMIT 10

Podemos ver pela amostra se temos ou não um conjunto de dados utilizáveis.

Executar a sua consulta durante as horas de menos movimento

Para minimizar o impacto das suas consultas analíticas na base de dados de produção, fale com um DBA sobre o agendamento da consulta a executar em horas de menos movimento. A consulta deve ser executada quando os utilizadores concorrentes estiverem no seu número mais baixo, que é tipicamente a meio da noite (3 – 5 da manhã).

Quanto mais dos seguintes critérios tiver a sua consulta, maior a probabilidade de um candidato concorrer à noite:

• Selecting from large tables (>1.000,000 registos)
• Juntas cartesianas ou CROSS JOINs

li>Declarações de aberturali>Declarações DISTINTASSELECTSubconsultas aninhadasli>Pesquisas de cartão de visita em texto longo ou memorando fields

• Multiple schema queries

Query confidently

Com estas dicas em mente (mais algumas outras dicas e truques SQL no seu bolso) deverá ser capaz de construir eficientemente, belas consultas que funcionarão sem problemas e devolverão os conhecimentos que a sua equipa necessita para a mudança do jogo.

br>Get get the free SQL starter kit here: