Why calculated fields using scalar functions are slow-CodePudding

I have below table:

CREATE TABLE [dbo].[Client](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](150) NOT NULL,
    [InternalSiteId]  AS (isnull(CONVERT([int],[dbo].[GetCurrentTemporalValue]([Id],'Client_InternalSite')),(0))),
    [BudgetingStatusId]  AS (isnull(CONVERT([int],[dbo].[GetCurrentTemporalValue]([Id],'Client_BudgetingStatus')),(1))),
    [BusinessUnitId]  AS (isnull(CONVERT([int],[dbo].[GetCurrentTemporalValue]([Id],'Client_BusinessUnit')),(0))),
 CONSTRAINT [PK_dbo.Client] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

which has 3 calculated fields using scalar function:

CREATE FUNCTION [dbo].[GetCurrentTemporalValue]
(
    @clientId INT,
    @temporalType NVARCHAR(128)
)
RETURNS NVARCHAR(255)
AS
BEGIN

    DECLARE @retVal INT
    DECLARE @at DATETIME

    SET @at = GETUTCDATE()

    SELECT @retVal = CAST(Value AS INT) FROM dbo.Temporal 
    WHERE 1 = 1
    AND ClientId = @clientId 
    AND TemporalType = @temporalType
    AND ( ValidFrom <= @at OR ValidFrom IS NULL ) 
    AND ( ValidTo >= @at OR ValidTo IS NULL)
    
    RETURN @retVal
END
GO

and this function uses below table:

CREATE TABLE [dbo].[Temporal](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientId] [int] NOT NULL,
    [Value] [nvarchar](255) NULL,
    [ValidFrom] [datetime2](7) NULL,
    [ValidTo] [datetime2](7) NULL,
    [TemporalType] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.Temporal] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

The Client table has around 2500 records but the select * from [Client] takes about 14 seconds!

When I comment these 3 calculated fields, it gets back to normal value below one second. So the scalar function seems to cause the issue.

I tried to make condition easier ( left only 1 = 1 ) but it didn't change the speed.

What I want to achieve is:

historical values (kept in Temporal table)
current value to be pointed out by Client.InternalSiteId, Client.BudgetingStatusId and Client.BusinessUnitId
I cannot just simply make InternalSiteId a regular int fields with foreign key to, let say, InternalSiteHistoryTable as there are ValidFrom and ValidTo fields pointing out the period in which given value is valid and current and e.g. ValidFrom can be set to future value. That's why I need such calculations in function to find out current value.

What should I do / change to achieve above goals, but keep the reasonable fetching data speed?

CodePudding user response：

Use a view to join to your Temporal table instead of embedding function calls

-- Table without those functions to slow things down
CREATE TABLE [dbo].[ClientName](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_dbo.Client] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) 
ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--instead, use a view to do the same thing
CREATE VIEW [dbo].[Client] as 
   SELECT
       ClientName.Id,
       ClientName.Name,
       isnull(CONVERT([int], Client_InternalSite.Value), 0) AS InternalSiteId,
       isnull(CONVERT([int], Client_BudgetingStatus.Value, 1) AS BudgetingStatusId,
       isnull(CONVERT([int], Client_BusinessUnit.Value, 0) AS BusinessUnitId
   FROM ClientName INNER JOIN 
       ( SELECT Value, ClientId
         FROM Temporal
         WHERE TemporalType='Client_InternalSite'
          AND ( ValidFrom <= GETUTCDATE() OR ValidFrom IS NULL ) 
          AND ( ValidTo >= GETUTCDATE() OR ValidTo IS NULL)
       ) AS Client_InternalSite ON ClientName.ID = Client_InternalSite.ClientID
       INNER JOIN
       ( SELECT Value, ClientId
         FROM Temporal
         WHERE TemporalType='Client_BudgetingStatus'
          AND ( ValidFrom <= GETUTCDATE() OR ValidFrom IS NULL ) 
          AND ( ValidTo >= GETUTCDATE() OR ValidTo IS NULL)
       ) AS Client_BudgetingStatus ON ClientName.ID = Client_BudgetingStatus.ClientID
       INNER JOIN
       ( SELECT Value, ClientId
         FROM Temporal
         WHERE TemporalType='Client_BusinessUnit'
          AND ( ValidFrom <= GETUTCDATE() OR ValidFrom IS NULL ) 
          AND ( ValidTo >= GETUTCDATE() OR ValidTo IS NULL)
       ) AS Client_BusinessUnit ON ClientName.ID = Client_BusinessUnit.ClientID

I can't test this against your DB, so I don't know about your indexes on your Temporal table (ID, ValidFrom, ValidTo columns), but typically a VIEW like this is going to run quicker because the tables are queried only once.