Databases Reference
In-Depth Information
• It runs where the data is, so you can save bandwidth and reduce latency by running
tasks on the database server.
• It's a form of code reuse. It can help centralize business rules, which can enforce
consistent behavior and provide more safety and peace of mind.
• It can ease release policies and maintenance.
• It can provide some security advantages and a way to control privileges more finely.
A common example is a stored procedure for funds transfer at a bank: the proce-
dure transfers the money within a transaction and logs the entire operation for
auditing. You can let applications call the stored procedure without granting access
to the underlying tables.
• The server caches stored procedure execution plans, which lowers the overhead of
repeated calls.
• Because it's stored in the server and can be deployed, backed up, and maintained
with the server, stored code is well suited for maintenance jobs. It doesn't have any
external dependencies, such as Perl libraries or other software that you might not
want to place on the server.
• It enables division of labor between application programmers and database pro-
grammers. It can be preferable for a database expert to write the stored procedures,
as not every application programmer is good at writing efficient SQL queries.
Disadvantages include the following:
• MySQL doesn't provide good developing and debugging tools, so it's harder to
write stored code in MySQL than it is in some other database servers.
• The language is slow and primitive compared to application languages. The num-
ber of functions you can use is limited, and it's hard to do complex string manip-
ulations and write intricate logic.
• Stored code can actually add complexity to deploying your application. Instead of
just application code and database schema changes, you'll need to deploy code
that's stored inside the server, too.
• Because stored routines are stored with the database, they can create a security
vulnerability. Having nonstandard cryptographic functions inside a stored routine,
for example, will not protect your data if the database is compromised. If the cryp-
tographic function were in the code, the attacker would have to compromise both
the code and the database.
• Storing routines moves the load to the database server, which is typically harder
to scale and more expensive than application or web servers.
• MySQL doesn't give you much control over the resources stored code can allocate,
so a mistake can bring down the server.
• MySQL's implementation of stored code is pretty limited—execution plan caches
are per-connection, cursors are materialized as temporary tables, there's very
