SlowMagic's questions

下面的查询看起来简单明了，但它会产生意想不到的结果。

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
;

我希望它执行一次 CTE A，然后将这些结果带入 CTE B 以产生如下所示的结果：

但是，它会产生如下结果：

这意味着它将返回并执行 CTE A 五次，每次在 CTE B 中提及 A 一次。这不仅是不需要的和不直观的，而且看起来效率也不必要地低。

发生了什么，CTE 天才将如何重写它以产生预期的结果？

顺便说一句，关于 CTE 的 Microsoft 文档页面包含这个可能相关也可能不相关的神秘声明：

如果定义了多个 CTE_query_definition，则查询定义必须由以下集合运算符之一连接：UNION ALL、UNION、EXCEPT 或 INTERSECT。

最后，重写查询以消除 CTE B 并没有帮助：

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
;

我认为这个社区的一般建议是避免使用临时表来支持 CTE。但是，我有时会遇到 CTE 构造非常慢，而它们的临时表等价物非常快的情况。

例如，这旋转了几个小时，似乎永远不会产生结果。查询计划充满了嵌套循环。

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)
;

查询计划： https ://www.brentozar.com/pastetheplan/?id=rk_5TaiiP

鉴于此的查询计划使用哈希匹配，并且它在瞬间运行：

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)
;

查询计划： https ://www.brentozar.com/pastetheplan/?id=B1cZC6isD

我将如何将第一个重写为与第二个一样快？

顺便说一句，如果您想知道所有几何/拓扑是什么，我需要知道在创建这个难题时所有三角形是如何相互连接的：
https ://puzzling.stackexchange.com/questions/105275/dragon -召唤咒语