A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.
Stored Procedure have a securable unit of code. When the stored proc is executed for the first time then the query plan will be put into the proc cache but then so will any query so the performance benefits of procs are largely mitigated. On 32 bit systems (if there are any left) the proc cache is limited to 2GB. On 64 bit systems the proc cache can grow to the point where it threatens the buffer cache which is where you want data to reside. Query plans will age out of the proc cache based on how complex they are and how often they are used.
Advantages of stored procedure are:
- They give visibility of application code to the DBAs.
- They can encapsulate more complex data manipulation logic and a transactional unit of work that would be awkward to do in the application and involve round-tripping.
- They can be used to define a precise interface between the application and the database. This means that someone wanting to steal data may have to do so through defined interfaces rather than SELECT * FROM Customer. This in turn means that any rate alarms on your system are more likely to be tripped in time for you to do something about it.
- It makes a profile trace easier to read.
- By providing an abstraction it means that the DBA can work in an agile fashion refactoring the underlying db schema while maintaining the same interface from the applications. Hence the comment about not needing to recompile the app for a DB change.
A stored procedure is also re-usable(for repeated tasks).So it can be used/called in more than one application. Maintaining a stored procedure is easier as there is only one place where changes are made.