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

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.

Answer 2

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 3

Parameterized queries have a lot of advantages, including often a hefty performance increases.

• Caching of queries
• String Concatenation problems minimized
• addressing SQL injection
• Data does not have to be converted to a string before processing

Answer 4

Stored procedures can run faster because the execution plan is cached by sql server.

But 10 times performance is suspicious. Does it run the same the First time after you've cleared the stored execution plans? You can use these commands to clear the cache. But they clear the whole servers cache so do it only on development servers.

DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB (<dbid>)

Are you running these directly on the SQL server to eliminate any network I/O from the performance testing?

My guess is that it is running slowly the first time, then once it is cached it is running faster.

Answer 5

The connection-level settings can be critical in some cases, especially ANSI NULLS, CONCAT NULL YIELDS NULL, etc. In particular, if you have calculated persisted indexed columns (including promoted "xml" columns), then it won't trust the pre-calculated, index value if the settings aren't compatible, and will recalculate for each row (i.e. table scan instead of index seek).

Answer 6

Parameter sniffing may be affecting the stored procedure performance.

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html