Why Stored Procedure is faster than Query

Question

I want to write a simple single line query to select only one value from database.

So if I write stored procedures for this query rather than writing simple select query in c# code, then I am sure that stored procedure for this simple select query will be faster but why?

I am confused with stored procedure vs writing simple query in my code? I am confused that why stored procedure are faster than simple one query written directly in code?

Solution 1

Stored Procedures Are Faster Than SQL Code

This is a myth, the performance is always equivalent, from the book: Architecting Microsoft® .NET Solutions for the Enterprise:

SQL is a language through which you declare your intentions about the operations (query, update, or management operations) to execute on the database. All that the database engine gets is text. Much like a C# source file processed by a compiler, the SQL source code must be compiled in some way to produce a sequence of lower-level database operations—this output goes under the name of execution plan. Conceptually, the generation of the execution plan can be seen as the database counterpart of compiling a program.

The alleged gain in performance that stored procedures guarantee over plain SQL code lies in the reuse of the execution plan. In other words, the first time you execute an SP, the DBMS generates the execution plan and then executes the code. The next time it will just reuse the previously generated plan, thus executing the command faster. All SQL commands need an execution plan.

The (false) myth is that a DBMS reuses the execution plan only for stored procedures. As far as SQL Server and Oracle DBMS are concerned, the benefit of reusing execution plans applies to any SQL statements. Quoting from the SQL Server 2005 online documentation:

When any SQL statement is executed in SQL Server 2005, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server 2005 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server 2005 generates a new execution plan for the query.

The debate around SPs performing better than plain SQL code is pointless. Performance wise, any SQL code that hits the database is treated the same way. Performance is equivalent once compiled. Period.

Solution 2

"Stored procedures are precompiled and cached so the performance is much better."

This was heart breaking for me as it would be for you when you come to know that this was true until SQL Server 2005.This article shatters the myth Stored Procedures DO NOT increase performance

Christa Carpentiere from Microsoft Corp. wrote An Evaluation of Stored Procedures for the .NET Developer

Solution 3

This depends on the query, for simple queries it is best written and executed as a query itself. However when you have more processing to do on the database side (you want to take the data in a cursor manipulate it and so on) , stored procedures are better as they execute on the database server and avoid unnecessary overheads such as parsing and extra communication.

Comments

[Ammar Raja]

@Cuong Le I searched out from google and found that stored procedure are fast than even simple query but did not get clear idea that why ??

[Ammar Raja]

@Coung Le so if both are equal in performance then it is clear that we use stored procedure just for security reasons ????

[cuongle]

What do you mean "security reason", for SQL injection?

[Ammar Raja]

@CuongLe mean to ask we use stored procedure to secure our application from hacking ???

[SchmitzIT]

@AmmarRaja - It can be for preventing sql injection as you suggested, or simply putting the bulk of the business logic in a central location (the procs) rather than having lots of queries sitting in the application code.

[cuongle]

This is the second myth, security is cross- cutting concern, it should be from presentation layer down to other layer. Does not mean, use SQL is easy to hack

[Ammar Raja]

@CuongLe Thanks.... you opened my mind now and me on right direction :)

[Ammar Raja]

mean to say that stored procedure will be not good for simple single query ?? i should write single simple query without writing it in stored procedure???

[Muhammad Azeem]

thats why Stored Procedures are Faster than Ad-Hoc Queries

[muhammad kashif]

You hit my mind. need to study it again from your point of view too. Thanks for a different point of view .

[Ammar Raja]

@muhammad kashif thank :) i read out your link and got my mind in clear direction.

[Muhammad Azeem]

Stored procedures are great for speeding up certain DB operations...However, while ‘CREATing’ a Stored procedure, having a SET NOEXEC ON will help in pre-compiling the SP (SQL statements)..:)

[Muhammad Azeem]

Stored procedures are great for speeding up certain DB operations

[muhammad kashif]

I found this . searchsqlserver.techtarget.com/news/1052737/…

[cuongle]

@muhammadkashif: this is out date, SP is from 10 years ago, the tendency is to use ORM with dynamically generating SQL in code. SP is hard to maintain, think about how to debug in SQL code, it is really painful

[muhammad kashif]

Yes agree on you with this. I am also moving towards LINQ slow but steady. but the question is all about SPs and plain queries.

[wizgot]

What I mean to say is there are certain conditions in which a static query is better than using a stored procedure. Especially if it is a simple query. Cheers!

[marc_s]

This is just plain not true - see the accepted answer

[marc_s]

This is just plain not true - see the accepted answer

[Matt Johnson-Pint]

One point to add - at least according to these docs, "The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified"

[Aaron]

Expanding on your point on execution plan reuse - this can often slow down execution of stored procs parameters. This behaviour is called "Parameter Sniffing" which is indeed a feature of SQL. As you mentioned, execution plans are saved in order to enhance future execution. However in scenarios where procs are ran multiple times with different parameters, one execution plan might not necessarily be optimal for all possible parameter values. More info on Parameter Sniffing here: link

[MSTdev]

@cuongle nice approch and describe on detailed for precompiled store procedure. But sql also gives you ability to debug your query so you can use tat functionality.

[Jan-Willem Gmelig Meyling]

Allright, so usually plain SQL statements hit the cache and are just as fast as stored procedures. But how does this translate to very complicated queries that are executed infrequently? Are these likely to miss the cache and could they then still benefit from stored procedures? Or were stored procedures never really pre-compiled in the first place, and do these instead utilize the very same cache as regular queries do?