时间戳

首页 > 解决方案 > 为什么“where”子句中的标量值函数变慢？

sql-server - 为什么“where”子句中的标量值函数变慢？

问题描述

不幸的是，我无法准备一个示例以便您可以在您的计算机上重复它，但我认为这对于理解问题所在没有必要。有这样的查询：

with cte as (
select  selectStations.ReceiverUID, 1907 as id
    from WF4_Routes r WITH (NOLOCK) 
    inner join WF4_Stages selectStages WITH (NOLOCK) on selectStages.RouteID = r.ID and r.SentToCAS = 1 and r.PRUZ <> 1 and selectStages.PRUZ <> 1 and selectStages.StageType = 1 
    inner join WF4_Stations selectStations WITH (NOLOCK) on selectStations.ApprovalStageID = selectStages.ID and selectStations.PRUZ <> 1
)
select *--case when dbo.fnGetGkExchangeParticipantQuick(cte.ReceiverUID, id)  = 'E477B8FA-7539-4B43-8961-807A29FECFC0' then 1 else 0 end
from cte
where dbo.fnGetGkExchangeParticipantQuick(cte.ReceiverUID, id) = 'E477B8FA-7539-4B43-8961-807A29FECFC0'

从整个脚本来看，在“where”条件下调用标量函数和在“select”中被注释掉的调用是很重要的。

CREATE FUNCTION [dbo].fnGetGkExchangeParticipantQuick(@CurrentUID uniqueidentifier, @IsExchangeParticipantAttrID int) 
RETURNS uniqueidentifier 
AS  
BEGIN 
  declare @UnitUID uniqueidentifier 

  select @UnitUID = UID from GL_OBJECTS where UID = @CurrentUID and ACTIVE = 1

  while @UnitUID is not null
  begin
    if (select top 1 cast(PropertyValue as bit) from MB_ATTRIBUTES_TO_VALUES where Object_UID = @UnitUID and Attribute_ID = @IsExchangeParticipantAttrID) = 1 
        break;

    set @UnitUID = null
    select @UnitUID = PARENT from GL_OBJECTS where UID = @UnitUID and ACTIVE = 1
  end

  return @UnitUID
END
GO

该函数从对象层次结构中的父对象中搜索特定属性。

如果我在“where”条件下调用这个函数，那么查询将在 1 秒内执行，而从磁盘读取大约 60 万条记录。如果我在“选择”条件下调用这个函数，那么该函数将在 10 毫秒内执行，从磁盘读取的次数为 30。

如果您查看计划，您可以看到调度程序由于某种原因满足“with”条件和“where”条件，即他正在尝试优化组合条件的执行，我不需要它。我试图将分组添加到“with”条件或“distinct”条件没有帮助。

请帮我理解问题是什么？

标签： sql-servertsql

解决方案

CTE 通常只是语法糖，SQL Server 将整个查询组合成它认为最好的单个执行计划。

您有时可以通过将 CTE 中的函数调用添加为列然后在您的where子句中使用它来解决此问题。

with cte as (
    select selectStations.ReceiverUID
      , 1907 as id
      , dbo.fnGetGkExchangeParticipantQuick(selectStations.ReceiverUID, 1907) as ExchangeParticipant
    from WF4_Routes r WITH (NOLOCK) 
    inner join WF4_Stages selectStages WITH (NOLOCK) on selectStages.RouteID = r.ID and r.SentToCAS = 1 and r.PRUZ <> 1 and selectStages.PRUZ <> 1 and selectStages.StageType = 1 
    inner join WF4_Stations selectStations WITH (NOLOCK) on selectStations.ApprovalStageID = selectStages.ID and selectStations.PRUZ <> 1
)
select *
from cte
where ExchangeParticipant = 'E477B8FA-7539-4B43-8961-807A29FECFC0';

笔记：

您永远不应该在where子句中使用标量函数，因为不能使用索引并且经常需要进行全表扫描。

如果您需要强制查询的 CTE 部分首先执行，那么您可以将结果具体化到一个临时表中，然后从中进行选择。这样 SQL Server 会创建两个独立的执行计划。

推荐阅读