T-SQL Tips - TOP Não é Tunning

/*

      Caros Senhores, Senhoras e Senhoritas DBAs!

      Vamos falar hoje de uma situação bem comum.

      Voce está lá tranquilão analisando o SQL Profiler quando assustadoramente vem aquela query do demônio

      "SELECT TOP 1 * FROM QualquerTabelaGigante ORDER BY QualquerCampo"     com milhares de reads e o login name do desenvolvedor vizinho.

      Você respira fundo e tranquilamente se vira para ele e diz:

            - "Amigão, essa query é pesada hein? Ta precisando mesmo?"

      e ele responde todo tranquilo:

            - "Na verdade só estou checando os dados, mas relaxa que eu coloquei um TOP 1."

      Ser DBA é Alegria de viver!

      Inspirado nessa situação resolvi escrever esse post para demonstrar ao amigo desenvolvedor que: Queridão, TOP NÃO É TUNNING!!

*/   

-- Vamos começar populando uma tabelinha com 100.000 registros

-- (e umas  446 pages)

CREATE TABLE TabelaExemplo (

       CampoID int  identity(1,1) CONSTRAINT PK_TabelaExemplo PRIMARY KEY,

       CampoTexto varchar(100),

       CampoData datetime

       )

SET IDENTITY_INSERT TabelaExemplo ON

INSERT INTO TabelaExemplo (CampoID,CampoTexto,CampoData )

SELECT

       ROW_NUMBER() OVER(ORDER BY Pop1.Valor ) CampoID,
       CAST(ROW_NUMBER() OVER(ORDER BY Pop1.Valor ) as varchar(6)) + '_Texto' CampoTexto,
       DATEADD (HOUR,-1 * CAST(ROW_NUMBER() OVER(ORDER BY Pop1.Valor ) as varchar(6)),GETDATE()) CampoData
FROM
       (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) Pop1 (Valor)
CROSS JOIN   (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) Pop2 (Valor)
CROSS JOIN   (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) Pop3 (Valor)
CROSS JOIN   (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) Pop4 (Valor)
CROSS JOIN   (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) Pop5 (Valor)

SET IDENTITY_INSERT TabelaExemplo OFF

-- Ligamos as estatisticas de IO para termos o retorno de paginas lidas

SET STATISTICS IO ON

-- Vamos começar com a consulta simples. Um TOP 1 limpo na tabela exemplo

SELECT TOP 1 * FROM TabelaExemplo

(1 row(s) affected)

Table 'TabelaExemplo'. Scan count 1, logical reads 2

/*

Faz parecer que o desenvolvedor tinha razão, afinal ele leu apenas duas pages de maneira rápida e econômica.

      Mas ai está a armadilha. Vamos colocar um Where para o campo texto da ultima linha da tabela.

*/

SELECT  TOP 1 * FROM TabelaExemplo

WHERE CampoTexto = '100000_Texto'

(1 row(s) affected)

Table 'TabelaExemplo'. Scan count 1, logical reads 447

/*

      Basicamente, o SQL varreu toda a tabela até encontrar um registro que satisfizesse a consulta. No primeiro caso ele encontrou logo na primeira linha (leu a Page do Indice e leu a leaf-page).

      Já no segundo ele só foi encontrar a página no ultimo registro conforme podemos ver no Execution Plan

*/

 

/*
      Ou seja, basta um WHERE para quebrarmos o falso tunning e termos uma consulta pesada novamente.

      Um simples indice no CampoTexto reduz bastante a nossa leitura.

*/

CREATE NONCLUSTERED INDEX IX_Texto ON TabelaExemplo (CampoTexto)

SELECT TOP 1 * FROM TabelaExemplo WHERE CampoTexto = '100000_Texto'

Table 'TabelaExemplo'. Scan count 1, logical reads 5

/*

      Como o Indice já está ordenado ele pode com pouca leituras te devolver um único registro dentro da condição e ir buscar no clustered index o restante da informação.

      Mais um exemplo agora com o campo data

*/

-- Na query abaixo, a condição WHERE filtra as 24 primeiras linhas da tabela* e colocamos um top 24, ou seja, igual a quantidade filtrada

*ajuste a data para a data que você está lendo o post

SELECT  TOP 24 * FROM TabelaExemplo

WHERE CampoData  BETWEEN  '2012-01-04' AND '2012-01-05'

(24 row(s) affected)
Table 'TabelaExemplo'. Scan count 1, logical reads 7

/*

Novamente o resultado é satisfatório, pois ele encontrou as 24 linhas e parou a varredura.

      Mas basta mudarmos o top d 24 para 25 que a coisa já não é mais a mesma.
*/

SELECT  TOP 25 * FROM TabelaExemplo

WHERE CampoData  BETWEEN  '2012-01-04' AND '2012-01-05'

(24 row(s) affected)

Table 'TabelaExemplo'. Scan count 1, logical reads 447

/*

      A tabela foi completamente lida porque o SQL não podia garantir que não existia a 25ª linha.

      Com ORDER BY a coisa também muda de figura. Vamos voltar o TOP 24 e ordenar para o campo Data

*/

SELECT  TOP 24 * FROM TabelaExemplo

WHERE CampoData  BETWEEN  '2012-01-04' AND '2012-01-05'

ORDER BY CampoData

(24 row(s) affected)

Table 'TabelaExemplo'. Scan count 1, logical reads 447

  

/*

      Boom! Mais uma vez, leitura completa da tabela pois não existe como o SQL determinar que as 24 primeiras linhas são suficientes para o retorno correto.

      Tudo isso da pra resolver com indice, mas indice é Tunning e conforme queriamos demonstrar Queridão, TOP NÃO É TUNNING!!

Obrigado e Abraços!!

Felipe Antunes

email:fjantunes@gmail.com
Twitter:@felipe_store
 

*/