16 August 2012

I find the option of stored procedures to be very appealing for a variety of purposes. Among other things, I've seen plenty of extremely complex SQL queries that only change by one parameter, which means that they could be wrapped up into a call that would require significantly less PHP markup and significantly less network traffic between the web and database servers. Add multi-query procedures, and the procedures would be even better.

In the end, I decided not to pursue stored procedures right now because:

  1. There is no Drupal-centric way to create or manage them
  2. A stored procedure would be unable to leverage the Drupal hook techniques as intended, especially db_rewrite_sql
  3. Beyond network bandwidth savings, MySQL procedures may not have the performance boost that might be expected due to the scope of availability of compiled functions
  4. MySQL read replicas (and therefore RDS read replicas) are unreliable with most non-deterministic functions
  5. On top of all that bad news, it was going to take tweaking of permissions to even get to where I could test

Although procedures might be an amazingly effective solution, the research was all so pessimistic that I've decided not to commence with testing at this point. It will remain at the front of my mind -- hopefully a use case will justify further research, development and testing.

References

  1. Stored procedure within Drupal or here
  2. Stored procedures permissions (and more) on RDS
  3. MySQL performance problems
  4. Debate over stored procedures, with excellent points on both sides
  5. Do not use non-deterministic functions
  6. How MySQL uses deterministic functions


blog comments powered by Disqus