Databases

Just as threatened in my last post, I'll describe the first component of my upcoming eZeAR framework: The ActiveRecord implementation. Since Alan Knowles' great DB_DataObject package crossed my path a few years ago, I've been using object-relational mapping as much as possible. It makes my code a lot more readable than embedding raw SQL queries whenever I need to get the tinyest bit of data out of a database. It also helps dividing the model (or business logic, if you will) from the controller and view scripts (which I have often intermixed for the sake of simplicity). Frankly, I've never looked back. I still like DB_DataObject a lot, mainly for its simplicity and flexibility. But it has some drawbacks: When working with large recordsets (tens of thousands of records), its caching mechanism ate up so much memory, that it had to be deactivated in batch processing scripts. Also, in said scripts it became apparent that performance wasn't quite what I'd like it to be. In my usual use-cases (intranet applications with master/detail views and a relatively small number of concurrent users) this didn't have much of an impact, but with really huge databases and batch-processing of huge amounts of data, it was a bitch. Just by reimplementing a batch script using pure PDO calls and hand-written SQL as opposed to DataObject I was able to cut down script runtime immensely. The script's job was to iterate over about 18.000 XML files, normalize the flat data structure and insert it into a schema consisting of about 30-40 database tables. The DataObject-based version of the script ran over one hour. The PDO version took about 6-8 minutes (mind that it was an older version of DBDO, so this may have changed in the meantime).