What are the benefits of using stored procedures over sql
statements?
- 3 Answers
- 7086 Views
- Ness Technologies, I also Faced
- E-Mail Answers
Answer Posted / brijen.patel
Applications that use stored procedures have the following
advantages:
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.
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.
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.
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.
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.
| Is This Answer Correct ? | 6 Yes | 1 No |
Post New Answer View All Answers
How to delete all rows with truncate table statement in ms sql server?
Can group by and orderby be used together?
What are “unrepeatable reads”?
How to provide values to user defined function parameters?
What is the difference between sdf and mdf?
What are the five major components of a dbms?
What is Federation and Federation Member?
How to disable a login name in ms sql server?
how to determine the service pack currently installed on sql server? : Sql server database administration
What do you know about normalization and de- normalization?
When multiple after triggers are attached to sql table, how to control the order of execution?
How to turn on the mssql api module in php?
What is a mixed extent?
System variable and temporary variables
You want to use a perspective in an mdx query. How do you select the perspective?
