sql-server - 如果 CTE 溢出到 tempDB，临时表会是更好的选择吗？

Question

我有一个使用 CTE 来定义员工部门的查询。

当查询优化器选择散列匹配连接时，CTE 会溢出到 tempdb。

• 在这种情况下，使用临时表而不是 CTE 会更好吗？
• SQL 是否有任何成本试图处理内存中的 CTE，但必须使用 tempdb，这可以通过故意指示它使用 tempdb 来减轻？

如果有帮助，我的代码如下所示：

;WITH cte_Staff_Departments
AS (
    SELECT DISTINCT
        [stf].[contact_id]
        , CASE
            WHEN [stf].[department] NOT IN('HR', 'SALES', 'IT')
                THEN [dpt].[department]
                ELSE [stf].[department]
            END AS [Department]
    FROM [dbo].[staff] AS [stf]
        LEFT OUTER JOIN [dbo].[departments] AS [dpt] ON [stf].[contact_id] = [dpt].[contact_id]
    WHERE ([stf].[ownership_group] in ('COR','SLE')
        OR [dpt].[ownership_group] in ('COR','SLE')
) 
select contact_id, department from cte_Staff_Departments

谢谢 LS23

标签： sql-servertsql

Answer 1

@lindsayScott23，我只是建议。请不要标记我。我实际上将取消 CTE 并使用子查询编写查询。像这样：

SELECT contact_id, department 
FROM (
    SELECT 
        [stf].[contact_id], 
        CASE
            WHEN [stf].[department] NOT IN('HR', 'SALES', 'IT')
                THEN [dpt].[department]
                ELSE [stf].[department]
        END AS [Department]
    FROM [dbo].[staff] AS [stf]
        LEFT JOIN [dbo].[departments] AS [dpt] 
            ON [stf].[contact_id] = [dpt].[contact_id]
            AND (
                [stf].[ownership_group] in ('COR','SLE')
                OR 
                [dpt].[ownership_group] in ('COR','SLE')
            )
) CSD
GROUP BY
    contact_id, department