时间戳

首页 > 解决方案 > 返回字段的数量是否会影响 TVF 的性能？

tsql - 返回字段的数量是否会影响 TVF 的性能？

问题描述

我试图弄清楚来自下一个 TVF 的不同请求字段集是否会影响性能（查询优化器是否会计算其主体内的所有选定语句，尽管您希望在外部检索多少字段？）：

CREATE function [dbo].[fn_bc_snapshot_OptionValue](
@date_min smalldatetime,
@date_max smalldatetime,
@uid int,
@isRecalc bit
)
returns table
AS
RETURN (                      
select
    convert(varchar(8), s.t_date, 112) as t_date,
    s.stock_id,
    convert(varchar(8), s.exp_date_MDB, 112) as exp_date,
    cast(convert(decimal(18,3),s.strike) as varchar) as strike,
    s.call_put,
    s.root_id,
    s.root,
    s.style,
    s.option_symbol_MDB as option_symbol,
    cast(convert(decimal(18,3),s.bid) as varchar) as bid,
    cast(convert(decimal(18,3),s.ask) as varchar) as ask,
    case when s.settlement = 0 then s.settlement else cast(convert(decimal(10,3),s.settlement) as varchar) end as settlement,
    cast(convert(decimal(18,3),s.price) as varchar) as price,
    s.open_interest,
    s.volume,
    cast(convert(decimal(18,5),s.iv) as varchar) as iv,
    cast(convert(decimal(18,5),s.delta) as varchar) as delta,
    cast(convert(decimal(18,5),s.gamma) as varchar) as gamma,
    cast(convert(decimal(18,5),s.theta) as varchar) as theta,
    cast(convert(decimal(18,5),s.vega) as varchar) as vega,
    cast(convert(decimal(18,5),s.rho) as varchar) as rho,
    cast(isnull(s.is_interpolated,0) as varchar) as is_interpolated,
    cast(isnull(s.status,0) as varchar) as status,
    s.shift,
    s.expiration_id,
    convert(varchar(8), s.calc_date, 112) as calc_date

    ,s.spc
    ,s.se_code
    ,convert(varchar(8), DATEADD(D,s.shift,s.exp_date_MDB), 112) as shifted_expiry   -- added by O.Brilkov 2019-02-06
from iv.dbo.bc_snapshot_OptionValue s
left join (select stock_id from iv.dbo.fn_BulkUser_stock(@uid)) instruments on s.stock_id = instruments.stock_id
where (s.t_date between @date_min and @date_max or s.calc_date between @date_min and @date_max)
    and s.is_recalc = case when isnull(@isRecalc,1) = 1 then s.is_recalc else 0 end
    and s.stock_id = case when @uid is null then s.stock_id else instruments.stock_id end
    --and s.t_date >= isnull((select ContractDate from iv.dbo.BulkUser where bulkUserID = @uid),s.t_date)
)

以下是我正在执行的查询：

select t_date, stock_id, exp_date, strike, call_put, root_id, [root], style, option_symbol, bid, ask, settlement, price, open_interest, volume, iv, delta, gamma, theta, vega, rho, is_interpolated, [status], shift, expiration_id, calc_date, shifted_expiry
from iv.dbo.fn_bc_snapshot_OptionValue('20190205', '20190205',438,1)

select t_date, stock_id, exp_date, strike, call_put, root_id, [root], style, option_symbol, bid, ask, settlement, price, open_interest, volume, iv, delta, gamma, theta, vega, rho, is_interpolated, [status], shift, expiration_id, calc_date
from iv.dbo.fn_bc_snapshot_OptionValue('20190205', '20190205',438,1)

如您所见，伙计们，第二个查询中没有“shifted_expiry”，但是每次我调用我的函数时都会计算它吗？

标签： tsqlsql-server-2008

解决方案

看看这个例子：

CREATE FUNCTION TestFunction ()
RETURNS TABLE AS
RETURN (
    SELECT
        Constant = 'Value',
        FailedExpression = CONVERT(INT, 'Failed')
)

如果我们尝试选择所有列...

SELECT T.* FROM TestFunction() AS T

-- Msg 245, Level 16, State 1, Line 1
-- Conversion failed when converting the varchar value 'Failed' to data type int.

但只有一个没有问题：

SELECT T.Constant FROM TestFunction() AS T

-- Constant
-- Value

引擎足够聪明，不会计算不需要的东西。使用非内联 TVF 时例外。

推荐阅读