SlowMagic's questions

A consulta abaixo parece simples e direta, mas produz resultados inesperados.

CREATE TABLE #NUMBERS
(
    N BIGINT
);

INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;



WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
),
B AS
(
    SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

    UNION ALL

    SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A
)
SELECT   *
FROM     B
ORDER BY B.QUANTITY
;

Eu esperaria que ele executasse o CTE A uma vez e, em seguida, carregasse esses resultados para o CTE B para produzir resultados mais ou menos assim:

No entanto, produz resultados como este:

Isso significa que ele está voltando e executando CTE A cinco vezes, uma vez para cada menção de A em CTE B. Isso não é apenas indesejado e não intuitivo, mas também parece desnecessariamente ineficiente.

O que está acontecendo e como um gênio CTE o reescreveria para produzir os resultados desejados?

BTW, as páginas de documentação da Microsoft sobre CTEs contêm esta declaração enigmática que pode ou não estar relacionada:

Se mais de um CTE_query_definition for definido, as definições de consulta deverão ser unidas por um destes operadores de conjunto: UNION ALL, UNION, EXCEPT ou INTERSECT.

Por fim, reescrever a consulta para eliminar CTE B não ajudou:

WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
)
SELECT   *
FROM     (
          SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

          UNION ALL

          SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A

         ) AS B
ORDER BY B.QUANTITY
;

Acho que o conselho geral desta comunidade é evitar tabelas temporárias em favor de CTEs. No entanto, às vezes encontro situações em que as construções CTE são muito lentas, enquanto seus equivalentes de tabela temporária são muito rápidos.

Por exemplo, isso gira por horas e nunca parece produzir resultados. O plano de consulta está cheio de loops aninhados.

CREATE TABLE #TRIANGLES
(
    NODE_A VARCHAR(22),
    NODE_B VARCHAR(22),
    NODE_C VARCHAR(22)
)
;


INSERT INTO #TRIANGLES VALUES
/*  150,000 ROWS  */
;


CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES (NODE_C);



WITH
TRIANGLES_FILTERED AS
(
    -- **** FILTERING OF THE TRIANGLE TABLE OCCURS IN A CTE ****
    SELECT   *
    FROM     #TRIANGLES AS T
    WHERE    LEN(T.NODE_A) = 2  AND
             LEN(T.NODE_B) = 2  AND
             LEN(T.NODE_C) = 2
),
CONNECTABLE_NODES AS
(
    SELECT   DISTINCT T1.NODE_C AS [NODE]
    FROM     TRIANGLES_FILTERED AS T1

             INNER JOIN
             TRIANGLES_FILTERED AS T2
             ON T1.NODE_B = T2.NODE_A  AND
                T1.NODE_C = T2.NODE_B

             INNER JOIN
             TRIANGLES_FILTERED AS T3
             ON T2.NODE_B = T3.NODE_A  AND
                T2.NODE_C = T3.NODE_B

    WHERE    T1.NODE_A <> T2.NODE_C  AND
             T1.NODE_A <> T3.NODE_C  AND
             T2.NODE_A <> T3.NODE_C
)
SELECT   *
FROM     #TRIANGLES AS T1
WHERE    T1.NODE_A IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_B IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_C IN (SELECT * FROM CONNECTABLE_NODES)
;

Plano de consulta: https://www.brentozar.com/pastetheplan/?id=rk_5TaiiP

Considerando que, o plano de consulta para isso usa correspondências de hash e é executado em um flash:

CREATE TABLE #TRIANGLES
(
    NODE_A VARCHAR(22),
    NODE_B VARCHAR(22),
    NODE_C VARCHAR(22)
)
;


INSERT INTO #TRIANGLES VALUES
/*  150,000 ROWS  */
;


CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES (NODE_C);



-- **** FILTERING OF THE TRIANGLE TABLE SAVED INTO A TEMP TABLE ****
SELECT   *
INTO     #TRIANGLES_FILTERED
FROM     #TRIANGLES AS T
WHERE    LEN(T.NODE_A) = 2  AND
         LEN(T.NODE_B) = 2  AND
         LEN(T.NODE_C) = 2
;    

CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES_FILTERED (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES_FILTERED (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES_FILTERED (NODE_C);



WITH
CONNECTABLE_NODES AS
(
    SELECT   DISTINCT T1.NODE_C AS [NODE]
    FROM     #TRIANGLES_FILTERED AS T1

             INNER JOIN
             #TRIANGLES_FILTERED AS T2
             ON T1.NODE_B = T2.NODE_A  AND
                T1.NODE_C = T2.NODE_B

             INNER JOIN
             #TRIANGLES_FILTERED AS T3
             ON T2.NODE_B = T3.NODE_A  AND
                T2.NODE_C = T3.NODE_B

    WHERE    T1.NODE_A <> T2.NODE_C  AND
             T1.NODE_A <> T3.NODE_C  AND
             T2.NODE_A <> T3.NODE_C
)
SELECT   *
FROM     #TRIANGLES AS T1
WHERE    T1.NODE_A IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_B IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_C IN (SELECT * FROM CONNECTABLE_NODES)
;

Plano de consulta: https://www.brentozar.com/pastetheplan/?id=B1cZC6isD

Como eu reescreveria o primeiro para ser tão rápido quanto o segundo?

BTW, se você está se perguntando sobre o que é toda a geometria/topologia, eu precisava saber como todos os triângulos se conectam na criação deste quebra-cabeça:
https://puzzling.stackexchange.com/questions/105275/dragon -feitiço de invocação