[CDBI] How well does Class::DBI scale?
rick at praxis.com.au
Tue Dec 20 00:41:29 GMT 2005
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
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
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).
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
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
More information about the ClassDBI