Login

Drupal 7 Database Abstraction Layer

Stored Procedures and Drupal 7

This article is for PHP programmers and those folks who are comfortable working directly with relational databases.

Stored procedures are not a general purpose solution. However, as Drupal is adopted by wider audiences, you may find your self in a situation where a dedicated solution is required (e.g. making calls to stored procedures).

For example, a customer wants Drupal to report the result of a tried and true scientific calculation. The calculation resides in a stored procedure. Drupal may be new to the customer, but the customer's core business processes are well established (i.e. the stored procedure is established).

This article provides a simple proof-of-concept example to get you started. This article does not represent a comprehensive study of Drupal 7 and stored procedures. To summarize, I found this to be an interesting topic.

Using a stored procedure, record selection was 14 times faster (versus a prepared statement via db_select).

Background:

The main advantage of stored procedures is performance.

The main disadvantage is, store procedures are usually vendor specific. Often a stored procedure written for one database (e.g. MySql) can not be run in another database (e.g. Postgres).

A database administrator (DBA) often writes the stored procedure. An application programmer writes code that calls the stored procedure.

The term stored procedure refers to "application logic" stored directly in the database (as opposed to application logic stored in PHP files). Application logic is thus run (and stored) within the database, reduces network traffic.

Case Study Details.

We are going to write 2 simple functions. We'll perform some crude timings and look at the results.

Both methods simply select "titles" from all nodes of a specific content type and publishing state. Not a real world query, but enough to demo performance difference. I am using MySql version 5.5.9 running under Arch Linux 2.6.37. I am using the current Drupal 7.0 release. The database server, web server and Drupal installation all resided on the same host (my laptop).

The first method uses the Drupal API method db_select(), the second method calls a stored procedure. Other than that, both methods are the same. Both methods take the database result, capture a timing value and display everything using theme_table().

Timing results (measured in seconds):

db_select() call stored procedure Difference Magnitude x Faster (stored procedure)
0.0125620365 0.000674963 0.0118870735 18.61
0.0096518993 0.0007021427 0.0089497566 13.75
0.0115540028 0.0007169247 0.0108370781 16.12
0.0083408356 0.0006780624 0.0076627731 12.30
0.0120971203 0.000742197 0.0113549232 16.30
0.0085980892 0.0006630421 0.0079350471 12.97
0.0078208447 0.0006899834 0.0071308613 11.33
0.0103640556 0.0006439686 0.0097200871 16.09
0.0084490776 0.0007648468 0.0076842308 11.05
0.0082781315 0.0006940365 0.007584095 11.93
Total      
0.0977160931 0.0069701672 0.0907459259 14.02
Syndicate content