Table functions with temp tables

Big surprise, its another database post! I’m working on tuning at the moment, so I guess it’s just going to be the hot topic.

Anyway, today I discovered something that blew my mind a little bit. It did so due to the vast performance improvement that it made.

Here’s the context.
We have a table function that creates a temp table (using a table variable).
Then a select from the table.
This database also caters for multiple languages, so there is a call to an external function to get the translated text for a certain section.
The translation is stored in a variable as the text value is the same for all rows, so we don’t have to ask for it for every row.


CREATE FUNCTION dbo.fName
(
    @Culture NVARCHAR(5)
)
RETURNS @Name TABLE ( ID INT,ResolvedName NVARCHAR(150), Name NVARCHAR(255), Inactive bit) 
AS 
BEGIN 
   DECLARE @alias nvarchar(50) 
   SELECT @alias = dbo.fTranslation('Alias', @Culture) 
    
   INSERT INTO @Name 
   SELECT c.ID,   
          n.ResolvedName, 
          Name = n.ResolvedName +   
           CASE WHEN n.NameID <> isnull(TN.NameID,n.NameID) THEN   
           CASE WHEN TN.ResolvedName <>N.ResolvedName  then ' ' + @alias + ' ' +TN.ResolvedName  else ''  end   
                else ''   
            END,   
            (CASE WHEN c.Inactive = 1 OR n.Inactive = 1THEN 1 ELSE 0 END) AS Inactive   
    FROM Contact c   
    INNER JOIN Name n  ON n.ContactId =c.ContactId  AND n.IsPrimaryName = 1 and N.Inactive= 0    
    LEFT JOIN Name TN  ON TN.ContactID =C.ContactID  and TN.NameTypeID = 2  and TN.Inactive= 0   
    WHERE c.IsPrimary = 1   
    RETURN
END 
GO

Now, the above code snippet is fictitious, but it’s based on a real example.

I asked for the top 100 records from this function, and it took between 10 & 11 seconds. Not good.

Here is where things get interesting.

We can improve performance of the function by removing the use of the temp table. We aren’t doing anything that special here, so its unnecessary.

CREATE FUNCTION dbo.fName
( 
   @Culture NVARCHAR(5)
)
RETURNS TABLE
AS
RETURN (
   SELECT c.TaxpayerID,
          n.ResolvedName,
          Name = n.ResolvedName +
          CASE WHEN n.NameID <> isnull(TN.NameID, n.NameID)  then   
          case when TN.ResolvedName <> N.ResolvedName  then ' ' + dbo.fTranslation('Alias', @Culture)  + ' ' + TN.ResolvedName  else ''  end 
 else ''   
         end,
       (CASE WHEN c.Inactive = 1 ORn.Inactive = 1 THEN 1 ELSE 0 END) AS Inactive
   FROM Contact c   
   INNER JOIN Name n 
      ON n.ContactId = c.ContactId 
      AND n.IsPrimaryName = 1 and N.Inactive= 0    
   LEFT JOIN Name TN 
      ON TN.ContactID = C.ContactID
      and TN.NameTypeID = 2  and TN.Inactive= 0   
   WHERE c.IsPrimary = 1
     )
GO

The only roadblock is the translation variable. If i use an anonymous function, i can’t define any variables before it.
So, I’ll have to ask for the translation for every row. Not ideal.

What blew my mind is that this change had a huge improvement. It served up the top 100 records in less than 1 second.
So, even with the terrible calls for the translation, the temp table was killing the function.

Subscribe

0 comments