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
*/
*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!!
Felipe Antunes
email:fjantunes@gmail.comTwitter:@felipe_store
*/
