>>Try adding
>>
>>option (recompile) to the query.
>>
>>http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a2030652-800b-48dd-8857-5e6e60658a6e
>>
>>http://social.msdn.microsoft.com/forums/en-us/transactsql/thread/CFA976DB-2D12-4D80-B4B2-CEFFB80B7F6C
>
>At the same time, I was testing something else. The fact that I have 175 fields to extract was causing this. So, when I subselected to get the primary key first and join that into the main table for the result, it was instant. Then, when I tried the original syntax with the RECOMPILE option, it was then also instant.
>
>Now, which approach is better recommanded. Should I go with the subselect to get the primary key and then join that into the main table or to simply use the RECOMPILE? In the Web site applications, I have it off. But, in this particular script that is executed in SSMS, I could consider it if it would be judged as a better approach.

I suggest your first approach with getting ID first and then join with the rest of the fields.

BTW, I was about to post a better thread when I was interruped by a call from work

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/5ef9b5a1-d25a-4319-869a-e7dbf6acb594

See Alejandro Mesa response there.