Performance Query

[prev] [thread] [next] [Date index for 2004/09/06]

Tony Bowden wrote:

>> On Fri, Aug 27, 2004 at 10:52:07AM -0500, Michael Jensen wrote:
>
>>>>Users
>>>>	id
>>>>	username
>>>>	email
>>>>	group_id
>>>>Groups
>>>>	id
>>>>	name
>
>> User->has_a(group_id => 'Group');
>> 
>> print $user->group_id->name;


From the above example, I have a question regarding performance (essentially the number of hits  on the database). Class::DBI has it's db connection and will not close it for this query, but I am unclear about just how many queries are generated from this statement.

Say we had a couple more tables.
User
    id
    username
    group_id
    type_id
    access_id

Group
    id
    name

Type
    id
    name

Access
    id
    name

User->has_a(group_id  => 'Group');
User->has_a(type_id   => 'Type');
User->has_a(access_id => 'Access');

I want to display the name, group name, type name and access name for a 
list of users. Assuming that $user is an interative Class::DBI result 
object:

while(my $u = $user->next){
    my $displayString = $u->username;
    $displayString .= '|'.$u->group_id->name;
    $displayString .= '|'.$u->type_id->name;
    $displayString .= '|'.$u->access_id->name;
    print "$displayString\n";
}

Is this creating 3 hits (queries) for every User? So from a list of 100 
users there will 400 queries on the database? If this is the case is 
there a way to optimise it?

If it is as bad as it potentially looks, then I will have to write some 
query-only business classes with the SQL hardcoded (to a lesser or 
greater extent). If this is the case, then are there any pitfalls to 
look out for? I would ideally like to use the Class::DBI dbh for the 
call, which I am assuming will not be a problem?

Many thanks!
Tom