Início / dba / Perguntas / 241038
Accepted
Kirk Saunders
Asked: 2019-06-21 06:54:26 +0800 CST

Lógica de decisão aninhada reutilizada - CTE versus código de cópia

  • 772

Pergunta

Eu tenho uma consulta que gera um column, criado por meio de uma série de CASEinstruções. Esse mesmo columné usado como parte de uma CASElógica para um 2º columnna mesma SELECTinstrução.

Se eu construísse um CTEcom a lógica interna aplicada para poder referenciar a lógica interna quando tiver que usá-la como uma decisão mais tarde. Qual é a sobrecarga geral adicional?

Pelo que entendi, não há nenhuma sobrecarga real adicionada. (Algumas pesquisas e um caso de teste simples que usei estão abaixo). Existem artigos que falam sobre isso ou casos em que não é o caso?

Pesquisa e Caso de Teste Simples

Encontrei alguns artigos que não indicam essa pergunta específica, mas me enviam na direção de que não há sobrecarga operacional adicionada.

Eu escrevi uma pequena consulta em um de nossos bancos de dados existentes para testar essa teoria. Os resultados e os tempos de execução foram os mesmos, assim como The statisticse o Query Execution Plan.

Code, Execution Plane Statisticspara não CTE-versão:

SELECT PC.CompanyID,
PC.ClientID,
PC.ProgramID,
PC.PatientID,
PC.CaseID,
CASE
    WHEN PFH.FulFilHdrCreateDateTime IS NULL
        THEN PC.CaseCreateDateTime
    ELSE
        PFH.FulFilHdrCreateDateTime
    END AS [ImportantDate],
DATEDIFF(Day, CASE WHEN PFH.FulFilHdrCreateDateTime IS NULL THEN PC.CaseCreateDateTime ELSE PFH.FulFilHdrCreateDateTime END, GETDATE())
FROM PATIENTCASES PC
    LEFT OUTER JOIN PATFULFILLMENTHEADER PFH
        ON PFH.CompanyID = PC.CompanyID
        AND PFH.ClientID = PC.ClientID
        AND PFH.ProgramID = PC.ProgramID
        AND PFH.PatientID = PC.PatientID
        AND PFH.CaseID = PC.CaseID
        AND PFH.FulFilHdrID = (SELECT TOP(1) temp.FulFilHdrID
                                FROM PATFULFILLMENTHEADER temp
                                WHERE temp.CompanyID = PC.CompanyID
                                AND temp.ClientID = PC.ClientID
                                AND temp.ProgramID = PC.ProgramID
                                AND temp.PatientID = PC.PatientID
                                AND temp.CaseID = PC.CaseID
                                ORDER BY temp.FulFilHdrID
                            )
WHERE PC.CompanyID = 'RxCRoads'

Plano de Execução Não-CTE

+---------------------------------------------------------+----------+
| Query Profile Statistics                                |          |
|   Number of INSERT, DELETE and UPDATE statements        | 0        |
|   Rows affected by INSERT, DELETE, or UPDATE statements | 0        |
|   Number of SELECT statements                           | 2        |
|   Rows returned by SELECT statements                    | 2880384  |
|   Number of transactions                                | 0        |
| Network Statistics                                      |          |
|   Number of server roundtrips                           | 3        |
|   TDS packets sent from client                          | 3        |
|   TDS packets received from server                      | 38523    |
|   Bytes sent from client                                | 2128     |
|   Bytes received from server                            | 157781300|
| Time Statistics                                         |          |
|   Client processing time                                | 10158    |
|   Total execution time                                  | 10158    |
|   Wait time on server replies                           | 0        |
+---------------------------------------------------------+----------+

Code, Execution Plane Statisticspara a versão CTE:

WITH CTE (CompanyID, ClientID, ProgramID, PatientID, CaseID, ImportantDate)
AS
(
    SELECT PC.CompanyID,
    PC.ClientID,
    PC.ProgramID,
    PC.PatientID,
    PC.CaseID,
    CASE
        WHEN PFH.FulFilHdrCreateDateTime IS NULL
            THEN PC.CaseCreateDateTime
        ELSE
            PFH.FulFilHdrCreateDateTime
        END AS [ImportantDate]
    FROM PATIENTCASES PC
        LEFT OUTER JOIN PATFULFILLMENTHEADER PFH
            ON PFH.CompanyID = PC.CompanyID
            AND PFH.ClientID = PC.ClientID
            AND PFH.ProgramID = PC.ProgramID
            AND PFH.PatientID = PC.PatientID
            AND PFH.CaseID = PC.CaseID
            AND PFH.FulFilHdrID = (SELECT TOP(1) temp.FulFilHdrID
                                    FROM PATFULFILLMENTHEADER temp
                                    WHERE temp.CompanyID = PC.CompanyID
                                    AND temp.ClientID = PC.ClientID
                                    AND temp.ProgramID = PC.ProgramID
                                    AND temp.PatientID = PC.PatientID
                                    AND temp.CaseID = PC.CaseID
                                    ORDER BY temp.FulFilHdrID
                                )
)
SELECT CompanyID, 
ClientID, 
ProgramID, 
PatientID, 
CaseID, 
ImportantDate,
DATEDIFF(Day, [ImportantDate], GETDATE())
FROM CTE
WHERE CompanyID = 'RxCRoads'

Plano de Execução CTE

+---------------------------------------------------------+-----------+
| Query Profile Statistics                                |           |
|   Number of INSERT, DELETE and UPDATE statements        |         0 |
|   Rows affected by INSERT, DELETE, or UPDATE statements |         0 |
|   Number of SELECT statements                           |         2 |
|   Rows returned by SELECT statements                    |   2880383 |
|   Number of transactions                                |         0 |
| Network Statistics                                      |           |
|   Number of server roundtrips                           |         3 |
|   TDS packets sent from client                          |         3 |
|   TDS packets received from server                      |     38523 |
|   Bytes sent from client                                |      2348 |
|   Bytes received from server                            | 157781800 |
| Time Statistics                                         |           |
|   Client processing time                                |      9985 |
|   Total execution time                                  |      9985 |
|   Wait time on server replies                           |         0 |
+---------------------------------------------------------+-----------+
sql-server performance

1 respostas

  1. Best Answer

    Parece que você fez uma análise sólida aqui, então talvez eu não consiga adicionar muito.

    Uma coisa que muitas vezes me vem à mente quando as pessoas começam a usar CTEs é este post de Erik Darling:

    CTEs, visualizações em linha e o que eles fazem

    Para resumir, os CTEs são uma ótima base a partir da qual você pode referenciar e filtrar itens na lista de seleção que você não conseguiria (pense em funções de janela), mas toda vez que você referenciar um CTE, eles são executados. Quanto menos vezes você tiver que atingir um conjunto de base maior, e quanto menos leituras você fizer, melhor. Se você estiver fazendo referência a CTEs mais de uma ou duas vezes, considere uma tabela temporária ou persistente, com os índices adequados.

    Portanto, no seu caso específico, como você não está ingressando no CTE ou correlacionando-o com outros conjuntos de dados, é improvável que o CTE cause problemas.

    Esteja ciente de que se alguém aparecer e começar a mexer com a consulta, juntando o CTE a outras tabelas, ou de volta a si mesmo, ou adicionando outro nível de "aninhamento" de CTE (para triturar mais números para a seleção final) - então você começam a entrar em território de "sobrecarga operacional".

    • 5

relate perguntas