Maneira mais eficiente de unir duas tabelas com valor MAX

Estou otimizando uma consulta, onde tenho que juntar duas tabelas, digamos Producte TransactionHistoryuma tabela, e retornar várias colunas de ambas as tabelas para a última TransactionDateda TransactionHistorytabela para cada Produto na Producttabela.

TransactionHistorytable tem cerca de 13 milhões de linhas e a Producttabela tem quase 2 milhões de linhas.

Parece muito fácil, não é? mas para diferentes cenários, a consulta diferente tem um desempenho diferente.

Não estou usando meus nomes de tabela reais aqui, para mostrar a sintaxe de consulta diferente que tentei, estou usando as tabelas BigProduct e BigSalesHistory de Adam Machanic.

A consulta foi originalmente escrita como ....

SELECT p.ProductID
     , p.Name
     , h.Quantity
     , h.TransactionDate
FROM [dbo].[bigProduct] p
INNER JOIN [dbo].[bigTransactionHistory] h ON p.ProductID = h.ProductID
WHERE h.TransactionDate = ( SELECT MAX(TransactionDate)
                            FROM [dbo].[bigTransactionHistory]
                            WHERE ProductID = h.ProductID )
AND p.ProductID > 1317
AND p.ProductID < 1416
GO

O que resultou em cerca de 30 contagens de varredura e quase 300.000 leituras lógicas.

Eu sendo um grande fã de funções de janelas, escrevi as seguintes consultas usando ROW_NUMBER(), ambas saíram pela culatra:

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
INNER JOIN ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID 
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
            ) c
            ON p.ProductID = c.ProductID AND rn = 1
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

E

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID 
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = ProductID
            ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
  AND rn = 1
GO

A contagem de varreduras foi para 100s e as leituras lógicas saltaram para 700.000s.

Ficando bastante desapontado com o desempenho da função de janela, reescrevi a consulta usandoCROSS APPLY

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT TOP 1  h.ProductID
                          , h.Quantity
                          , h.TransactionDate
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = h.ProductID
              ORDER BY h.TransactionDate 
             ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

Agora, essa consulta parece ter o melhor desempenho de todas as mencionadas acima, mas assim que o intervalo da consulta externa para ProductIDaumenta, essa consulta dá terrivelmente errado e começa a ter um desempenho pior do que os dois acima.

Além disso, embora tenha reduzido as leituras lógicas para 27.000, mas o tempo de CPU e o tempo decorrido aumentaram 8 vezes.

Existe uma maneira mais eficiente de escrever uma consulta como essa, tendo em mente que o intervalo de consulta externo para ProductID varia muito?.

Qualquer sugestão ou indicações na direção certa são muito apreciadas. Obrigada.