Geek Answers Handbook

Why is a query a slow but fast process on SQL Server?

Why do I run my query as a parameterized procedure, it runs 10 times faster if I run it directly as a parameterized query?

I use the same query in both cases, and it doesn't matter if I call from Management Studio or SqlCommand from code.

EDIT: The execution plan looks different. So why? I call it EXACTLY the same set of parameters.

EDIT: after more testing, it seems that a 10-fold slowdown only occurs when a parameterized query is run from SQL Management Studio.

+4
source share
6 answers

I recently saw that if you incorrectly configure the request parameters, this can cause serious problems.

For example, let's say you have a parameter for the varchar indexed column and set it from .Net using the SqlCommand AddWithValue() method. With this scenario you are faced with the world..Net uses unicode strings and will set your parameter as nvarchar, not varchar. Now the sql server will not be able to use your index, and you will see a significant decrease in performance.

+10
source

Find out if they use the same execution plan to display it at startup. Use the "enable actual execution plan" in the management studio and see what the difference is.

+4
source

Connection level settings can be critical in some cases, especially ANSI NULLS , CONCAT NULL YIELDS NULL , etc. In particular, if you have calculated stored indexed columns (including advanced "xml" columns), then it will not trust the previously calculated index value if the settings are incompatible, and will be recalculated for each row (for example, scanning a table instead of searching for an index) .

+3
source

There are many advantages to parameterized queries, including high performance often increasing.

  • Request Caching
  • String concatenation issues minimized
  • SQL injection addressing
  • Data should not be converted to a string before processing
+2
source

The sniffing option may affect the performance of the stored procedure.

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

+1
source

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

But 10 times the performance is suspicious. Is this done the first time you run it after you have cleared your saved execution plans? You can use these commands to clear the cache. But they clear the entire server cache, so only do this on development servers.

 DBCC FREEPROCCACHE DBCC FLUSHPROCINDB (<dbid>)

Do you execute them directly on the SQL server to eliminate any network I / O operations during performance testing?

I assume that it runs slowly for the first time, and then when it is cached, it runs faster.

-1
source

More articles:


All Articles