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?

Answer 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.

Answer 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

Answer 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.