Inicial > SQL Server - Performance > 10 pontos que devem ser observados quanto a performance de uma consulta Parte 1

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…

  1. 31 de janeiro de 2011 às 13:36

    Muito bom o Post Parabéns!.

    Vou para o próximo agora =D

  2. toper
    17 de março de 2011 às 10:39

    Nice post.
    Scalar and multiline UDFs are causing problems when dealing with large resultsets.. I would use inline UDF and cross/outer apply.

  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: