Comparison With Dynamic SQL
Overhead: Because stored procedure statements are stored directly in the database, they may remove all or part of the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. (However, most database systems implement "statement caches" and other mechanisms to avoid repetitive compilation of dynamic SQL statements.) In addition, while they avoid some overhead, pre-compiled SQL statements add to the complexity of creating an optimal execution plan because not all arguments of the SQL statement are supplied at compile time. Depending on the specific database implementation and configuration, mixed performance results will be seen from stored procedures versus generic queries or user defined functions.
Avoidance of network traffic: A major advantage with stored procedures is that they can run directly within the database engine. In a production system, this typically means that the procedures run entirely on a specialized database server, which has direct access to the data being accessed. The benefit here is that network communication costs can be avoided completely. This becomes particularly important for complex series of SQL statements.
Encapsulation of business logic: Stored procedures allow programmers to embed business logic as an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. This can result in a lesser likelihood of data corruption by faulty client programs. The database system can ensure data integrity and consistency with the help of stored procedures.
Delegation of access-rights: In many systems, stored procedures can be granted access rights to the database that users who execute those procedures do not directly have.
Some protection from SQL injection attacks: Stored procedures can be used to protect against injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. Also, some DBMSs will check the parameter's type. A stored procedure that in turn generates dynamic SQL using the input is however still vulnerable to SQL injections unless proper precautions are taken.
Read more about this topic: Stored Procedure
Famous quotes containing the words comparison with, comparison and/or dynamic:
“In everyone’s youthful dreams, philosophy is still vaguely but inseparably, and with singular truth, associated with the East, nor do after years discover its local habitation in the Western world. In comparison with the philosophers of the East, we may say that modern Europe has yet given birth to none.”
—Henry David Thoreau (1817–1862)
“He was a superior man. He did not value his bodily life in comparison with ideal things. He did not recognize unjust human laws, but resisted them as he was bid. For once we are lifted out of the trivialness and dust of politics into the region of truth and manhood.”
—Henry David Thoreau (1817–1862)
“We Americans have the chance to become someday a nation in which all radical stocks and classes can exist in their own selfhoods, but meet on a basis of respect and equality and live together, socially, economically, and politically. We can become a dynamic equilibrium, a harmony of many different elements, in which the whole will be greater than all its parts and greater than any society the world has seen before. It can still happen.”
—Shirley Chisholm (b. 1924)