Poor performance in selecting addresses

[Chad Heuschober]

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('eac', 'agEntityAddressContact')
->addComponent('ct', 'agAddressContactType')
->addComponent('addr', 'agAddress')
->addComponent('admj', 'agAddressMjAgAddressValue')
->addComponent('advl', 'agAddressValue')
->addComponent('adst', 'agAddressStandard')
->addComponent('adfm', 'agAddressFormat')
->addComponent('adft', 'agFieldType')
->addComponent('adel', 'agAddressElement')
->select('{addr.id},
{ct.address_contact_type},
{advl.value},
{adel.address_element},
{adfm.line_sequence},
{adfm.inline_sequence},
{adfm.pre_delimiter},
{adfm.post_delimiter},
{adfm.is_required},
{adft.field_type},
')
->from('
ag_entity_address_contact eac
INNER JOIN ag_address_contact_type ct ON eac.address_contact_type_id = ct.id
INNER JOIN
(SELECT ec.entity_id,
ec.address_contact_type_id,
MAX(ec.priority) mxpriority
FROM ag_entity_address_contact ec
GROUP BY ec.entity_id,
ec.address_contact_type_id
) mxeac ON eac.entity_id = mxeac.entity_id
AND eac.address_contact_type_id = mxeac.address_contact_type_id
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_standard_id = adst.id
INNER JOIN ag_address_format adfm ON adst.id = adfm.address_standard_id
INNER JOIN ag_field_type adft ON adfm.field_type_id = adft.id
INNER JOIN ag_address_element adel ON adfm.address_element_id = adel.id
INNER JOIN ag_address_value advl ON adel.id = advl.address_element_id
INNER JOIN ag_address_mj_ag_address_value admj ON advl.id = admj.address_value_id
AND addr.id = admj.address_id
')
->orderBy('addr.id, adfm.line_sequence, adfm.inline_sequence')
->where('eac.entity_id = ?', 12);
<-- cut -->

[Chad Heuschober (chad-heuschober)]

attached it to mayon