Why is query slow but procedure fast on SQL Server?

Question

Why is it that if I run my query as a parameterized procedure it runs 10 times faster then if I run it directly as a parameterized query?

I'm using the exact same query in both cases, and it doesn't matter if I'm calling from Management Studio or an SqlCommand from code.

EDIT: The execution plan looks different. So, why? I'm calling it with EXACTLY the same set of parameters.

EDIT: After more testing it seems the 10x slowdown only occurs when running the parameterized query from SQL Management Studio.

Answer 1

One thing I've seen recently is that if you set up the query parameters wrong it can cause major problems.

For example, say you have a parameter for an indexed varchar column and set it up from .Net using the SqlCommand's AddWithValue() method. You're in for a world of hurt with this scenario. .Net uses unicode strings and will set up your parameter as an nvarchar rather than varchar. Now sql server won't be able to use your index, and you'll see a significant performance penalty.

Answer 2

Find out if they are using the same execution plan is to display it when running. Use "include actual execution plan" in management studio and see what is the difference.