Fabio,
Thanks for the reply. It just seem counter intuitive that the optimizer doesn't process the inner query first. Anyway I worked around it with a 2 step query using a temp table.
Thanks for the information.
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
>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