>>Try to add
>>

>>OPTION (RECOMPILE)
>>

>
>I have found that if I remove the TOP 100 clause that this helps. Basically, I get results within a second or up to three seconds. When I have this clause, it can take between 20 and 100 seconds. So, basically, I have removed it from the six locations where it was before in the code.
>
>However, with OPTION (RECOMPILE), the result was instant. I also tried with other searches and it is always as VFP was doing it.
>
>I also found that with OPTION (RECOMPILE), queries that were running before without the TOP 100 clause, which were running ok, are also faster.
>
>What does it do exactly? Should I include that in all my SQL exec command?

The OPTION (RECOMPILE) tells the SQL Server Query Processor to not cache the query plan. As result every new parametrized query gets new execution plan instead of reusing a previous one that may not be optimized for the current parameters values.
For a simple query producing new execution plan should be cheap compare to the performance lost. It could be not the case for a complex one.