Optimise use of multiple user defined functions in join condition

Question

Working in SQL Server 2016 (v13).

I am performing a left join between two tables (tblA and tblB) where the join condition includes multiple user defined scalar functions (UDFs). The UDFs take a parameter from tblA and tblB. I don't know if or how to optimise the query.

The (reduced) query is along the lines of:

   select *
     from tblA A
left join tblB B 
       on 1 = dbo.udf1(A.field1, B.anotherField1)
      and 1 = dbo.udf2(A.field2, B.anotherField2)

As an example, dbo.udf1 and dbo.udf2 are something like:

CREATE FUNCTION dbo.udf1 (@p1 VARCHAR(100), @p2 VARCHAR(100))
  RETURNS bit
AS
BEGIN
    DECLARE @result BIT;
    SELECT @result = IIF(@p1 LIKE @p2, 1, 0);
    RETURN(@result)
END

The UDFs are naturally a bit more complex, but are both written as a single SELECT (i.e., both are INLINE functions).

I cannot find a way to rework this into a form making use of table value functions. I'm assuming that there must be a way to improve this query, but not sure if there is, or how to start.

Answer 1

I cannot find a way to rework this into a form making use of table value functions.

This is possible. Note, that this logic requires quadratically comparing all rows from A to all rows from B. This is necessarily rather slow.

So rewrite as follows:

SELECT ...
FROM A
CROSS JOIN B
CROSS APPLY dbo.MyPredicate(A.x, B.y) p
WHERE p.SomeValue = 1

The CROSS JOIN is the quadratic comparison. After the join you have the values from both tables available to execute the join condition.