Método para encontrar o registro principal com condições WHERE em tabelas filhas

Configurar

Recentemente trabalhei em uma otimização para uma Consulta SQL que retornou o Produto de Prescrição usado mais recentemente por um Paciente. A consulta original tratou isso por meio de 3 aninhados CTEe teve um desempenho muito ruim após a migração para um novo servidor. Em vez de tentar fazer a versão original funcionar com ajustes de servidor ou alterações de índice, modificamos a consulta usando ROW_NUMBER()partições. Isso levou o tempo de execução da consulta de cerca de 10 minutos para cerca de 6 segundos para aproximadamente 215.000 linhas na saída final.

Pergunta

Eu estou olhando para ver se há uma maneira melhor do que como eu o reescrevi, já que na minha mente isso quase parece um hack. Eu só quero ver se isso só está funcionando bem por causa do tamanho do nosso conjunto de dados ou se isso realmente é uma boa solução. Eu diria que preferimos usar algum tipo de solução que utiliza alguns JOINs diferentes que encontram esse tipo de informação em vez de usar ROW_NUMBER()e um arquivo CTE.

detalhes adicionais

Por causa desta pergunta, vou deixar de fora alguns detalhes em nosso banco de dados que são menos do que desejáveis ​​(chaves primárias compostas, usando VarChar(1)em vez de um BIT, etc...) para que a raiz da pergunta possa ser melhor abordada.

Temos 4 tabelas no escopo:

• Patient- vamos apenas retornar a Coluna de Identidade desta tabela
• OrderHeader- contém informações sobre o pacote, como o endereço para o qual está indo. E é o registro pai para OrderDetail. Esta tabela está vinculada por Patientmeio de uma PatientIDcoluna.
• OrderDetail- cria uma relação entre OrderHeadere Productpara indicar quais Produtos foram na caixa real quando foi enviado
• Product- mantém a lista de produtos

Estamos precisando de uma consulta que retorne para cada paciente, o OrderDetailregistro superior, para o OrderHeaderregistro superior, onde o Productregistro vinculado ao OrderDetailregistro Product.IsRx= 1.

Infelizmente, não podemos simplesmente obter o MAXvalor de OrderDetail.IDwhere Product.IsRx= 1, pois isso OrderDetail.IDpode não pertencer ao MAXvalor de OrderHeader.ID. Também não podemos confiar no MAXvalor para OrderHeader.ID, pois não é garantido que tenha um registro, OrderDetailnem nenhum registro lá é garantido que tenha a Product.IsRx= 1

Resolvi esse problema com a consulta abaixo:

WITH CTE
(
    PatientID,
    OrderHeaderID,
    OrderDetaillID,
    ProductNDCCode,
    ProductNDCDescription,
    RowNumber
)
AS
(
    SELECT P.ID AS 'PatientID',
    H.ID AS 'OrderHeaderID',
    D.ID AS 'OrderDetaillID',
    P.NDCCode AS 'ProductNDCCode',
    P.NDCDescription AS 'ProductNDCDescription',
    ROW_NUMBER() 
    OVER
    (
    PARTITION BY P.ID
    ORDER BY 
        P.ID ASC, --This part in the ORDER BY may not be needed, I apologize if it is unnecessary
        SH.OrderHdrID DESC, 
        SD.OrderDtlID DESC
    ) AS 'RowNumber'
    FROM Patient P
        INNER JOIN OrderHeader H
            ON P.ID = H.PatientID
        INNER JOIN OrderDetail D
            ON H.ID = D.OrderHeaderID
        INNER JOIN Product PR
            ON PR.ID = D.ProductID
    WHERE PR.IsRx = 1
)
SELECT PatientID,
OrderHeaderID,
OrderDetaillID,
ProductNDCCode,
ProductNDCDescription
FROM CTE
WHERE RowNumber = 1

Uma orientação ou experiência que eu poderia obter seria muito útil.