Optimization tips

General

• get only necessary data, rather than everything that can be
• sometimes is good to prepare specific object(model) for different views, rather than use standard model on each view.
• for example for list prepare custom header dto (data transfer object) object with only primitive types with calculated values from dependent entities

SQL optimizations

• use syntax 'JOIN table ON ()' rather than 'FROM tableA, tableB WHERE ...'
• use more restrictive and fast conditions first
• it's better because database in each next step have less records to verify
• use indexes

Examples

• standard model, which can be insertable, updatable and deletable

<model name="warehouseStock"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schema.qcadoo.org/model"
 xsi:schemaLocation="http://schema.qcadoo.org/model http://schema.qcadoo.org/model.xsd"
 activable="false" auditable="false" deletable="false" insertable="false" updatable="false" >
    <fields>
        <belongsTo name="location" model="location" plugin="materialFlow" required="true" />
        <belongsTo name="product" model="product" plugin="basic" required="true" />
        <decimal name="quantity" required="true" />
        <decimal name="orderedQuantity"></decimal>
        <decimal name="minimumState"></decimal>
    </fields>
    <hooks>
    </hooks>
</model>

• dto header, which can not be insertable, updatable and deletable

<model name="warehouseStockListDto"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schema.qcadoo.org/model"
 xsi:schemaLocation="http://schema.qcadoo.org/model http://schema.qcadoo.org/model.xsd"
 activable="false" auditable="false" deletable="false" insertable="false" updatable="false" >
    <fields>
        <decimal name="quantity" />
        <string name="locationNumber" />
        <string name="locationName" />
        <string name="productNumber" />
        <string name="productName" />
        <string name="productUnit" />
        <decimal name="minimumState" />
        <decimal name="orderedQuantity" />
    </fields>
    <hooks>
    </hooks>
</model>

Links

https://wiki.postgresql.org/wiki/Performance_Optimization
https://wiki.postgresql.org/wiki/SlowQueryQuestions
http://explain.depesz.com/