How can I increase the performance of scalar functions in SQL?

Question

In my table view i have 4 scalar functions to be called. eg:

CREATE view [SUMMARY]
AS 

SELECT  [Code] ,
    ...
    dbo.[udf_REPORTTYPE](ts.LegTankSystemId,ReportingDate,analysis.[AnalysisTypeName]) as ReportingType ,   
    dbo.[udf_WATER](ts.SystemId,LastObsDate) as WaterReading,
    case when analysis.LastObsDate is null then NULL else dbo.[udf_BJs_GENERAL](ts.LegTankSystemId,ISNULL(analysis.LastObsDate, ssd.ReportingDate)) end as [1Plus130_Reslut],
  (CASE  WHEN State ='NY' THEN dbo.[udf_NY](ts.SystemId,LastObsDate)
         WHEN State ='CT' THEN dbo.[udf_CT] (ts.SystemId,LastObsDate)
    END) AS State_SIR_Result
FROM LOBDW.SITE dimsite
LEFT JOIN [bjs].[udv_TANK] ts on dimsite.SiteId = ts.SiteId

...

In those scalar function I read same parameter and doing different calculations.

To return 400 rows My view takes more than 1 minute. It is really bad. How Can I improve the performance of those scalar functions? Is there a way to define global variable from one function and use that in another function if both functions requires same data?

eg function;

CREATE FUNCTION [dbo].[udf_REPORTTYPE]
    (
      @TankSystemId int,
      @TimeStamp datetime2(7),
      @AnalysisTypeName varchar(10)
    )
RETURNS varchar(10)
AS 
BEGIN
    DECLARE @ReportType varchar(10);
    DECLARE @TimePeriod datetime2(7)
    DECLARE @LatestAnalysisDate datetime2(7)

    SELECT TOP 1 @TimePeriod = Date FROM udv_DailySiraData  where TankSystemId=@TankSystemId  ORDER BY Date DESC
    SELECT TOP 1 @LatestAnalysisDate = LastObsDate FROM [udv_ANALYSES] where TankSystemId =@TankSystemId  ORDER BY LastObsDate DESC

    SET @ReportType=@AnalysisTypeName
    IF ((@TimePeriod>=@TimeStamp) AND (@LatestAnalysisDate < @TimeStamp) AND @AnalysisTypeName IS NULL)
         SET @ReportType = 'No Analysis Result';
    IF ((@TimePeriod>=@TimeStamp) AND @AnalysisTypeName IS NULL)
         SET @ReportType = 'Latest';

    RETURN @ReportType;
END;

Answer 1

Here's a CTE alternative to the OUTER APPLY, although I'm sure that works just fine...

WITH TimePeriods AS (
    SELECT
        TankSystemId,
        TimePeriod = MAX(Date)
    FROM
        udv_DailySiraData
    GROUP BY
        TankSystemId
), AnalysisDates AS (
    SELECT
        TankSystemId,
        LatestAnalysisDate = MAX(LastObsDate)
    FROM
        udv_ANALYSES
    GROUP BY
        TankSystemId
)
SELECT
    ...
    ReportingType =
        CASE
            WHEN tp.TimePeriod >= ReportingDate AND ad.LatestAnalysisDate < ReportingDate AND analysis.AnalysisTypeName IS NULL THEN 'No Analysis Result'
            WHEN tp.TimePeriod >= ReportingDate AND analysis.AnalysisTypeName IS NULL THEN 'Latest'
            ELSE analysis.AnalysisTypeName
        END,
    ...
FROM
    LOBDW.SITE dimsite
LEFT JOIN
    bjs.udv_TANK ts
    ON dimsite.SiteId = ts.SiteId
LEFT JOIN
    TimePeriods tp
    ON ts.LegTankSystemId = tp.TankSystemId
LEFT JOIN
    AnalysisDates ad
    ON ts.LegTankSystemId = tp.TankSystemId

Answer 2

you can convert your UDF to OUTER APPLY , something like this

SELECT  [Code] ,
    ...
    --dbo.[udf_REPORTTYPE](ts.LegTankSystemId,ReportingDate,analysis.[AnalysisTypeName]) as ReportingType ,   -- change from UDF to Outer Apply
    RT.ReportingType,
    dbo.[udf_WATER](ts.SystemId,LastObsDate) as WaterReading,
    case when analysis.LastObsDate is null then NULL else dbo.[udf_BJs_GENERAL](ts.LegTankSystemId,ISNULL(analysis.LastObsDate, ssd.ReportingDate)) end as [1Plus130_Reslut],
    (CASE  WHEN State ='NY' THEN dbo.[udf_NY](ts.SystemId,LastObsDate)
        WHEN State ='CT' THEN dbo.[udf_CT] (ts.SystemId,LastObsDate)
    END) AS State_SIR_Result
FROM    LOBDW.SITE dimsite
    LEFT JOIN [bjs].[udv_TANK] ts on dimsite.SiteId = ts.SiteId
    OUTER APPLY -- convert from udf_REPORTTYPE
    (
        SELECT  ReportingType = 
                CASE    WHEN    MAX(x.DATE)    >= ssd.ReportingDate 
                        AND     MAX(y.LastObsDate) <  ssd.ReportingDate 
                        AND     analysis.[AnalysisTypeName] iS NULL
                        THEN    'No Analysis Result'
                        WHEN    MAX(x.DATE)    >= ssd.ReportingDate 
                        AND     analysis.[AnalysisTypeName] iS NULL
                        THEN    'Latest'
                        ELSE    analysis.[AnalysisTypeName]
                        END
        FROM    udv_DailySiraData x
            INNER JOIN udv_ANALYSES y   ON  x.TankSystemId  = y.TankSystemId
        WHERE   x.TankSystemId  = ts.LegTankSystemId
    ) RT