Yes, still new. Getting the hang of using (and generally loving) XPDO. But I definitely lack some understanding. I'd appreciate if somebody could have a quick look at this.
This is my custom table schema.
<?xml version="1.0" encoding="UTF-8"?>
<model package="koshu" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
<object class="Agents" table="agents" extends="xPDOSimpleObject">
<field key="agent_code" dbtype="int" precision="6" phptype="integer" null="true" />
<field key="agent_group" dbtype="varchar" precision="4" phptype="string" null="true" />
<field key="agent_name" dbtype="varchar" precision="256" phptype="string" null="true" />
<field key="agent_country" dbtype="varchar" precision="3" phptype="string" null="true" />
<field key="agent_office" dbtype="varchar" precision="3" phptype="string" null="true" index="index" />
<index alias="OFFICE" name="OFFICE" primary="false" unique="false" type="BTREE" >
<column key="agent_office" length="" collation="A" null="true" />
</index>
<aggregate alias="Market" class="Markets" local="agent_office" foreign="id" cardinality="one" owner="foreign" />
<composite alias="Tour" class="Tours" local="id" foreign="tour_agentNumber" cardinality="many" owner="local" />
</object>
<object class="Markets" table="markets" extends="xPDOSimpleObject">
<field key="market_city" dbtype="varchar" precision="50" phptype="string" null="true" />
<field key="market_office" dbtype="varchar" precision="3" phptype="string" null="true" />
</object>
<object class="Remarks" table="remarks" extends="xPDOSimpleObject">
<field key="remark_date" dbtype="datetime" phptype="datetime" null="true" index="index" />
<field key="remark_creator" dbtype="varbinary" precision="12" phptype="binary" null="true" />
<field key="remark_level" dbtype="int" precision="1" phptype="integer" null="true" />
<field key="remark_type" dbtype="int" precision="2" phptype="integer" null="true" index="index" />
<field key="remark_tour" dbtype="int" precision="8" phptype="integer" null="true" index="index" />
<field key="remark_service" dbtype="int" precision="8" phptype="integer" null="true" index="index" />
<field key="remark_text" dbtype="text" phptype="string" null="true" />
<index alias="DATE" name="DATE" primary="false" unique="false" type="BTREE" >
<column key="remark_date" length="" collation="A" null="true" />
</index>
<index alias="TYPE" name="TYPE" primary="false" unique="false" type="BTREE" >
<column key="remark_type" length="" collation="A" null="true" />
</index>
<index alias="TOUR" name="TOUR" primary="false" unique="false" type="BTREE" >
<column key="remark_tour" length="" collation="A" null="true" />
</index>
<index alias="SERVICE" name="SERVICE" primary="false" unique="false" type="BTREE" >
<column key="remark_service" length="" collation="A" null="true" />
</index>
<aggregate alias="Service" class="Services" local="remark_service" foreign="id" cardinality="one" owner="foreign" />
<aggregate alias="Tour" class="Tours" local="remark_tour" foreign="id" cardinality="one" owner="foreign" />
</object>
<object class="Services" table="services" extends="xPDOSimpleObject">
<field key="service_status" dbtype="varchar" precision="4" phptype="string" null="true" index="index" />
<field key="service_lastUpdate" dbtype="datetime" phptype="datetime" null="true" index="index" />
<field key="service_tourno" dbtype="int" precision="10" phptype="integer" null="true" index="index" />
<field key="service_deadline_cfm" dbtype="datetime" phptype="datetime" null="true" />
<field key="service_key_so" dbtype="varchar" precision="10" phptype="string" null="true" />
<field key="service_key_ga" dbtype="varchar" precision="10" phptype="string" null="true" />
<field key="service_key_ok" dbtype="varchar" precision="10" phptype="string" null="true" />
<field key="service_price_so" dbtype="float" phptype="float" null="true" />
<field key="service_price_ga" dbtype="float" phptype="float" null="true" />
<field key="service_price_cfm" dbtype="float" phptype="float" null="true" />
<field key="service_supplement" dbtype="float" phptype="float" null="true" />
<field key="service_currency" dbtype="varchar" precision="3" phptype="string" null="true" />
<field key="service_priority" dbtype="int" precision="1" phptype="integer" null="true" default="0" />
<field key="service_difficultPeriod" dbtype="int" precision="1" phptype="integer" null="true" default="0" />
<field key="service_date" dbtype="datetime" phptype="datetime" null="true" />
<field key="service_cxlDate" dbtype="datetime" phptype="datetime" null="true" index="index" />
<field key="service_market" dbtype="int" precision="4" phptype="integer" null="true" default="0" index="index" />
<index alias="LASTUPDATE" name="LASTUPDATE" primary="false" unique="false" type="BTREE" >
<column key="service_lastUpdate" length="" collation="A" null="true" />
</index>
<index alias="TOUR" name="TOUR" primary="false" unique="false" type="BTREE" >
<column key="service_tourno" length="" collation="A" null="true" />
</index>
<index alias="MARKET" name="MARKET" primary="false" unique="false" type="BTREE" >
<column key="service_market" length="" collation="A" null="true" />
</index>
<index alias="STATUS" name="STATUS" primary="false" unique="false" type="BTREE" >
<column key="service_status" length="" collation="A" null="true" />
</index>
<index alias="CANCELALTION" name="CANCELALTION" primary="false" unique="false" type="BTREE" >
<column key="service_cxlDate" length="" collation="A" null="true" />
</index>
<aggregate alias="Market" class="Markets" local="service_market" foreign="id" cardinality="one" owner="foreign" />
<aggregate alias="Tour" class="Tours" local="service_tourno" foreign="tour_number" cardinality="one" owner="foreign" />
<composite alias="Remarks" class="Remarks" local="id" foreign="remark_service" cardinality="many" owner="local" />
</object>
<object class="Tours" table="tours" extends="xPDOSimpleObject">
<field key="tour_status" dbtype="varchar" precision="3" phptype="string" null="true" index="index" />
<field key="tour_lastUpdate" dbtype="datetime" phptype="datetime" null="true" index="index" />
<field key="tour_number" dbtype="int" precision="10" phptype="integer" null="true" default="0" index="unique" />
<field key="tour_name" dbtype="varchar" precision="45" phptype="string" null="true" />
<field key="tour_provisional" dbtype="int" precision="1" phptype="integer" null="true" default="0" index="index" />
<field key="tour_agentNumber" dbtype="int" precision="7" phptype="integer" null="true" default="0" />
<field key="tour_market" dbtype="int" precision="4" phptype="integer" null="true" default="0" index="index" />
<field key="tour_responsible_so" dbtype="varchar" precision="3" phptype="string" null="true" />
<field key="tour_opererator_so" dbtype="varchar" precision="3" phptype="string" null="true" />
<field key="tour_operator_aga" dbtype="varchar" precision="3" phptype="string" null="true" />
<field key="tour_controller" dbtype="varchar" precision="3" phptype="string" null="true" />
<field key="tour_pax" dbtype="int" precision="4" phptype="integer" null="true" default="0" />
<field key="tour_bookingstatus" dbtype="int" precision="4" phptype="integer" null="true" default="0" />
<field key="tour_date_ga" dbtype="datetime" phptype="datetime" null="true" />
<field key="tour_date_departure" dbtype="datetime" phptype="datetime" null="true" />
<field key="tour_deadline_cfm" dbtype="datetime" phptype="datetime" null="true" />
<field key="tour_profit_sold" dbtype="float" phptype="float" null="true" default="0" />
<field key="tour_profit_ga" dbtype="float" phptype="float" null="true" default="0" />
<index alias="TOURNUMBER" name="TOURNUMBER" primary="false" unique="true" type="BTREE" >
<column key="tour_number" length="" collation="A" null="true" />
</index>
<index alias="STATUS" name="STATUS" primary="false" unique="false" type="BTREE" >
<column key="tour_status" length="" collation="A" null="true" />
</index>
<index alias="PROVISIONAL" name="PROVISIONAL" primary="false" unique="false" type="BTREE" >
<column key="tour_provisional" length="" collation="A" null="true" />
</index>
<index alias="MARKET" name="MARKET" primary="false" unique="false" type="BTREE" >
<column key="tour_market" length="" collation="A" null="true" />
</index>
<index alias="LASTUPDATE" name="LASTUPDATE" primary="false" unique="false" type="BTREE" >
<column key="tour_lastUpdate" length="" collation="A" null="true" />
</index>
<aggregate alias="Market" class="Markets" local="tour_market" foreign="id" cardinality="one" owner="foreign" />
<composite alias="Services" class="Services" local="tour_number" foreign="service_tourno" cardinality="many" owner="local" />
<composite alias="Remarks" class="Remarks" local="id" foreign="remark_tour" cardinality="many" owner="local" />
</object>
</model>
I'm importing relatively large amounts of data - and that all works fine. But I find that my data retrieval doesn't only take too long, but also eats up way too much memory.
Here an example. I make two separate calls.
1. Get all services for a specific tour.
$oServicesByTour = $modx->getCollection('Services', array('service_tourno' => 1285788));
This returns 6 rows (of the 21510 in the table), takes 0 seconds and the RAM peaks out at 11.3 MB.
The seconds call is getting all services by department:
$oMarket = $modx->getObject('Markets',array('market_office' => 'TYO'));
$oTours = $modx->getCollection('Services', array('service_market' => $oMarket->get('id')));
This gets 8872 rows (of the 21510 in the table), takes 11 seconds and spikes at 126.31 mb RAM.
What am I doing wrong?
Thanks for any tips and tricks and hints.
a.
