SQL Where Clause Against View

Question

I have a view (in fact, this is a table-valued function, but the observed behavior is the same in both cases) that the inner joins and the left outer joins several other tables. When I request this view with a where clause similar to

SELECT *
FROM [v_MyView]
WHERE [Name] like '%Doe, John%'

... the request is very slow, but if I do the following ...

SELECT *
FROM [v_MyView]
WHERE [ID] in 
(
    SELECT [ID]
    FROM [v_MyView]
    WHERE [Name] like '%Doe, John%'
)

MUCH FASTER. The first request takes at least 2 minutes to return, if not longer, where the second request will be returned in less than 5 seconds.

, ? SQL ( ), . , , , OUTER JOINS, GROUP BYS TOP ##, where vs , . , SQL , ?

, AdventureWorks . ( , - , ?) . , , , Scalar Valued Functions. "GetDisplayName", , , , lastname .. /TVF/view , . , , .

Answer 1

UDF . , RBAR. , SELECT, WHERE JOIN....

, , SELECT, UDFs , , UDF, .

Answer 2

SQL-, , , , , , -, , , . , .

:

SELECT * FROM [v_MyView] WHERE [Name] like '%Doe, John%'

:

SELECT * FROM [v_MyView] WHERE [ID] in  
(SELECT [ID] FROM [v_MyView] WHERE [Name] like '%Doe, John%')