Cinco maneiras de melhorar o desempenho do SQL
Este artigo foi extraído do artigo do MSDN "Cinco maneiras de melhorar o desempenho do SQL" e propõe como melhorar a eficiência operacional de aplicativos baseados em SQL Server. É altamente recomendado. Para alguns sistemas aplicativos com alto tráfego, como melhorar e aprimorar as instruções SQL é muito importante e um bom ponto de avanço.
*O artigo inclui principalmente o seguinte conteúdo (se você estiver interessado, visite diretamente o seguinte URL para ler o documento completo em chinês e inglês):
1. Retorne IDENTITY de INSERT
SELECT @@IDENTITY
2, visualização incorporada e tabela temporária
Tabelas temporárias – Tabelas temporárias em tempdb podem fazer com que as consultas executem operações pesadas de E/S e acessos ao disco, e tabelas temporárias podem consumir muitos recursos.
Visualizações Inline - Use visualizações inline em vez de tabelas temporárias. Uma visualização embutida é simplesmente uma consulta que pode ser associada à cláusula FROM. Se você só precisa juntar dados a outras consultas, você pode tentar usar visualizações embutidas para economizar recursos.
3. Evite LEFT JOIN e NULL
LEFT JOINs consomem muitos recursos porque contêm dados que correspondem a dados NULL (inexistentes). Em alguns casos isto é inevitável, mas o custo pode ser muito elevado. LEFT JOIN consome mais recursos que INNER JOIN, então se você puder reescrever a consulta para que ela não use nenhum LEFT JOIN, você receberá uma recompensa muito boa.
Uma técnica para acelerar consultas que usam LEFT JOIN envolve criar um tipo de dados TABLE, inserir todas as linhas da primeira tabela (a tabela à esquerda de LEFT JOIN) e, em seguida, atualizar o tipo de dados TABLE com os valores de a segunda mesa. Esta técnica é um processo de duas etapas, mas pode economizar muito tempo em comparação com um LEFT JOIN padrão. Uma boa regra é tentar uma variedade de técnicas diferentes e registrar o tempo necessário para cada uma até obter a consulta com melhor desempenho para seu aplicativo.
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))
4, uso flexível do produto cartesiano
Entrarei em detalhes sobre essa técnica e defenderei o uso de produtos cartesianos em alguns casos. Por alguma razão, os produtos cartesianos (CROSS JOIN) recebem muitas críticas e os desenvolvedores são frequentemente alertados para não usá-los. Em muitos casos, consomem demasiados recursos para serem utilizados de forma eficiente. Mas, como qualquer ferramenta SQL, elas podem ser valiosas se usadas corretamente.
Vale a pena seguir um dos códigos de exemplo:
-- O produto cartesiano retorna todos os clientes em todos os meses. O produto cartesiano basicamente multiplica a primeira tabela pela segunda tabela, produzindo um conjunto de linhas que contém o número de linhas da primeira tabela multiplicado pelo número de linhas da segunda tabela. Portanto, o produto cartesiano retorna 12 (todos os meses) * 81 (todos os clientes) = 972 linhas para a tabela @tblFinal. As etapas finais são atualizar a tabela @tblFinal com os totais de vendas mensais de cada cliente para esse período e selecionar o conjunto final de linhas.
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))
DECLARE @tblCustomers TABLE (CustomerID CHAR(10),
NomedaEmpresa VARCHAR(50),
NomeDoContato VARCHAR(50))
DECLARE @tblFinal TABLE (sMonth VARCHAR(7),
ID do cliente CHAR(10),
NomedaEmpresa VARCHAR(50),
NomeDoContato VARCHAR(50),
mVendasMONEY)
DECLARE @dtStartDate DATETIME,
@dtEndDate DATETIME,
@dtDate DATETIME,
@i INTEGER
SET @dtEndDate = '5/5/1997'
SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS
VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + ' 23:59:59' AS DATETIME))
SET @dtDataInício = DATEADD(MM, -1 * 12, @dtDataEnd)
-- Coloque todos os meses na primeira tabela
DEFINIR @i = 0
ENQUANTO (@i <12)
COMEÇAR
SET @dtData = DATEADD(mm, -1 * @i, @dtEndDate)
INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR(4)) + '-' +
CASO
QUANDO MÊS(@dtData) <10
THEN '0' + CAST(MONTH(@dtDate) AS VARCHAR(2))
ELSE CAST(MONTH(@dtDate) AS VARCHAR(2))
TERMINA EM sMês
DEFINIR @i = @i + 1
FIM
– Coloque todos os clientes que tiveram vendas durante esse período na tabela “y”
INSERIR EM @tblCustomers
SELECIONE DISTINTO
c.IDDoCliente,
c.NomedaEmpresa,
c.NomeContato
DE Clientes c
INNER JOIN Pedidos o ON c.CustomerID = o.CustomerID
ONDE o.OrderDate ENTRE @dtStartDate E @dtEndDate
INSERT INTO @tblFinal
SELECIONE m.sMês,
c.IDDoCliente,
c.NomedaEmpresa,
c.NomeContato,
0
DE @tblMonths m CROSS JOIN @tblCustomers c
UPDATE @tblFinal SET
mVendas = meusdados.mVendas
FROM @tblFinal f INNER JOIN
(
SELECIONE c.CustomerID,
CAST(ANO(o.OrderDate) AS VARCHAR(4)) + '-' +
CASO QUANDO MÊS(o.OrderDate) < 10
THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))
TERMINA COMO mês,
SOMA(od.Quantidade * od.PreçoUnit) AS mVendas
DE Clientes c
INNER JOIN Pedidos o ON c.CustomerID = o.CustomerID
INNER JOIN [Detalhes do pedido] od ON o.OrderID = od.OrderID
ONDE o.OrderDate ENTRE @dtStartDate E @dtEndDate
Agrupar por
c.IDDoCliente,
CAST(ANO(o.OrderDate) AS VARCHAR(4)) + '-' +
CASO QUANDO MÊS(o.OrderDate) < 10
THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))
FIM
) meus dados em f.CustomerID = meus dados.CustomerID AND f.sMonth =
meusdados.sMês
SELECIONE f.sMês,
f.IDDoCliente,
f.NomedaEmpresa,
f.NomeContato,
f.mVendas
DE @tblFinal f
ENCOMENDAR POR
f.NomedaEmpresa,
f.sMês
5. Pegue as peças que faltam e complete as que faltam.
Aqui estão algumas outras técnicas comuns que podem ajudar a melhorar a eficiência das consultas SQL. Suponha que você queira agrupar todos os vendedores por região e subtotalizar suas vendas, mas deseja apenas esses vendedores marcados como ativos no banco de dados. Você pode agrupar os vendedores por região e eliminar aqueles que não estão ativos usando a cláusula HAVING, ou pode fazer isso na cláusula WHERE. Fazer isso na cláusula WHERE reduz o número de linhas que precisam ser agrupadas, portanto é mais eficiente do que fazer isso na cláusula HAVING. A filtragem com base nas condições de linha na cláusula HAVING força a consulta a agrupar dados que seriam removidos na cláusula WHERE.
Outra dica de eficiência é usar a palavra-chave DISTINCT para encontrar relatórios separados para linhas de dados em vez de usar a cláusula GROUP BY. Nesse caso, SQL usando a palavra-chave DISTINCT é mais eficiente. Use GROUP BY somente quando precisar calcular funções agregadas (SUM, COUNT, MAX, etc.). Além disso, se sua consulta sempre retornar uma linha exclusiva, não use a palavra-chave DISTINCT. Nesse caso, a palavra-chave DISTINCT apenas adiciona sobrecarga ao sistema.
－－－－－－－－－－－－－－－－－－－
URL chinês:
http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
URL em inglês:
http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/