Does use of Stored Procedures increase performance?

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

Three things happen when a SQL command is executed:

• The SQL syntax is checked for any errors.
• The best plan is selected to execute the SQL (choice to use clustered index, non-clustered etc.).
• Finally the SQL is executed.

When a stored procedure for the first time it will go through all the above steps and the plan will be cached in-memory. So the next time when the stored procedure is executed it just takes the plan from the cache and executes the same. This increases performance as the first two steps are completely eliminated.
It implies that for inline queries all the above steps are repeated again and again which brings down the performance considerably.

The above explanation was pretty valid and true for older versions of SQL Server, but from 2005 onwards, all SQL statements, irrespective of it’s a SQL coming from inline code or stored procedure or from anywhere else, they are compiled and cached.

Detailed blog from CodeProject.com at http://bit.ly/PhofYm