Blog – Fabiano Neves Amorim

O segundo problema foi mais interessante e vou explicar porque, normalmente focamos nossas forças em otimizar o SELECTs, mas neste caso o maior problema estava em um DELETE, operações de DELETE são muito pesadas para o banco de dados, mais ainda caso a tabela que esta sendo deletada tenha uma primary key que é referenciada em outras tabelas(foreign keys). Antes de efetuar o DELETE o SQL terá que consultar cada tabela referenciada pra verificar se o DELETE não está violando nenhuma constraint. No meu caso a tabela que estava sendo apagada tinha relacionamento com outras 15 tabelas, portanto antes de apagar os dados o SQL tinha que ler as outras 15 tabelas para verificar se o DELETE era valido. Para piorar, em 3 dessas tabelas referenciadas a coluna da Foreign Key não estava indexada conclusão, Clustered Scan. Para resolver o problema bastou criar os devidos índices nas colunas da Foreign Key e pronto.

Para baixar o arquivo com um exemplo clique no link abaixo,  

 http://cid-52eff7477e74caa6.skydrive.live.com/embedrowdetail.aspx/Publica/DELETEComForeignKeyssemIndice.sql

Esta semana tivemos um problema de performance em uma rotina que estava funcionando normalmente e começou a apresentar lentidão chegando a exibir uma mensagem de timeout para o usuário. Comecei a analisar as query da procedure e encontrei o 1º problema. Segue um exemplo:

USE TempDB

GO

CREATE TABLE TesteA(ID_TesteA Int IDENTITY(1,1) PRIMARY KEY,

                    Data      DateTime DEFAULT GETDATE())

CREATE TABLE TesteB(ID_TesteB   Int IDENTITY(1,1),

                    Nome        VarChar(200) DEFAULT NEWID(),

                    ID_TesteA   Int REFERENCES TesteA(ID_TesteA),

                    Primary Key(ID_TesteB, Nome))

GO

SET NOCOUNT ON

DECLARE @I INT

— Insere 5000 linhas nas tabelas

SET @I = 0

WHILE @I < 5000

BEGIN

  INSERT INTO TesteA DEFAULT VALUES

  INSERT INTO TesteB(ID_TesteA) VALUES(@@IDENTITY)

  SET @I = @I + 1;

END

GO

— Por causa de statisticas desatualizadas e o TOP + Order By DESC

— o SQL estava fazendo Scan na tabela TesteA

— No exemplo abaixo ele não irá fazer Scan, porem irá gerar um Sort

— por causa do TesteA.Data DESC. Como a tabela não está ordenada por Data e sim por

— ID_TesteA(Primary key e indice cluster) ele tem que ordenar a tabela por Data.

SELECT TOP 1 TesteA.Data

    FROM TesteB

   INNER JOIN TesteA

      ON TesteB.ID_TesteA = TesteA.ID_TesteA

   WHERE TesteB.ID_TesteB = 1

   ORDER BY TesteA.Data DESC

GO

— A solução encontrada foi alterar a consulta acima para esta abaixo

— A idéia é pegar o ultimo registro incluido na tabela, portanto simplismente

— troquei o TOP 1 + Desc por MAX. No exemplo abaixo ele geou um Stream Aggregate na coluna

— TesteA.Data ordenando o resultado por TesteA.ID que é o indice Cluster e já está ordedado

— fisicamente.

SELECT MAX(TesteA.Data)

    FROM TesteB

   INNER JOIN TesteA

      ON TesteB.ID_TesteA = TesteA.ID_TesteA

   WHERE TesteB.ID_TesteB = 1

/*

O Edvaldo que trabalha junto comigo fez uma observação importante:

Cuidado em utilizar funções de agregação (MAX, MIN, COUNT…) com EXISTS pois sempre vai existir valor.

Exemplo:

IF EXISTS (SELECT MAX(campo) FROM tabela WHERE condição que não encontrou nenhum registro)

BEGIN

    …

END

ELSE

BEGIN

  …

END

Sempre vai passar no IF e nunca no ELSE.

*/

Continua…