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.