What are the benefits of using stored procedures over sql statements?

What are the benefits of using stored procedures over sql
statements?

Question Posted / brijen.patel

3 Answers
8156 Views
Ness Technologies, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What are the benefits of using stored procedures over sql statements? ..

Answer / 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

What are the benefits of using stored procedures over sql statements? ..

Answer / yudistara

reducing network traffic..
user friendlyness..
no need to write queries every time..
sp help to improve perfrmance

Is This Answer Correct ?

5 Yes

1 No

What are the benefits of using stored procedures over sql statements? ..

Answer / vishnu

reduces network traffic
boost up the performance
eliminate webserver overhead
it takes input parameters
if we modify it, clients 'll get newer version automatically

Is This Answer Correct ?

4 Yes

1 No

Post New Answer