I’m having a performance issue with non-needed SQL queries (Symfony 5.4, Doctrine 2, Elasticsearch 6.1.1, FosElastica 5.2)
Here are an extract of the concerned entities :
class Etablissement
{
/**
* @ORMManyToOne(targetEntity="AppEntityEtablissement", cascade={"persist"}, inversedBy="geres", fetch="EXTRA_LAZY")
* @ORMJoinColumn(name="gestionnaire_id", referencedColumnName="id")
*/
protected $gestionnaire; // The manager of this Etablissement, can be NULL
/**
* @ORMOneToMany(targetEntity="AppEntityEtablissement", mappedBy="gestionnaire", fetch="EXTRA_LAZY")
* @ORMOrderBy({"type" = "ASC"})
*/
protected $geres; // All the managed Etablissements
/** @ORMOneToOne(targetEntity="TexteReferencement", mappedBy="etablissement", cascade={"persist"}) */
protected $texteReferencement; // can be NULL
}
class TexteReferencement
{
/**
* @ORMOneToOne(targetEntity="Etablissement", inversedBy="texteReferencement", cascade={"persist"})
* @ORMJoinColumn(name="etablissement_id", referencedColumnName="id", unique=true)
*/
private $etablissement;
}
I’m using Elastica to get a list of Etablissement then display only the id. This is an example of the result :
Id | Gestionnaire |
---|---|
ET075-183 | NULL |
ET075-341 | GE075-46 |
ET075-403 | NULL |
ET075-469 | GE075-103 |
The problem is Doctrine generates some extra SQL queries :
SELECT * FROM texte_referencement t0 WHERE t0.etablissement_id = 'GE075-46';
SELECT * FROM texte_referencement t0 WHERE t0.etablissement_id = 'GE075-103';
I’d like to get rid of thoes usesless queries since I don’t need data from texte_referencement
Maybe I’m wrong but this is my understanding of the workflow :
- Elastica hydrates the Etablissement entity set
- Since each Etablissement have a manager, Doctrine loads the manager of each Etablissement (if exists)
- Since each Etablissement have a TexteReferencement, Doctrine loads the data for each manager
But this seems non-sence :
- using fetch=”EXTRA_LAZY” should not run step #2
- why do step #3 concerns only managed Etablissement ?