TOP performance issue

user_2208597 0
Viewed 81

I have this query, actually it is similar to this, this is for testing purpose:

SELECT
[Distinct1].[ProspectID] AS [ProspectID]
FROM ( SELECT DISTINCT 
    [Limit1].[ProspectID] AS [ProspectID]
    FROM ( SELECT TOP 10
        [Extent1].[ProspectID] AS [ProspectID]
        FROM [sqlsolvent].[crm_SearchTable] AS [Extent1]
        WHERE ([Extent1].[CP_Name] LIKE '%dsd%' ESCAPE N'~') 
    )  AS [Limit1]
)  AS [Distinct1]

This is the execution plan for it:

enter image description here

And when I remove TOP 10 from query I get this execution plan:

enter image description here

The second execution plan without TOP 10 is 2 times faster, can someone explain why TOP changes execution plan so much and why it so slower? It is the same even if query doesn't return any result, shouldn't top just be aplied to the result set, why does it have so much impact on performance when query returns nothing?

sql-server sql
3 Answers
Related