嵌套选择内的昂贵函数

Question

我正在使用昂贵的用户定义标量函数调用进行相对复杂的 CTE 查询。

CTE的where子句过滤了大部分大表数据，并返回相对较小的结果，因此如果在结果集上计算，计算昂贵的字段不是问题。

如果不评估昂贵的列，如果没有被 CTE 子查询内的任何谓词触及，则 SQL Server 做得很好，它仅针对结果集进行评估。

问题是，在这种情况下我可以依赖优化器的这种良好行为，还是重建计划时可能会出现问题？

这是一个测试代码。

use tempdb;
go
/****** Object:  UserDefinedFunction [dbo].[expensive]    Script Date: 01/15/2010 18:43:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[expensive]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[expensive]

go
-- used to model expensive user defined function.
-- inexpensive in case of @i = 1
create function dbo.expensive(@i int)
returns int
as
begin
 if( @i = 1 ) begin return @i; -- inexpensive in this case
 end;
 declare @N bigint
 declare @ret bigint
 set @N = 17; -- will generate a set of 2^N

 declare @tab table(num int);

 with gig as
 (
  select 1 as num
  union all
  select g.num + 1 as num from gig g
  where g.num < @N
  union all
  select g.num + 1 as num from gig g
  where g.num < @N
 )
 select @ret = count(num) from gig;
 return @ret;
end

go

declare @tab table(i int);

insert into @tab select 1 union select 2 union select 3;

select CURRENT_TIMESTAMP;

with source as
(
 -- some really complex stuff that has an expensive calcutated scalar
 select c.i, c.caclulated from @tab t
 join
 (select i, dbo.expensive(i) as caclulated from @tab) as c
 on t.i = c.i
)
select * from source where
 i = 1; -- this query is inexpensive, because do not touch anything but 1

select CURRENT_TIMESTAMP;

-- this one is expensive
select dbo.expensive(2)

select CURRENT_TIMESTAMP;

Answer 1

你永远不会知道。您可以做的一件事是使用计划指南来帮助优化器保持在正确的道路上。请参阅使用计划指南优化已部署应用程序中的查询。