Optimization question - loading an object hierarchy

Troutnut

I'm writing an object oriented forum, and I've got a topic class and a post class, corresponding to a topic table and a post table in my MySQL database.

I'm trying to decide between two alternatives to construct the post objects in my PHP app. They are:

-Have the topic object do one little query for post IDs (SELECT postid FROM posttable WHERE topicid=xx), and call the post constructor for each ID. Then the post constructor queries the database once for each post (SELECT * FROM posttable WHERE postid=xx).

-Have the topic object do one big query, (SELECT * FROM posttable WHERE topicid=xx), call the post constructor without an ID to create a blank object, and have a setProperties() method of the post object, called by the topic object after the constructor to give the post the rest of its data.

The first way of doing things seems much more elegant and simple, but it seems like the latter would be much more efficient. But I don't really know. I face a similar problem to this with some other object hierarchies on my site, so any thoughts on optimization will be very much appreciated!

sneakyimp

if you are concerned about server performance, the latter approach will be much more efficient. there is time overhead associated with every query you run. given that you would be gathering the exact same information, one query is going to be MUCH more efficient for that reason alone.

secondly because you'll be searching a table with a potentially very large number of records, it would be MUCH more efficient to search it only once and grab all the relevant records in one fell swoop rather than scanning thousands of records several times--each time for just a single record.

thirdly, i bet the sheer number of instructions you execute would be greater in the first approach. perhaps not by much though. i could be wrong about this.

all that said, the advantage of the first approach is CODE EFFICIENCY. It would be much easier to maintain the first approach because there's much less code overall. If your database structure regarding postings were to change, you'd only have to change the code in the posting classes.

Troutnut

Thanks.

I've decided on the second approach. I figured out a way to do it that still makes my object construction sort of intuitive. Instead of constructing a null object and filling its values with a method, I construct the object by ID with optional parameters for everything else. If the optional parameters aren't given, it pulls them by ID from the database, and if they are it doesn't.