6 Advantages of using stored procedures in your application
Applications that use stored procedures have the following advantages:
1. Stored Procedures are Precompiled
Once created, these can be used again and again without compilation.
2. Reduced network usage between clients and servers
A client application passes control to a stored procedure on the database server. The stored procedure performs intermediate processing on the database server, without transmitting unnecessary data across the network. Only the records that are actually required by the client application are transmitted. Using a stored procedure can result in reduced network usage and better overall performance.
Applications that execute SQL statements one at a time typically cross the network twice for each SQL statement. A stored procedure can group SQL statements together, making it necessary to only cross the network twice for each group of SQL statements. The more SQL statements that you group together in a stored procedure, the more you reduce network usage and the time that database locks are held. Reducing network usage and the length of database locks improves overall network performance and reduces lock contention problems.
Applications that process large amounts of SQL-generated data, but present only a subset of the data to the user, can generate excessive network usage because all of the data is returned to the client before final processing. A stored procedure can do the processing on the server, and transmit only the required data to the client, which reduces network usage.
3. Enhanced hardware and software capabilities
Applications that use stored procedures have access to increased memory and disk space on the server computer. These applications also have access to software that is installed only on the database server. You can distribute the executable business logic across machines that have sufficient memory and processors.
4. Improved security
By including database privileges with stored procedures that use static SQL, the database administrator (DBA) can improve security. The DBA or developer who builds the stored procedure must have the database privileges that the stored procedure requires. Users of the client applications that call the stored procedure do not need such privileges. This can reduce the number of users who require privileges.
5. Reduced development cost and increased reliability
In a database application environment, many tasks are repeated. Repeated tasks might include returning a fixed set of data, or performing the same set of multiple requests to a database. By reusing one common procedure, a stored procedure can provide a highly efficient way to address these recurrent situations.
6. Centralized security, administration, and maintenance for common routines
By managing shared logic in one place at the server, you can simplify security, administration, and maintenance . Client applications can call stored procedures that run SQL queries with little or no additional processing.