Re: [CDBI] How well does Class::DBI scale?

>>>Let's say we have one hundred rows the refer to foreign key 86

Dave Howorth wrote:

>>>Let's say we have one hundred rows the refer to foreign key 86
>>>in the units table. The units table has a (id,name) pair. I will
>>>see one hundred SQL requests for (id=86,name) from the database!
>>>I find this strange if the corresponding Unit object has already
>>>been _flesh()'d out once.
>>>
>>>Note that in the case of this Unit stuff, the only Essential
>>>column is the primary key. Would this explain the behaviour?
>>
>>Yes, incorrect keys will cause tons of problems.  Fix your primary key
>>so that the database and your Class::DBI classes agree on it.
> 
> 
> I think the key is correct, no? It's the Essential columns that is
> sub-optimal. As I understand what the OP said, units is a table with
> [just?] two columns that are [nearly] always used together:
> 
>   id   - is the primary key
>   name - is presumably text for display
> 
> But only id is in the Essential column group. If so, add name to the
> Essential group and this will [approximately] halve the number of
> queries.

Yes, that is correct. I will have a play with the Essential columns.

The SQL behaviour is:

1. SELECT id FROM units WHERE id = 86
2. SELECT name FROM units WHERE id =86

Unfortunately, this will not reduce the total number of SQL statements
by very much. Here is what I am dealing (just gathered some more stats
today):

Sql    Objects   Seconds
4375   49279      141.328
4367   54147      143.885
4246   1480281   1964.542
3295   38589      110.198
3071   30974       28.225
2971   64833       27.455

But! I will do everything possible to reduce the the SQL counts above.
(The above table lists the worst offenders first).

So,

1. review and re-specify the Essential columns
    ... and I am wondering if it might be best to simply load all
    non-blob columns and be done with it

2. manually re-write all joins were possible

cheers
rickw

        -- 
        _________________________________
Rick Welykochy || Praxis Services

Virtue is more to be feared than vice, because its excesses are not subject
to the regulation of conscience.
      -- Adam Smith

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi