存在函数时的 TSQL 视图选择优化 [英] TSQL view select optimization when function is present

问题描述

我将这个简单的 SQL 作为 SSIS 任务的源:

I have this simple SQL as a source in a SSIS task:

Select * from budgetview

来源是:

CREATE VIEW [dbo].[BudgetView] AS
SELECT   DISTINCT  Country, 
            SDCO AS Company, 
            SDAN8 AS Customer, 
            SDLITM AS PrintableItemNumber, 
            dbo.fn_DateFromJulian(SDIVD) AS Date, 
            SDPQOR/100.0 AS Quantity, 
            SDAEXP/100.0 AS Value, 
            SDITWT/10000.0 AS Weight
FROM         dbo.F553460

没有关于索引的建议，一切似乎都经过优化.

There are NO advices for indexes, every thing seems optimized.

函数fn_DateFromJulian 来源是:

CREATE FUNCTION [dbo].[fn_DateFromJulian] 
(
    @JulianDate numeric(6,0)
)
RETURNS date
AS
BEGIN
    declare @resultdate date=dateadd(year,@JulianDate/1000,'1900-01-01')
    set @resultdate=dateadd(day,@JulianDate%1000 -1,@resultdate)
    return @resultdate

END

问题是我等了大约 20 分钟才让 SSIS 中的行进入......

The problem is that i am waiting around 20 minutes just to get the rows going in SSIS....

我在那里等了 20 分钟才开始

I am waiting there 20mins BEFORE it gets started

是否有任何建议可以找到罪魁祸首?

Are there any suggestions to find the culprit?

推荐答案

我的假设是花在视图上的时间是通过计算 Julian 日期值消耗的.没有看到实际的查询计划，根据下面的文章，这似乎是一个合理的猜测.

My assumption is that the time spent on the view is consumed by calculating the Julian date value. Without seeing the actual query plan, it seems a fair guess based on the articles below.

将原始函数重写为下面的表值函数(我只是将您的代码混合在一起，可能有改进的机会)

Rewrite the original function as a table valued function below (I've simply mashed your code together, there are likely opportunities for improvement)

CREATE FUNCTION dbo.fn_DateFromJulianTVF
(
    @JulianDate numeric(6,0)
)
RETURNS TABLE AS
RETURN
(
    SELECT dateadd(day,@JulianDate%1000 -1,dateadd(year,@JulianDate/1000,CAST('1900-01-01' AS date))) AS JDEDate
)

用法是

CREATE VIEW [dbo].[BudgetView] AS
SELECT   DISTINCT  Country, 
            SDCO AS Company, 
            SDAN8 AS Customer, 
            SDLITM AS PrintableItemNumber, 
            J.JDEDate AS [Date], 
            SDPQOR/100.0 AS Quantity, 
            SDAEXP/100.0 AS Value, 
            SDITWT/10000.0 AS Weight
FROM         dbo.F553460 AS T
    CROSS APPLY
        dbo.fn_DateFromJulianTVF(T.SDIVD) AS J

标量值函数，闻起来像代码重用，执行起来像重用的一次性尿布

Scalar valued function, smell like code reuse, performs like a reused disposable diaper

• https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html
• http://blogs.lobsterpot.com.au/2011/11/08/when-is-a-sql-function-not-a-function/

这篇关于存在函数时的 TSQL 视图选择优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！