It looks like you have a classic parameter sniffing issue. As far as I can tell your queries are identical but both of them use different cached plans. The cached plan that you get depends on the input parameters for the first run. I assume that there's a plan that works well enough for all inputs and you want that plan. However, depending on parameter values you might get unlucky and end up with a plan that works better for some inputs but poorly for others.
If possible I would add a RECOMPILE
hint to the query. This will prevent the query optimizer from using a cached plan for the query but it will see the values of the variables when making the plan. As a general rule of thumb you'll get better performance if you can give more information to the optimizer. The downside of this approach is that the query plan will be recompiled each time, but this is only a three table query. I wouldn't worry about it unless the query runs hundreds of times per second.
Another option is to use the OPTIMIZE FOR
query hint. This will give you a cached plan that isn't based on input values, so the key is getting a query plan that works well enough for all possible input values. In your other question you implied that you weren't satisfied with the default guess of 100 rows for TOP
so you can force the query optimizer to optimize for a specific TOP
value like follows:
OPTION (OPTIMIZE FOR UNKNOWN, OPTIMIZE FOR (@top = 5000));
None of the input variables will be sniffed to form the plan. That syntax may look odd but it's allowed:
If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.
If neither one of those options works and you're confident that you have a query plan which works well you could look into using plan guides.