>>Make also sure that statistics are up to date.
>
>I finally found something interesting. I used to always create those index, those ones which contain several keys, by including they keys following the order of the WHERE clause. While this is good, in that particular situation, it was not as good as including the keys from the join conditions and then the WHERE clause. After I created the index, in respect to that approach, I was able to obtain an immediate result. The first one, right after I restart the SQL Server, takes about 2.5 second. I did the test several times and I was able to obtain the result. The first hit is a little bit longer so as any other hit we would do on that big table. But, the index does its job. I can see the execution plan has changed a little bit now showing more in the index seek than the lookup. It also shows that it benefits from that index.
>
>That is also by including the @Field as is in the WHERE clause, thus preserving the parameterized approach.

Since I never saw the query nor the structure of the table(s) including indexes, it's hard to comment on this approach.