StructureCMS

May 12, 2010

Good, Fast, or Cheap. Choose Two.

Filed under: Musings, Programming — joel.cass @ 4:53 pm

How true is the above statement, really? With the rise of open source products one would think that it’s possible to find a product that ticks all of the above products, and is free to boot.

I found this out recently when I tried using SQL server to load in some website log data so I could generate some reports. Geez, it was slow. It ran at about 45 records per second. Inserting about 64 million records could take a loooooong time. On the other hand, I remembered the faithful MySQL server that I had used a while ago. Loading data into MySQL was fast – about 600 records/sec fast. And it’s free. But is it good?

When actually getting around to running the reports, I was finding that MySQL was falling short. Due to its architecture, sorting operations had to be done by writing a temporary table to disk. This could be worked around by using indexes, however often the indexes would not be picked up, plus the reconfiguration of an index over 64 million records can take 2-3 hours, thus slowing things down.

So either way it was a headache. I know that SQL server is very efficient at sorting and searching records and has its own optimised low-level methods for searching data. If you’ve ever tried to run a database from a compressed drive / folder you would know that certain operations do not work because SQL server accesses data at such a low level. And I think it’s that sort of optimisation that you would happily pay for. Whilst it may be slow on the insert side, it’s fast on the searching side.

So, “Good, Fast, or Cheap. Choose Two.” – I think the saying has real meaning here. An open source product may have the commitment of a small group of developers or no commitment at all. A paid product has a real business motive to keep developers “on the ball”, continually optimising and improving the product.

1 Comment »

  1. For the last few (3?) years MySQL has been owned first by Sun and now by Oracle. During this time it has had a full time professional development team. Even going past that MySQL AB was a company with full time dev staff working on the product.

    I’m sure the resources it gets don’t match SQL server and sure a lot of the development is geared towards enterprise features like replication…

    Sometimes SQL just sucks and when you get up to around 64 mil records you’re probably approaching it’s useful limits.

    You might be interested in Tim Bray’s Wide Finder & Wide Finder2 projects – http://www.tbray.org/ongoing/When/200x/2008/05/01/Wide-Finder-2

    Comment by Mark — May 13, 2010 @ 7:06 am

RSS feed for comments on this post. TrackBack URL

Leave a comment