Why should we use Store Procedure instead of Ad hoc queries
Stored procedure is a set of Structured Query Language statements with an assigned name which are stored with in the database in compiled form so that it can be used by a number of programs.
Ad hoc queries are normally written on application side and are meant to be used for once only and are never saved to run again.
At the beginning developers who are not good at database side, like to use ad hoc queries for fetching and to make changes in required data. These ad hoc queries can kill performance and some time it is hard to control complex logics through these ad hoc queries. Store procedures are the best choice to accomplish these data processes. These are helpful in following regards.
- Reduce Network Traffic
Excessive network traffic is a big performance killer. Frequent trips to database server from client application (because of ad hoc queries) may be a cause of this excessive network traffic. Store Procedures helps you to reduce such network traffic by holding group of statements and returning required result with a single call.
Avoid lengthy transactions in store procedures to prevent lock contention problems.
- Database Privileges
Users can be restricted from having access to read/write to tables directly in database by using store procedures. Only developer of store procedure require specific privileges, while creating a store procedure but to execute these store procedures client of application only need execute privileges.
- Code Security
Sql Injections, which uses AND or Or to append commands on to a valid input parameter can be defended by using store procedures, but If you still have a string in your application with the store procedure name and concatenated parameters from user input to that string in your code, you are still on risk.
- Execution Plan Re-use
Store procedures are compiled once and resultant execution plan are utilized for future executions. This results in tremendous performance boosts when store procedures are called repeatedly.
- Efficient Re-use of Code
Commonly used store procedures can be effectively used for different projects.
For example, create a store procedure which returns amount in words against integer input. (INPUT= 1542214, OUTPUT= 1.5 Million, Forty Two Thousand, Two Hundred and Fourteen). Store procedure like this, can be used in any application.
- Single Point of Maintenance
Change in business rules defined for a project, over a time is normal. If such business rules are controlled with in store procedures rather then application, it is easy to make changes in database and NO need to recompile your application code.