Tuning - Estatísticas de I/O

Responda rápido: qual dos dois modelos de select abaixo é mais eficiente ?

Modelo - 1

select o.orderid from x_orders o
where o.orderid=11078
and OrderId in ( select orderid
from x_order_details d
where d.orderid = o.orderid
)
----------------------------------------------------------------
orderid
-----------
11078

(1 row(s) affected)

Modelo - 2

select o.orderid from x_orders o
where o.orderid=11078
and exists ( select orderid
from x_order_details d
where d.orderid = o.orderid
)
----------------------------------------------------------------
orderid
-----------
11078

(1 row(s) affected)

Quem optou pelo modêlo-2 acertou. Subqueries que utilizam a cláusula EXISTS são finalizadas assim que a primeira ocorrencia é encontrada. Nesse exemplo existem 20.000 linhas em x_order_details para orderid=11078. A subquery que utiliza a cláusula IN força um processo de scan nas 20.000, menos eficiente

Agora como poderíamos provar que o modêlo-2 é mais eficiente que o modêlo-1 ? Existem diversas maneiras:

1. Cronometre o tempo de execução das duas queries; aquela que executar num menor tempo é a mais eficiente. Essa efetivamente NÃO é uma bôa prática, principalmente porque irá depender da atividade do servidor no momento em que as queries são executadas; depende também do volume de páginas que já estão presentes no cache do servidor - queries complexas e que movimentam muitas linhas podem ser executadas rapidamente se os dados estiverem no cache do banco.

2. Faça uma análise no plano de execução das duas queries, e opte pelo mais “enxuto”. Certamente a análise do plano de execução é uma bôa pedida, mas apresenta um grau de complexidade maior. Na próxima matéria comentarei esse item.

3. Ligue as estatísticas de I/O nas duas queries e execute novamente. Opte pelo select que movimentar um número menor de páginas. Esse procedimento é simples e traz a informação necessária para provar que o modêlo-2 é mais eficiente – façamos o teste:

Modelo-1

set Statistics IO ON
select o.orderid from x_orders o
where o.orderid=11078
and OrderId in ( select orderid
from x_order_details d
where d.orderid = o.orderid
)
----------------------------------------------------------------
orderid
-----------
11078

(1 row(s) affected)
Table 'x_order_details'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'x_orders'. Scan count 1, logical reads 2,
physical reads 0, read-ahead reads 0.

Modelo-2

set Statistics IO ON
select o.orderid from x_orders o
where o.orderid=11078
and exists ( select orderid
from x_order_details d
where d.orderid = o.orderid
)
----------------------------------------------------------------
orderid
-----------
11078

(1 row(s) affected)
Table 'x_order_details'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'x_orders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Concluimos que o modelo 2 é mais eficiente porque requer um número menor de leituras em x_order_details. Aqui vale aquela regra de “quanto menor, melhor”.

Set Statistics IO ON ativa a produção das estatísticas; a partir desse momento todo select / update/ delete irá produzir linhas adicionais detalhando o número de páginas movimentadas por cada tabela. As informações fornecidas por statistics io são:

Logical Reads : informa o número de páginas lidas em memória. No modêlo-1 foram executadas 10 leituras em cache na tabela x_order_details para processar a query. No modêlo-2, foram executadas somente 2 leituras.

Physical Reads: número de páginas lidas em disco. Se as páginas requeridas por um comando não estão em memória, devem ser lidas do disco para a memória. Quando você executa um comando pela primeira vez, podem ocorrer leituras físicas. Se você executar o mesmo comando repetidas vezes, irá notar que leituras físicas são “convertidas” em leituras lógicas. As leituras físicas “desaparecem”, permanecendo somente as leituras lógicas.

Read Ahead Reads: páginas lidas por antecipação. O SQL Server 2000 lê páginas adicionais para efeito de otimização, mantendo-as em cache para agilizar sua utilização por outras queries.

Scan Count: número de vezes que a tabela foi acionada. Dependendo da maneira como escrevemos a query, o mesmo pelo modêlo de join utilizado, uma mesma tabela pode ser acessada repetidas vezes – exemplo: uma subquery na linha do select exige um acesso para cada linha lida na tabela principal, portanto o scan count das tabelas presentes na subquery será igual ao número de linhas retornadas pelo select.

Conclusão

Estatística de I/O é um ótimo indicador de performance e ajuda muito quando estamos em dúvida sobre como escrever uma query. Se você ainda não utiliza, não perca tempo – suas queries agradecem.

Por hoje é só.
Até a próxima !

Paulo Ribeiro

Paulo Ribeiro (psribeiro@hotmail.com) é Microsoft MCDBA e membro da equipe editorial da SQL Magazine. Atua como DBA sênior em SQL Server na Livraria e Papelaria Saraiva S/A.