Joining two inline functions, slows considerably when filtering on second function

Question

I have two inline functions that run quickly enough alone.

When I left join them like this:

select *
from
Function1(-1) F1 
left join
Function2(-1) F2 on F1.key = F2.key

...I get my result set in approx 14 seconds. Perfectly fine for what it's doing.

But if I add a where condition:

Where 
F2.Boolean = 0

...then it takes over 8 minutes to complete.

There are some results that do not have a F2 record so those are null.

I did a test with isnull(f2.boolean,0) = 0 and that runs in 10 seconds, also if a place a or F2.boolean is null it runs quickly.

I am using SQL Server 2008 R2, and tested on SP2 and SP3. Does anyone have ideas or suggestions for what to look at?

The functions are simple select statements joining multiple tables. Nothing special in them. Each individual function returns results of the entire tables in under 10 seconds.

When I add "where one of the date fields in F2 is between two dates", that runs fast. I also just tested on a varchar(2) field in the where clause, and this causes it to be extremely slow as well (but not the datetime field). ok, Why would a filter on a bit field or varchar from the second function cause such a speed decrease, vs a datetime between? Another observed behavior: If I add a filter on F1, to specify only ones that actually have a F2 record first then the original filter on F2 it runs quickly again.

Answer 1

Workaround suggestion:

If you can change the functions, try making them multi-statement table value function instead of inline functions. When doing so, you define an output table in the RETURNS part. In the DDL of the output table, declare a good clustered primary key.

Example:

CREATE FUNCTION dbo.fn_test1(@i int)
RETURNS @out TABLE (
    a    char(5) NOT NULL,
    b    date NOT NULL,
    c    int NOT NULL,
    PRIMARY KEY CLUSTERED (a, b)
)
AS

BEGIN;
    INSERT INTO @out (a, b, c)
    SELECT x, y, z
    FROM dbo.someTable
    WHERE someCriteria>@i;

    RETURN;
END;

Even though swapping an inline function for a multi-statement function comes with its own set of advantages and drawbacks, an important difference in your case may be that the output is stored in a temp table with a clustered index before the join happens. If you've properly aligned the clustered indexes on the two functions, joining their output tables should be a speedy affair.

Here's an idea of what kind of query plan you could expect (excluding the inner workings of the functions, obviously).

Answer 2

Most times this situation comes out from statistic on field "Boolean". Remember that statistics may come from index and can be create automatically or manually. Be sure that statistics on this field are updated. In SQL below 2014 (Cardinality Estimator below 120) values out of statistics has estimation of 0 and this value might be out of statistics because statistics is not updated or sample of update does not take rows with value filtered in your query. You can walkaround this by using variable for value of this column and use OPTION (OPTIMIZE FOR(@ariable UNKNOWN)). In this way statistics are not considered since this will be no longer parameter to the execution plan.