Software Development

Using MySQL stored procedures

A lot of my past software development background has been around client/server in financial markets, with the main RDBMS use in Sybase, but also MS SQL Server and Oracle. In most cases stored procedures is the way to query and manipulate the database. There are a number of reasons for this including security, performance and centralised business logic out of the client.
Now in recent years I have made the move to web development in PHP, and in most cases using MySQL as the database server. From personal experience of others code, and available FOSS systems, it is extremely rare to see use of MySQL stored procedures behind web sites. They are there, have been for a number of years, and do work. So why is this so?
The first reason I could understand, was that they are a MySQL 5 feature, and most FOSS apps set their MySQL minimum requirements at version 4.x.
I now understand a 2nd and crucial reason, thanks to a post by Jay Pipes. MySQL stored procedures are not the same as Sybase or MS SQL. The shared compilation performance benefit is not there in MySQL 5.0/1 for non persistent or pooled connections. ie. most smaller to medium web sites. Their usage may actually be to the detriment of performance.
So stored procedures can have a use in MySQL, but mostly in limited use cases.

Standard
Software Development

PHP News #5

PHP news to August 6th, 2010.

  • New PHP drivers for for SQL Server released, under an Apache 2.0 license.
  • Zend framework 1.10.7 released. Bug fixes including an update for Zend_Service_Twitter, as Twitter moves to OAuth.
  • PHPNW conference on October 9 & 10, 2010 in Manchester, UK.
  • New PHP Excel extension from Ilia Alshanetsky.
  • Call for proposals at OSDC (Open Source Developers Conference) 2010 to be held in Melbourne, Australia from November 24th to 26th, 2010.
Standard