在sql server 2005存储过程中使用函数调用？

发布于 2024-12-05 13:17:57 字数 332 浏览 0 评论 0原文

在存储过程的 where 子句中使用函数调用会降低 sql server 2005 的性能？

SELECT * FROM Member M 
WHERE LOWER(dbo.GetLookupDetailTitle(M.RoleId,'MemberRole')) != 'administrator' 
AND LOWER(dbo.GetLookupDetailTitle(M.RoleId,'MemberRole')) != 'moderator'

在此查询中， GetLookupDetailTitle 是用户定义的函数，而 LOWER() 是内置函数，我正在询问这两个函数。

原文

Use of function calls in where clause of stored procedure slows down performance in sql server 2005?

SELECT * FROM Member M 
WHERE LOWER(dbo.GetLookupDetailTitle(M.RoleId,'MemberRole')) != 'administrator' 
AND LOWER(dbo.GetLookupDetailTitle(M.RoleId,'MemberRole')) != 'moderator'

In this query GetLookupDetailTitle is a user defined function and LOWER() is built in function i am asking about both.

分享到QQ

分享到微博

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

发布评论

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

香橙ぽ 2024-12-12 13:17:57

是的。

这两种做法都是应尽可能避免的。

将几乎任何函数应用于列可以使表达式 unsargable 这意味着无法使用索引，即使该列没有索引，它也会使计划的其余部分的基数估计不正确。

此外，您的 dbo.GetLookupDetailTitle 标量函数看起来像是进行数据访问，这应该内联到查询中。

查询优化器不会从标量 UDF 内联逻辑，并且您的查询将为源数据中的每一行执行此查找，这将有效地强制执行嵌套循环连接，无论其适用性如何。

此外，由于有 2 个函数调用，这实际上每行会发生两次。您可能应该重写为类似的内容，

SELECT M.* /*But don't use * either, list columns explicitly... */
FROM Member M 
WHERE NOT EXISTS(SELECT * 
                 FROM MemberRoles R 
                 WHERE R.MemberId = M.MemberId 
                 AND R.RoleId IN (1,2)
                 )

不要试图将文字值 1,2 替换为具有更具描述性名称的变量，因为这也可能会扰乱基数估计。

Yes.

Both of these are practices to be avoided where possible.

Applying almost any function to a column makes the expression unsargable which means an index cannot be used and even if the column is not indexed it makes cardinality estimates incorrect for the rest of the plan.

Additionally your dbo.GetLookupDetailTitle scalar function looks like it does data access and this should be inlined into the query.

The query optimiser does not inline logic from scalar UDFs and your query will be performing this lookup for each row in your source data, which will effectively enforce a nested loops join irrespective of its suitability.

Additionally this will actually happen twice per row because of the 2 function invocations. You should probably rewrite as something like

SELECT M.* /*But don't use * either, list columns explicitly... */
FROM Member M 
WHERE NOT EXISTS(SELECT * 
                 FROM MemberRoles R 
                 WHERE R.MemberId = M.MemberId 
                 AND R.RoleId IN (1,2)
                 )

Don't be tempted to replace the literal values 1,2 with variables with more descriptive names as this too can mess up cardinality estimates.

回复收藏 0 原文