10 pontos que devem ser observados quanto a performance de uma consulta Parte 1
Performance de querys é sem dúvida uma das maiores causadoras de dor de cabeça em DBAs e afins(J), se vocês já leram algum post neste blog devem ter percebido que gosto muito deste assunto, trato diariamente com problemas deste tipo e tem alguns pontos que acho importantes de serem analisados quando falamos em análise de consultas, vou tentar explicar melhor abaixo.
Primeiro vou falar um pouco da empresa onde trabalho a, CNP-M, graças a Deus somos uma empresa certificada MPS.BR pois os processos que foram implantados nos ajudam a diminuir e MUITO possíveis problemas de performance que teríamos e que não deixamos chegar nos clientes pois param no processo de validação. Os pontos que vou mencionar abaixo servem como base para procurar possíveis problemas de performance, bom chega de conversa mole e vamos para a melhor parte(você já sabe, TSQL).
Todas as consultas abaixo foram executadas no banco AdventureWorks / SQL Server 2005.
1. Sempre verificar o plano de execução de cada select existente no código SQL, e analisar o uso ou não uso dos índices de cada tabela pertencente a query.
2. Verificar o uso de Functions. Caso exista alguma function envolvida no SQL analise bem a consulta e verifique se é possível alterar a consulta para fazer um join com a própria tabela ou então até mesmo tabelas temporárias, vamos ver alguns exemplos para ficar mais fácil de entender o que estou querendo dizer.
IF OBJECT_ID(‘VendaPorCliente’) IS NOT NULL DROP FUNCTION dbo.VendaPorCliente
GO CREATE FUNCTION dbo.VendaPorCliente(@CustomerID Int) RETURNS Decimal(18,2) AS BEGIN DECLARE @Total Decimal(18,2)
SELECT @Total = SUM(OrderQty * UnitPrice) FROM AdventureWorks.Sales.SalesOrderHeader a INNER JOIN AdventureWorks.Sales.SalesOrderDetail b ON a.SalesOrderID = b.SalesOrderID WHERE a.CustomerID = @CustomerID
RETURN @Total END GO /* Seleciona o total de venda por Customer Aqui temos um problema, pois para cada linha na tabela Customer o SQL Server irá executar a Function VendaPorCliente, ou seja se minha tabela Customer tiver 50000 linhas o SQL irá acessar as tabelas de header e Detail 50000 vezes. */ SELECT AccountNumber, dbo.VendaPorCliente(CustomerID) as Total FROM AdventureWorks.Sales.Customer
/* Para resolver o problema da consuta acima poderiamos fazer o seguinte Criar uma nova function do tipo "multi-statement table-valued" */
IF OBJECT_ID(‘VendaTotalClientes’) IS NOT NULL DROP FUNCTION dbo.VendaTotalClientes
GO CREATE FUNCTION dbo.VendaTotalClientes() RETURNS @tb_result TABLE ( CustomerID Int, Total Decimal(18,2), PRIMARY KEY(CustomerID) ) AS BEGIN INSERT INTO @tb_result SELECT a.CustomerID, SUM(OrderQty * UnitPrice) Total FROM AdventureWorks.Sales.SalesOrderHeader a INNER JOIN AdventureWorks.Sales.SalesOrderDetail b ON a.SalesOrderID = b.SalesOrderID GROUP BY a.CustomerID
RETURN END GO /* Seleciona o total de venda por Customer Desta vez ao invés de acessar a function para cada linha da tabela Customer o SQL Server irá ler os dados das tabelas Header e Detail apenas 1 vez pois a function irá retornar todos os dados em uma tabela. */ SELECT AccountNumber, b.Total FROM AdventureWorks.Sales.Customer a INNER JOIN dbo.VendaTotalClientes() b ON a.CustomerID = b.CustomerID GO /* Agora chegamos onde eu queria!, imagine que eu queria retornar o total de venda apenas do Customer ‘AW00000001’, eu iriá escrever o seguinte select. O que acontece abaixo é que o SQL irá primeiro retornar todos os dados da function, ou seja, todas as vendas por customer e depois aplicar o filtro de AccountNumber = ‘AW00000001’ */ SELECT AccountNumber, b.Total FROM AdventureWorks.Sales.Customer a INNER JOIN dbo.VendaTotalClientes() b ON a.CustomerID = b.CustomerID WHERE a.AccountNumber = ‘AW00000001’ GO /* O ideal neste caso seria usar: */ SELECT AccountNumber, dbo.VendaPorCliente(CustomerID) as Total FROM AdventureWorks.Sales.Customer WHERE AccountNumber = ‘AW00000001’ GO /* Ou então simplesmente não utilizar a function e fazer o select direto nas tabelas */ SELECT a.AccountNumber, SUM(OrderQty * UnitPrice) AS Total FROM AdventureWorks.Sales.Customer a INNER JOIN AdventureWorks.Sales.SalesOrderHeader b ON a.CustomerID = b.CustomerID INNER JOIN AdventureWorks.Sales.SalesOrderDetail c ON b.SalesOrderID = c.SalesOrderID WHERE a.AccountNumber = ‘AW00000001’ GROUP BY a.AccountNumber GO |
Obs.: Atenção, o comando SET STATISTICS IO ON não leva em consideração as leituras efetuadas nas suas functions, o que certas vezes acaba gerando uma má interpretação do comando, portanto fique ligado nisso.
Continua…
Muito bom o Post Parabéns!.
Vou para o próximo agora =D
Nice post.
Scalar and multiline UDFs are causing problems when dealing with large resultsets.. I would use inline UDF and cross/outer apply.