>I have a stored proc that uses a UDF. The UDF calculates the number of business days between 2 dates. The result set is dependent on if the UDF returns > 14 days. I know that using the UDF in a where clause would cause unacceptable performance, so I did the following:
>

>select *
>from
>(
>select some data
>from some tables
>to get about 300 rows
>) x
>where dbo.udf_countBusinessDays(x.date,getdate()) > 14
>

>I figured that the sub-select would return about 300 rows and then the UDF would take no time at all. However, it takes the same amount of time as using the UDF in the where clause of a query without the sub-query (1 min. +). The query plans for both are virtually identical.
>
>Can someone explain to me how the sub-select used as a table is working here?
>
>TIA
>John

Hi John,

the SQL optimizer add the external WHERE into the subquery with a AND condition;
force the optimizer to don't make this is very difficult.
This optimization is on 99% the best solution, but not for this case.

Because SQL evaluate the complete expression, if you have not indexes for optimize the inner
where , the evaluation require a lot of time.

You have 3 choices:
1. two steps query
2. filter the query with a worst case condition on the date, with a index for date

select some data
from some tables
where 
and x.date-getdate()>14 -- or 15 16 17 (a sure inf value)
and  dbo.udf_countBusinessDays(x.date,getdate()) > 14

3. Probable SOLUTION : SELECT THE PKs list for the other conditions
and autojoin with business condition

select some data
from (some tables) X
JOIN (some tables) Y
ON X.PK=Y.PK 
WHERE other conditions on X 
AND dbo.udf_countBusinessDays(y.date,getdate()) > 14

Fabio