Tuning - Estatísticas de I/O

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

            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 Modelo 2 acertou. Subqueries que utilizam à cláusula EXISTS são finalizadas assim que a primeira ocorrência é 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 Modelo 2 é mais eficiente que o Modelo 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 boa 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 boa 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 modelo-2 é mais eficiente – façamos o teste:

            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.
        

            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 Modelo 1 foram executadas 10 leituras em cache na tabela x_order_details para processar a query. No Modelo 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 modelo 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!