Dicas T-SQL – CROSS APPLY, TOP 1 e ORDER BY

Vamos apresentar hoje uma dica de escrita de código para resolver um problema bem comum no dia a dia de quem escreve consultas em T-SQL.

Imagine que você tenha uma tabela com cadastro de produtos e outra tabela com os valores históricos daquele produto. Esse tipo de modelagem nos leva muitas vezes à necessidade de consultar o último valor de cada produto. Vamos apresentar aqui uma sugestão de abordagem para resolução desse tipo de situação combinando o uso de CROSS APPLY, TOP 1 e ORDER BY.

Em nosso exemplo, iremos usar as tabelas [Sales].[SalesPerson] e [Sales].[SalesPersonQuotaHistory] da base AdventureWorks2017. A consulta abaixo lista os registros de SalesPerson associados à SalesPersonQuotaHistory:

SELECT P.BusinessEntityID, Q.QuotaDate, Q.SalesQuota
FROM [Sales].[SalesPerson] P
JOIN [Sales].[SalesPersonQuotaHistory] Q ON P.BusinessEntityID = Q.BusinessEntityID;

Uma forma de escrita bastante comum para trazermos a última ocorrência seria a seguinte:

SELECT P.BusinessEntityID, Q.QuotaDate, Q.SalesQuota
FROM [Sales].[SalesPerson] P
JOIN [Sales].[SalesPersonQuotaHistory] Q ON P.BusinessEntityID = Q.BusinessEntityID 
                                            AND Q.QuotaDate = (SELECT MAX(QuotaDate) FROM [Sales].[SalesPersonQuotaHistory]
                                                                WHERE Q.BusinessEntityID = BusinessEntityID);

Agora vamos reescrever a consulta combinando os elementos propostos:

SELECT P.BusinessEntityID, Q.QuotaDate, Q.SalesQuota
FROM [Sales].[SalesPerson] P
CROSS APPLY ( SELECT TOP 1 QuotaDate, SalesQuota FROM [Sales].[SalesPersonQuotaHistory] 
              WHERE P.BusinessEntityID = BusinessEntityID
              ORDER BY QuotaDate DESC ) Q;

O CROSS APPLY faz com que possamos filtrar a subconsulta de acordo com o Id da tabela da esquerda; O ORDER BY define nossa ordenação como descendente, de forma com que os registros mais atuais sejam trazidos primeiro; e o TOP 1 faz com que seja trazido apenas um único registro, levando em consideração a ordenação definida com ORDER BY.

Se compararmos os planos de execução, notaremos que a abordagem com CROSS APPLY conseguirá usar o índice presente em [SalesPersonQuotaHistory] com SEEK, enquanto que a abordagem tradicional realiza um SCAN:

Apesar do comparativo dos planos de execução mostrar uma pequena diferença entre as duas formas de escrita, na prática conseguimos bons resultados com a abordagem sugerida em diversas situações.

Esta abordagem é bastante flexível, pois além de trazer a última ocorrência podemos também trazer valores tomando como base uma data, conforme exemplo abaixo:

SELECT P.BusinessEntityID, Q.QuotaDate, Q.SalesQuota
FROM [Sales].[SalesPerson] P
CROSS APPLY ( SELECT TOP 1 QuotaDate, SalesQuota FROM [Sales].[SalesPersonQuotaHistory] 
              WHERE P.BusinessEntityID = BusinessEntityID
              AND QuotaDate < '2014-01-01'
              ORDER BY QuotaDate DESC ) Q;

Dúvida sobre como utilizar essa abordagem? Deixe um comentário.

Deixe um comentário Cancelar resposta