Much Ado About Stored Procedures

Drizzle is against stored procedures (SP):

Drizzle does not currently have any plugins that implement stored procedures. We viewed the implementation in MySQL to be non-optimal. They bloat the parser and only support one language (SQL2003 stored procedures), which was not well known.

Fundamentally, stored procedures usually are not the correct architectural decision for applications that need to scale. Pushing more computation down into the database (which is the trickiest layer to scale) isn’t a good idea.

We do recognize the value of using stored procedures to reduce the time row locks are held, but think we can achieve the same advantage by improved batching of commands over the wire. This removes adding and administering stored procedures from the list of things that can go wrong in administering the database.

VoltDB, on the other hand, is not only in favor of SP but requires it!

VoltDB’s choice to use stored procedures as transactions is motivated by three observations.

  • Round tripping unnecessary data (a large JSON document or unnecessary table columns) across the network to a client quickly becomes a bottleneck and an expensive one if you’re paying for bandwidth. VoltDB stored procedures allow full, transactional manipulation of stored data, eliminating unnecessary data transfer between application and database.
  • Application complexity is reduced when the data store provides stronger atomicity and isolation guarantees.
  • Multiple round-trips to the data store impose a latency cost that many applications can not afford. Even if applications are structured to tolerate inconsistent, non-atomic updates, latency budgets require minimizing application to database round trips.

Moving data processing closer to the data, into the storage engine satisfies these observations. That requires a rich data query language and the ability to batch multiple statements together. This is exactly the functionality that VoltDB’s stored procedure interface provides.

Moreover, VoltDB adds:

Defining the database schema — and particularly the partitioning plan — goes hand in hand with understanding how the data is accessed. The two must be coordinated to ensure optimum performance. It doesn’t matter whether you design the partitioning first or the data access first, as long as in the end they work together. However, for the sake of example, we will use the schema and partitioning outlined in the preceding sections when discussing how to design the data access.

Writing VoltDB Stored Procedures

The key to designing the data access for VoltDB applications is that all access to the database must be done through stored procedures. It is possible to perform ad hoc queries on a VoltDB database (as described later) and they can be useful for debugging an application during development. However, ad hoc queries circumvent all of the performance optimizations VoltDB is designed for and therefore should not be used for production code.

In VoltDB, a stored procedure and a transaction are one and the same. The stored procedure succeeds or rolls back as a whole. Also, because the transaction is defined in advance as a stored procedure, there is no need for specific BEGIN TRANSACTION or END TRANSACTION commands.

Within the stored procedure, you access the database using standard SQL syntax, with statements such as SELECT, UPDATE, INSERT, and DELETE. You can also include your own code within the stored procedure to perform calculations on the returned values, to evaluate and execute conditional statements, or to perform any other functions your applications need.

The stored procedures themselves are written as Java classes, each procedure being a separate class.

It is worthwhile to note that VoltDB was designed for certain use cases only, not as a general-purpose RDBMS. As such, it caters to applications who want every ounce of speed in performance while providing ACID and scalability.

Chad Hower aka Kudzu

The following network topology is a major reason against stored procedures. The complexity of business logic is better suited at the business layer while relegating the schema changes and the data itself  to the database layer.

Such a design has the following advantages:

  • All the business logic exists in a single location and can be easily verified, debugged, and modified.
  • A true development language can be used to implement business rules. Such a language is both more flexible and more suited to such business rules rather than the SQL and stored procedures.
  • The database becomes a storage layer and can focus on efficiently retrieving and storing data without any constraints related to business logic implementations or the presentation layer.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s