T-SQL CTE材料化技术在SQL Server 2012上不起作用

Question

T-SQL CTE材料化技术在SQL Server 2012上不起作用

sql-servert-sqlsql-server-2012common-table-expressionmaterialized

7

我必须使用以下技术来实现我的CTEs并提高视图性能：

WITH CTE AS(
    SELECT TOP 100 PERCENT
    ORDER BY ...
)

WITH CTE AS(
    SELECT TOP 2147483647
    ORDER BY ...
)

目前，这两种方法都不起作用。是否有人遇到了相同的问题或者知道在SQL Server 2012中这些方法是否无效？

- gotqn

3

“TOP 100 PERCENT”几乎总是被忽略。但“TOP 1E2 PERCENT”或许可行。另外，“TOP 2147483647”很可能不应该是“PERCENT”。即使现在有效，但随时可能停止工作。将其实现为一个“#temp”表将更加健壮。 - Martin Smith

@MartinSmith 谢谢。这只是数字，没有百分号。无论如何，TOP 1E2也不起作用。实际上，我已经从你对我的问题的一个答案中学会了“材料化”CTE :-]。不幸的是，它们似乎在SQL MS 2012上不起作用...我不能使用#temp表，因为这是代码视图。 - gotqn

2

@Blam - SQL Server可能会将其材料化为一个spool而不是反复重新评估底层查询。这在此处的连接项目中讨论。不过，我此刻没有访问2012实例的机器。 - Martin Smith

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bogdan Sahlean · Accepted Answer

您可以尝试使用多步骤表值函数来进行翻译，以此强制服务器将TVF的结果材料化为表变量。此外，在声明此表类型（PRIMARY KEY、UNIQUE、CHECK）时，您还可以尝试使用声明性约束来提高最终查询的性能：

CREATE FUNCTION CocoJamboSchema.CocoJamboFunction(@parameters ...)
RETURNS @Results TABLE (
    Col1 INT NOT NULL,
    Col2 VARCHAR(10) NULL,
    ...
    PRIMARY KEY(Col1)
)
AS
BEGIN
    WITH MyCTE (...)
    AS
    (
        ...
    )
    INSERT @Results (...)
        FROM MyCTE;

    RETURN;
END;

SELECT ...
FROM CocoJamboSchema.CocoJamboFunction(param values) f
INNER JOIN MySchema.MyTable t ON f.Col1=t.Col1
ORDER BY t.Col1;

不要忘记在最终查询中添加ORDER BY子句。

最近，我使用了这个解决方案来优化一个由其他视图（ViewB）使用的视图（ViewA，DISTINCT + LEFT JOIN + GETDATE()）。在这种情况下（ViewA）无法创建索引视图（因为DISTINCT + LEFT JOIN + GETDATE()）。相反，我创建了一个多语句TVF，通过减少最终查询的逻辑读取（在某些情况下大幅减少）来提高性能。

注意：当然，你可以尝试使用索引视图。