Re: Re: Performance Query

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

> <i>Check out the Lazy Population section of the docs to see if that
explains your question.</i>

Unfortunately I do not see how this answers my question.
I understand that for the 1 object there are methods that split retrieving of the fields in a row into multiple database queries, but I am particularly concerned about queries that are JOINs, not singgle table queries.

The query from my original question could be written in SQL something like:
<pre>
SELECT USER.id
       USER.name, 
       GROUP.name, 
       TYPE.name, 
       ACCESS.name
  FROM USER, 
       GROUP, 
       TYPE, 
       ACCESS
 WHERE USER.group_id  = GROUP.id
   AND USER.access_id = ACCESS.id
   AND USER.type_id   = TYPE.id
</pre>

Assuming this has been 'modelled' with the use of the "has_a" Class::DBI methods (because that is the best way to do it with Class::DBI?) on the User class - how many hits are there actually to the database?

My unerstanding is that it will be split into something like this (pesudocode):
<pre>
while(SELECT id, name, group_id, type_id, access_id FROM USER){
   SELECT name FROM GROUP  g WHERE g.id=group_id
   SELECT name FROM TYPE   t WHERE t.id=type_id
   SELECT name FROM ACCESS a WHERE a.id=access_id
}
</pre>

The first SQL is 1 query with 1 overhead retriving X rows and the 2nd loop is 1+X*3 queries retrieving X+1 rows. My worries are that if I start retrieve a few thousand rows, instead of 1 query, I have 3000+ queries to the database. Make this a web site with a few concurrent visitors and suddenly I am hitting the database with 10,000 queries? To me that is a performance issue.

However, I am not claiming to understand everything, and am seeking a little help/clarification here :)