Do stored procedures perform better than SQL queries (or embedded SQL)?

Posted by Vishvvas on 7/29/2011 | Category: Sql Server Interview questions | Views: 7726 | Points: 40

Post |

Interview FAQs |

Exclusive Questions |

Interview Home

Answer:

Its a myth that the stored procedure perform better than SQL queries. Definitely, it was a fact versions before SQL 7.0 where in stored procedures were a way to partially precompile an execution plan in SQL Server version 6.5 and earlier. Such partially compiled execution plan created at the the time the stored procedure was created, were stored in a system table. Please see the article for more details.
http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

Important anecdote from this article is as follows
"SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created."

Source: my onw questions | Asked In: Many Interviews | Alert Moderator

Bookmark It

< Previous : what is immutable and mutable?

Next > : What is the address formats of the WCF transport s ...

Comments or Responses

Posted by: Vuyiswamb on: 9/24/2011 | Points: 10

i donot agree

Posted by: Vuyiswamb on: 9/24/2011 | Points: 10

i donot agree

Posted by: Vishvvas on: 9/24/2011 | Points: 10

Its a fact and it is proclaimed by none other than Mircosoft who has developed this DBMS. Did you visit the URL mentioned? This is a one of the good examples of the suppotions of programming fraternity which is never validated and verified.

Posted by: Vuyiswamb on: 9/24/2011 | Points: 10

i do not agree with this statement

Its a myth that the stored procedure perform better than SQL queries

Did you see this

Execution Plan Recompiles

Performance is affected every time a query results in the creation of a new execution plan or when a plan is recompiled. Recompiles are not always a bad thing. The initial plan that was created may not be optimal for other calls or data may have changed. A recompile might be needed to create a better plan. The optimizer generally causes a recompile when it is necessary; however, there are steps that you can take to ensure that recompilation does not occur when it is not needed. The following guidelines help you avoid frequent recompiles:



Use stored procedures or parameterized queries.

Use sp_executesql for dynamic code.

Avoid interleaving DDL and DML in stored procedures, including the tempdb database DDL.

Avoid cursors over temporary tables.

Use Stored Procedures or Parameterized Queries

and from here
http://msdn.microsoft.com/en-us/library/ff647793.aspx#scalenetchapt14 _topic10

now this tells you something that , if in your application you execute naked statements(Non StoredProcedure), the execution plan will be created for each request and it is a performance problem, but with the storedprocedure the execution plan is stored and it does no need to be created when the SP is called , that is why i say i disagree with that statement

Its a myth that the stored procedure perform better than SQL queries

Thank you for posting at Dotnetfunda

Vuyiswa Maseko

Posted by: Vishvvas on: 9/24/2011 | Points: 10

Good reference. I am just reproducing the some statements from above material
Recompiles are not always a bad thing.
Use stored procedures or parameterized queries
So this clearly implies that parametrized queries are almost comparable in terms of providing performance through query compilation. Which means if inlineSQL are parametrised queries then it offers same benefits as using stored procedures. The first statement says that recompiling is not always a bad thing which leads to conclusion that recompiling always doesn't result in performance hit so even using inlineSQL with no parameterization may not result in bad performance.
In a nutshell, if one uses parametrized queries for inline SQL, the performance would be matching to stored procedure when we consider recompile overhead.

Posted by: Varung on: 12/5/2011 | Points: 10

as stored procdures are pre-compiled and they provide security they perform well than sql queries

Posted by: Vuyiswamb on: 12/5/2011 | Points: 10

In a nutshell, if one uses parametrized queries for inline SQL, the performance would be matching to stored procedure when we consider recompile overhead.

This is not Correct. The Execution plan is a problem for inline SQL. In a Stored Procedure the execution plan does not fired every time a storedprocedure gets fired. All stored Procedures that accept parameters , are parameterised stored procedures. So i am not sure how the execution will be the same.

If you want to test this, try 1 terabyte database not a 2 megs DB.

More Interview Questions by Vishvvas

Latest Interview Questions