SQL Server - CLR 在标量函数中存储过程作为过滤器在查询优化器中未正确加权 ==>糟糕的执行计划

发布于 2024-10-01 19:04:05 字数 333 浏览 9 评论 0原文

我有一个如下所示的查询：

SELECT someString  FROM
(
    SELECT someString FROM someTable
    WHERE someField = 1
) X
WHERE dbo.fnMyClrScalarFunction(X.someString) = 0

问题是查询优化器正在将 UDF 移动到子查询内，在相当严格的“someField = 1”条件之前应用它。不幸的是，UDF 速度并不快，这导致性能很差。有没有什么方法可以防止这种情况（除了使用临时表）或向 sql server 表明 UDF 很昂贵？

提前致谢

原文

I have a query that looks like the following:

SELECT someString  FROM
(
    SELECT someString FROM someTable
    WHERE someField = 1
) X
WHERE dbo.fnMyClrScalarFunction(X.someString) = 0

The problem is that the query optimizer is moving the UDF inside the subquery, where it is applied before the fairly restrictive 'someField = 1' condition. Unfortunately, the UDF is not exactly speedy, and this results in terrible performance. Is there any way to prevent this (aside from using a temp table) or to establish to sql server that the UDF is expensive?

Thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

荒芜了季节 2024-10-08 19:04:05

“相当限制性的‘someField = 1’条件”

您在该列上有索引吗？如果 SQL 认为这样做会更快，则应仅应用标量函数。如果您的索引的第一列是“someField”，那么我建议您确保该表的统计信息是最新的。

回复收藏 0 原文

甩你一脸翔 2024-10-08 19:04:05

一种方法是使用临时表或表变量：

declare @t table (SomeString varchar(100))
insert @t select someString from someTable where someField = 1
select someString from @t where  dbo.fnMyClrScalarFunction(someString) = 0

临时表看起来比实际更昂贵。事实上，大多数连接都是使用临时辅助表执行的:)

One way is to use a temporary table, or a table variable:

declare @t table (SomeString varchar(100))
insert @t select someString from someTable where someField = 1
select someString from @t where  dbo.fnMyClrScalarFunction(someString) = 0

Temporary look more expensive than they are. In fact, most joins are executed using temporary helper tables :)

回复收藏 0 原文

~没有更多了~