Tuesday, February 20, 2018
sql server - Why execute stored procedures is faster than SQL query from a script?
Answer
Answer
In fact, if I call the stored procedures from my application, I need a connection to my DB.
So, why calling a "stored procedures" should be faster than "passing a SQL query" string to be executed?
Answer
SQL Server basically goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):
1) syntactically check the query
2) if it's okay - it checks the plan cache to see if it already has an execution plan for that query
3) if there is an execution plan - that plan is (re-)used and the query executed
4) if there is no plan yet, an execution plan is determined
5) that plan is stored into the plan cache for later reuse
6) the query is executed
The point is: ad-hoc SQL and stored procedures are treatly no differently.
If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.
Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.
Update: using stored procedures over non-parametrized queries is better for two main reasons:
since each non-parametrized query is a new, different query to SQL Server, it has to go through all the steps of determining the execution plan, for each query (thus wasting time - and also wasting plan cache space, since storing the execution plan into plan cache doesn't really help in the end, since that particular query will probably not be executed again)
non-parametrized queries are at risk of SQL injection attack and should be avoided at all costs
plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV
In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...
-
This question attempts to collect the few pearls among the dozens of bad C++ books that are published every year. Unlike many other programm...
-
I need to do the following: My current address looks like: https://www.domain.com I want to redirect with htaccess: www.domain.com TO https:...
-
using namespace std; So far in my computer science courses, this is all we have been told to do. Not only that, but it's all tha...
No comments:
Post a Comment