Why execute stored procedures is faster than SQL query from a script?
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?
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
Because every time you pass a query string to SQL Server the code has to be compiled etc, stored procedures are already compiled and ready to run on the server.
Also you are sending less data over the network although this is generally a minimal impact anyway.
EDIT: As a side note stored procedures have other benefits.
1) Security - Since the actual query is stored on the server you are not transmitting this over the network which means anyone intercepting your network traffic does not gain any insight into your table structure. Also a well designed SP will prevent injection attacks.
2) Code seperation, you keep your database code in your database and your application code in your application, there is very little crossover and I find this makes bug fixing a lot nicer.
3) Maintainability and Code Reuse, you can reuse a procedure many times without having to copy paste the query, also if you wish to update the query you just have to update it in one place.
4) Decreased network traffic. As mentioned above this may not be an issue for most people but with a large application you can significantly reduce the ammount of data being transferred via your network by switching to using stored procedures.
Unlike standard SQL statements, stored procedures are compiled and optimized by the database server. This optimization involves using information about the structure of a particular database that's required at execution time by the stored procedure. This process of storing execution information (the execution plan) is a tremendous time saver, especially if the stored procedure is called many times.
Speed is also improved by the fact that stored procedures run entirely on the database server - there's no need to pass large chunks of SQL code over a network. For a simple SELECT statement, that might not make a big difference, but in cases where we perform a series of loops and calculations, it can have a significant effect.
stored procedures are compiled and cached. But SQL statements will be compared to existing execution plans and if a match is present used, thus nullifying somewhat any advantage.
Whats the actual performance difference after a number of executions ?
Your statement that Stored Procedures are faster than SQL Queries is only partially true. To explain: Most of these answers already explain that with stored procedures a query plan is generated and cached. So if you call the stored procedure again, the SQL engine first searches through its list of query plans and if it finds a match, it uses the optimized plan.
Passing a normal query does not allow for this advantage as the SQL engine does not know what to expect and thus it cannot find a match for your query. It creates a plan from scratch and then renders your results.
Good news: You can enable plan caching for your queries by using Parametized queries, a new feature in SQL. This enables you to generate plans for your queries and can be very effective in your situation as most of the queries you pass from code, remains the same, except for variables in the Where clause mostly. There is also a setting where you can force parameterization for all your queries. Search MSDN for this topic, should help you in deciding what's best.
However, this said, Stored Procedures remains a good way to to interact with the DB from your applications as it provides an additional security layer.
Hope this was helpful!
one thing is that you can avoid Sql Injection Attacks by using Stored Procedure
Another issues that is over looked, compare the network traffic between the web server and the database server of this-
exec someproc @var1='blah', @var2='blah', @var3='blah'
Select field1, field2, field3, field4, field5, field6....field30 join table1 on table2.field12 = table1.field12 where blah blah blah and table1.field3 = @var1 and table2.field44 = @var2 and (table1.field1 is null or table1.field1 = @var3.......
See the difference? For 99% of us this probably won't matter, but for some of you writing high performance apps this might, though there are probably some caching or other ways of dealing with this.
I think a lot of people who claim there is no difference between adhoc queries and stored procedures are generally using tables as object stores for whatever ORM they are using, and that is fine. There are still a lot of heavy data driven enterprise apps, rightly or wrongly, that have 1000+ line stored procedures. You may end up working on them. Also, for those of you who may have to make changes in production every once in a while and need to bypass the formal process it is a lot easier to do that in the database than in production compiled code. Alter proc...done. Cowboy, horrible, evil, happens. Yes I know doing this is an unforgivable sin in many of your minds, sign of shear slop...but it happens. Just something to think about too.
I know the latest tools generally make using stored procs a pain in the rear if you expect all your entities to be generated nicely for you, but stored procs still have their place sometimes.
- Stored procedures sometimes run a little bit faster because or using RPC calls when possible
- SP runs faster for queries that have to be recompiled - for ex. - using temp tables creations somewhere in the middle