Stored Procedures: Good or Bad?

December 15, 2008 – 22:06

A project team rewriting one of our web products has made the decision to jump ship from SQL Server to the open-source MySQL database and, at the same time, to place a greater emphasis on the use of stored procedures. Since that decision, there have been various other projects where the choice of moving some business logic to the database server has had to be contemplated.

My initial reaction to the use of stored procedures was hesitant.  I work in a small company with no dedicated database programmers and a relatively small team of PHP experts. Due to this balance alone, I consider a greater reliance of stored procedures to be a risk.  But there is one very good reason to think the move may not be so risky after all: it takes load off the network - which might also result in a net performance gain.

But my initial wariness still stands - and continues for all the projects for which I’ve considered stored procedures recently.  The following disadvantages overwhelm that performance gain:

  • Language: the extended SQL language used in stored procedures is relatively immature and doesn’t lend itself to certain complex logic.
  • Tools: development tools for MySQL stored procedures (including debugging aids) are very limited.
  • Server load: increased processing on the database server is more difficult to accommodate (database servers are more difficult to scale than Apache/PHP web servers, for example.)
  • Deployment headaches: it can be hard enough to keep database and application code in sync without causing even a blip of downtime.  Introducing stored procedures introduces another element which needs careful management.  It’s not rocket science, but it’s just more to think about - and more to go wrong.
  • Portability is limited (DB2 has a similar language to that used by MySQL, but otherwise, you’re pretty much stuck if you want to move to another database platform.)  That said, it’s not exactly an everyday occurrence, is it?
  • Performance(!): the performance gain you may expect from stored procedures might often be a myth anyway. Stored procedure code executes slowly when compared with many application programming languages - so the benefits of reduced network traffic might soon be eaten into.

So, for the time being, while we have the skills to focus development of business logic in PHP, that’s just what we’ll do.


Posted in Geek, Technologies | 3 Comments »

Hailing Ubuntu 8.10 Intrepid Ibex (and the Eee PC 701)

October 20, 2008 – 14:08

(Updated: new steps needed for wireless on Eee PC)

Each release of Ubuntu just gets more and more, well, grown-up.  When I started using Ubuntu on the desktop, it was a brave man who would upgrade when a new release became available rather than install from scratch (and keep your fingers crossed with drivers.)  But things have improved significantly - even on hardware which is new and typically less well supported (see my Eee PC 701 setup guide later in this article.)

Things I like about Intrepid over previous versions: (more…)


Posted in Geek, Ubuntu Linux | 1 Comment »

Integrating Subversion (SVN) with Bugzilla

April 2, 2008 – 21:31

It’s well documented why using a source code control system is a worthwhile investment. Change tracking, team working, a safety-net, release management, control and reporting. And Subversion, a.k.a. svn, is the leading light in the open source world, taking over the mantle from CVS.

Subversion is different. It takes a different approach to any other source code control system I know. It uses lightweight ‘copy’ operations to achieve tagging and branching. It’s intuitive. If you didn’t have source code control and you had a code base that you wanted to keep as “version 1″, you’d most likely take a copy of the directory and call the new directory “version 1″. Well that’s tagging in svn.

But, like I say, this is all well-documented. (more…)


Posted in Configuration Management, Geek | 4 Comments »

Sync Google Calendar with iPod on Ubuntu Linux

October 6, 2007 – 10:45

Create a script to fetch the Google Calendar and copy it to your iPod:

# download the Google calendar ical file through the private URL
wget -c -N http://www.google.com/calendar/ical/yourname%40gmail.com/public/basic.ics

# copy it to the iPod
cp basic.ics /media/your-ipod-name/Calendars/google.ics
rm basic.ics

Now configure Ubuntu so that this script is run whenever you plug your iPod in. Add the following line to /etc/udev/rules.d/80-programs.rules:

KERNEL=="sdc1", RUN+="/path/to/your/script.sh"

(Replace sdc1 with the appropriate device name, which you can find out by typing “df”.)


Posted in Geek, Ubuntu Linux | 2 Comments »

©2008 xenzero.com