Interested to Buy Any Domain ? << Click Here >> for more details...
What are the benefits of using stored procedures over sql
statements?
- 3 Answers
- 8341 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 connect php with different port numbers?
when would you go for denormalization? : Sql server database administration
what are the different types of SSRS reports?
What is the difference between ROW_NUMBER and Ranking function in SQL SERVER?
What is forward - only cursors / read only cursor?
How do you create a clustered index?
How to identify current user in ssrs report?
Which autogrowth database setting is good?
How do I make a resultset scrollable?
How can you transfer data from a text file to a database table? Or how can you export data from a table to a comma delimited (csv) file? Or how can you import data from ms access to a table in a database? Or how can you export data from a table to an excel file?
When is update_statistics command used?
How to add a new column to an existing table with "alter table ... Add" in ms sql server?
What are the different types of sql server replication? : sql server replication
What is key attribute?
What is nested transaction?
