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.

Stop splattering nolock everywhere. Don't use that hint unless you understand the consequences, have a valid reason for using it, and accept the errors that can throw. — SMor
– SMor, Commented Oct 8, 2020 at 18:08
Since the parameters you use for the function are exactly the same each time you call it I would suggest you use a temp table Instead of the function. — JMabee
– JMabee, Commented Oct 8, 2020 at 20:15
Thank you @SMor, I had no idea about NOLOCK. I will look into it. — Wilmar
– Wilmar, Commented Oct 9, 2020 at 17:02
Hi @JMabee, I think that is what I will end up doing. I tried before but it was taking a long time. I will look into that. — Wilmar
– Wilmar, Commented Oct 9, 2020 at 17:03

Hogan · Accepted Answer · 2020-10-08 19:40:29Z

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

edited Oct 8, 2020 at 19:40

answered Oct 8, 2020 at 17:05

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wilmar Over a year ago

Thanks Hogan for the comment. I have a follow up question: if I use a view, can I use the view results multiple times or are the view results re-created everytime I use them? Now, I can pass variables to the function, I do not think I can do the same for a view, can I?

Dale K Over a year ago

Inline table functions are usually as fast as views, because they are expanded the same way a view is.

Hogan Over a year ago

@DaleK -- very true. I was assuming that the usefulness of a function was the fact it can be re-used, which is sometimes hard to do with inline functions -- but not so hard with a CTE or view

Collectives™ on Stack Overflow

Avoid calling T-SQL function multiple times

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related