Avoid calling T-SQL function multiple times

Question

I have an inline function which result I need multiple times during a T-SQL script execution. The results from the function are quite large. I am trying to figure out how to call the function only once.

This is my function:

CREATE OR ALTER FUNCTION getRecords (@earliestDate Datetime, @token1 varchar(64), @token2 varchar(64))
RETURNS TABLE
AS
RETURN
(
    SELECT  CONVERT(DATE, LogDate) AS LogDate, UserType, UserStatus
        FROM LogTable WITH (NOLOCK)
        WHERE UserStatus = 'Active'
            AND LogDate >= @earliestDate
            AND (token = @token1 OR token = @token2)
        GROUP BY LogDate, UserType, UserStatus
);
GO

And I need to use the result in different places later on in the script. For instance:

DECLARE @token1 varchar(64) = '1111'
DECLARE @token2 varchar(64) = '9999'
DECLARE @earliestDate Datetime='2020-09-01';

SELECT Product, UserType, 

    (SELECT COUNT(DISTINCT UserStatus) 
        FROM getRecords (@earliestDate, @token1, @token2) AS GR
        
        INNER JOIN mySecondTable AS ST
        ON GR.UserType = ST.UserType 
    ) AS MyCount

FROM  ProductTypes INNER JOIN getRecords (@earliestDate, @token1, @token2) AS GR2
ON ProductTypes.UserType = GR2.UserType;
WHERE ProductTypes.avail = 1;

It is a long script. I need to use the results over different steps. I tried creating a temporary table but the process was too slow.

The above query works but I would like to make it more efficient.

As always, thank you for any suggestions.

Answer 1

Calling functions can be very slow in SQL engines. I'd recommend creating a view and then joining to the view -- this should be much faster. If I have time I will code you an example

" if I use a view, can I use the view results multiple times or are the view results re-created everytime I use them?"

Results are "re-created" every time, but the query is compiled which means you can get a lot of increased performance. Depending on the platform the system may even do caching an re-use queries against data if it is not dirty.

Also note that most systems have the ability to create "materialized views" which then will do the query once and store it as a table till you asked for it to be recalculated. This works differently on SQL Server, Oracle and DB2 but you can get the same functionality.

For some systems to can add parameters to view (called shockingly parameterized views) but I don't recommend this -- depending on your data model there is probably a better way to do this functionality (in your case I would do the group by after I select from the view, for example.

Also remember

  AND (token = @token1 OR token = @token2)

is the same as

    AND token IN (@token1, @token2)

But some platforms might not support using parameters this way. In that case you can probably use a join like this

WITH filter(t) AS 
(
   VALUES  (@token1),
           (@token2)
)
SELECT x
FROM  y
JOIN filter ON filter.t = y.token