OOP speed penalty with mysql?

[mbeals]

My database is very hierarchical in nature, which lends itself well to OOP classes.  I have a ton of code originally written in functional logic that I'm porting over and it hit me that I'm deconstructing a lot of my more complicated queries into several smaller ones.  Is this bad form?  Is this going to impact server performance?

 

For example, I have a site that tracks IP assignments.  I have a database table that consists of public IP, local IP and a handfull of other info about the address.  Then I have one table for each kind of device that can be associated with an address.  In functional world, I write this nice long query using Unions and joins to build one array containing IP to device relationships.

 

In OOP world, I have an IP class and different classes for each device.  So instead of a single query, I have a method to find all ip addresses and create an IP object for each one.  Then the IP class contains methods to search for each kind of device and create an object for each one of those.  Each one of those searches is a simple query.  So now i have one simple query for every device type (3) per every ip (a whole lot).  It doesn't take long before I'm executing a very large number of individual queries as opposed to my single one in functional programming.

[448191]

This has nothing to do MySQL really.

 

Multiple small queries are generally slower than a larger query that does the same job, but I think you already know that.

 

Whenever you're going to map OO to relational, performance loss will be inevitable. Basically your tradeoff will be between development speed/ease and performance with most of what is OO.

 

But, there are ways to reduce the cost of OR Mapping. Look into stuff like Lazy Initialization, Virtual Collections, Ghosts, and Virtual Proxies.

 

Good luck, this is a very extensive subject.

[JonnoTheDev]

Why not use a factory pattern to create your objects with all data you require.

 

So if you have an IP class, a device class etc:

 

 

class IPAssignmentFactory {

 

public static function generate($ipAddress) {

  // database query (WHERE ip=$ipaddress)

  $query =

  $result = mysql_fetch_array($query)

  return array('ip' => new IP($ipaddress), 'device' => new IPDevice($result['field1'], $result['field2']));

 

}

}

 

$ipInfo = IPAssignmentFactory('10.0.0.1');

$ipInfo['ip']->classMethodInIPClass();

 

// etc

[JonnoTheDev]

Sorry bad code above. Better

 

class IPFactory {

    public static function generate($ipAddress) {
        // database query (WHERE ip=$ipAddress)
        $result = mysql_query("SELECT ");
        $row = mysql_fetch_array($result);
        return array('ip' => new IP($ipAddress), 'device' => new IPDevice($row['field1'], $row['field2'])); 
    }
}

// usage
$ipInfo = IPFactory::generate('10.0.0.1');
$ipInfo['ip']->classMethodInIPClass();
$ipInfo['device']->classMethodInIPDeviceClass();

[deadonarrival]

There is a speed penalty, but on each single page load it's not a huge difference. You have the added overhead of extra queries, but it's offset to an extent by the fact that each query is simpler and faster.

 

One worry is the extra traffic between the http and mysql servers - if you're not using persistant connections, now might be the time to start!

 

I'd suggest that you aren't doing too much harm by splitting the queries up - it's like the people who test 5million prints against 5million echos - even if one is faster, nobody really cares that much. Your users won't notice the thousandths of a second.

 

Think of it this way, one page load might be significantly slower than another just due to their connection and increased traffic. Their connection will make far more fluctuation than your switching methods.

[JonnoTheDev]

You dont have to do this with many queries. The way you are generating your objects forces you to do it the way you describe!

 

Use the same SQL as you mention in the initial post with your unions and joins. This as you say returns all the data you require in 1 go rather than using sub queries in a loop from an initial query. You can generate the objects you require from the data returned by the query using similar code to my post above.