“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
