Poor performance in selecting addresses
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Sahana Agasti | Status tracked in Mayon | |||||
Mayon |
Triaged
|
Medium
|
Chad Heuschober |
Bug Description
Because of doctrine's default branching and sorting behaviour, a unnecessary number of queries are executed which impede performance -- on average this results in 8 queries per address (per type, per person, etc).
The following doctrine_rawsql() should optimize this behaviour (with the proper where clause). However, developers implementing this should take care to note that the line/inline strings sill need to be concated and the address flattened in a for loop.
<-- cut -->
->addComponent(
->addComponent(
->addComponent(
->addComponent(
->addComponent(
->addComponent(
->addComponent(
->addComponent(
->addComponent(
->select(
{ct.address_
{advl.value},
{adel.address_
{adfm.line_
{adfm.inline_
{adfm.pre_
{adfm.post_
{adfm.is_required},
{adft.field_type},
')
->from('
ag_entity_
INNER JOIN ag_address_
INNER JOIN
(SELECT ec.entity_id,
ec.address_
MAX(ec.priority) mxpriority
FROM ag_entity_
GROUP BY ec.entity_id,
ec.address_
) mxeac ON eac.entity_id = mxeac.entity_id
AND eac.address_
AND eac.priority = mxeac.mxpriority
INNER JOIN ag_address addr ON eac.address_id = addr.id
INNER JOIN ag_address_standard adst ON addr.address_
INNER JOIN ag_address_format adfm ON adst.id = adfm.address_
INNER JOIN ag_field_type adft ON adfm.field_type_id = adft.id
INNER JOIN ag_address_element adel ON adfm.address_
INNER JOIN ag_address_value advl ON adel.id = advl.address_
INNER JOIN ag_address_
AND addr.id = admj.address_id
')
->orderBy('addr.id, adfm.line_sequence, adfm.inline_
->where(
<-- cut -->
attached it to mayon