为什么在CTE中的WHERE子句之前执行UDF调用？-Java 学习之路

我试图理解为什么带有UDF（用户定义函数）调用的CTE（公用表表达式）如此之慢 .

表TABLE1有1000万行 .
最后一个where子句（ROWN = 1）正在过滤并仅返回10条记录 .

出于不明原因，MYFUNCTION被称为百万次（在WHERE子句过滤器之前）并且它使查询变慢 . 如果删除了MYFUNCTION调用，则查询立即运行 .

如何在应用WHERE子句后强制SQL运行MYFUNCTION？

WITH MAINDATA
AS
(
    SELECT
        FIELD1,
        FIELD2,
        FIELD3,
        ROW_NUMBER() OVER (PARTITION BY FIELD5 ORDER BY FIELD6) AS ROWN
    FROM
        TABLE1
)
SELECT
    FIELD1,
    dbo.MYFUNCTION(FIELD2, FIELD3) AS FUNCTIONRESULT
FROM
    MAINDATA
WHERE
    ROWN = 1

1 回答

您是否尝试添加顺序cte？

WITH MAINDATA
AS
(
    SELECT
        FIELD1,
        FIELD2,
        FIELD3,
        ROW_NUMBER() OVER (PARTITION BY FIELD5 ORDER BY FIELD6) AS ROWN
    FROM
        TABLE1
) ,
RESULTS (
        SELECT 
            FIELD1
        FROM
            MAINDATA
        WHERE
            ROWN = 1
)
SELECT *
      ,dbo.MYFUNCTION(FIELD2, FIELD3) AS FUNCTIONRESULT
FROM  RESULTS

回复于 2024-04-27T10:56:06+08:00

为什么在CTE中的WHERE子句之前执行UDF调用？

1 回答

相关问题