php - symfony3 inverse entity mapping slow -

i got following entities:

<?php  namespace appbundle\entity;  use doctrine\orm\mapping orm; use doctrine\common\collections\arraycollection; use doctrine\common\collections\collection;  /**  * productnum  *  * @orm\table(name="productnum")  * @orm\entity  */ class productnum  {   /**    * @var object    *    * @orm\onetomany(    *      targetentity="appbundle\entity\products",    *      mappedby="productnum",    *      cascade={"persist", "remove"}    * )    */   protected $productnuminverse;    /**    * constructor    */   public function __construct()   {       $this->productnuminverse = new arraycollection();   }     /**    * productnuminverse    *    * @return collection    */   public function getproductnuminverse()   {       return $this->productnuminverse;   }  }  <?php  namespace appbundle\entity;  use doctrine\orm\mapping orm; use doctrine\common\collections\arraycollection; use doctrine\common\collections\collection;   /**  * products  *  * @orm\table(name="products")  * @orm\entity  */ class products {    /**    * @var \appbundle\entity\productnum    *    * @orm\manytoone(targetentity="appbundle\entity\productnum", inversedby="productnuminverse")    * @orm\joincolumns({    *   @orm\joincolumn(name="productnum_id", referencedcolumnname="row_id")    * })    */   public $productnum;     /**    * @var object    *    * @orm\onetomany(    *      targetentity="appbundle\entity\product_region",  fetch="eager",    *      mappedby="productid",    *      cascade={"persist", "remove"}    * )    */   protected $productinverse;    /**    * constructor    */   public function __construct()   {       $this->productinverse = new arraycollection();   } }  <?php  namespace appbundle\entity;  use appbundle\appbundle; use appbundle\entity\productnum_filial; use doctrine\orm\mapping orm;  /**  * productnum_region  *  * @orm\table(name="productnum_region")  * @orm\entity  */ class productnum_region {   //regular getters , setters here... } 

and mapping entity:

<?php  namespace appbundle\entity;  use doctrine\orm\mapping orm;  /**  * product_region  *  * @orm\table(name="product_region")  * @orm\entity  */ class product_region {   /**    * @orm\column(name="id", type="integer")    * @orm\id    * @orm\generatedvalue(strategy="identity")    */   private $id;  /**  * @var \appbundle\entity\products  *  * @orm\manytoone(targetentity="appbundle\entity\products")  * @orm\joincolumns({  *   @orm\joincolumn(name="product_id", referencedcolumnname="row_id")  * })  *   */ private $productid;  /**  * @var \appbundle\entity\productnum_region  *  * @orm\manytoone(targetentity="appbundle\entity\productnum_region")  * @orm\joincolumns({  *   @orm\joincolumn(name="region_id", referencedcolumnname="id")  * })  */ private $regionid; } 

in controller got following code

$sql = sprintf("select p 'appbundle:productnum' p");      $productnums = $em->createquery($sql)        ->setfirstresult($start)        ->setmaxresults($length)        ->getresult();      $data = [];      foreach($productnums $productnum) {          $prods = '';          foreach($productnum->getproductnuminverse() $product) {             $filials = [];             $regions = [];             if($product && $product->getallregions()){                 $regions[] = $filials[] = 'all';                     } elseif($product && $product->getallfilials()){                       $filials[] = 'all';                        $regs = $product->getproductinverse();                        foreach($regs $reg){                                   $regions[] =  $reg->getregionid()->getname();                                 }                         }elseif($product){                             $regs = $product->getproductinverse();                             foreach($regs $reg){                                 $fil = $reg->getregionid()->getfilial()->getname();                                 if(!in_array($fil, $filials)){                                     $filials[] = $fil;                                 }                                 $regions[] =  $reg->getregionid()->getname();                               }   } } 

the problem on local machine code works fine, on remote server works slow. opened symfony profiler both on local machine , on server , saw on local machine (which ok) took 336 db (1.5 sec total) queries complete of following;

select t0.id id_1, t0.name name_2, t0.code code_3, t0.filial_id filial_id_4 productnum_region t0 t0.id = ? parameters: [0 => 100]  

and

select t0.row_id row_id_1, ...  t0.productnum_id productnum_id_21, t22.id id_23, t22.product_id product_id_24, t22.region_id region_id_25 products t0 left join product_region t22 on t22.product_id = t0.row_id t0.productnum_id = ? parameters: [0 => 945] 

while on server there 36 queries in total (20 sec total, bad), 1 of (and slowest one) following:

select t0.row_id row_id_1, ... t0.productnum_id productnum_id_21, t22.id id_23, t22.product_id product_id_24, t22.region_id region_id_25  products t0 left join product_region t22 on t22.product_id = t0.row_id t0.row_id in (?) parameters: [ 0 => [ 0 => 2, 1 => 97, 2 => 212, 3 => 225, 4 => 297, 5 => 355, 6 => 356, 7 => 482, 8 => 571, 9 => 737, 10 => 789  ...many many many data here... 

so question how happen same code on different machines convert different queries , how avoided?

thank you

it may have overall mistake database design or code requiring have 4 foreach loops , 2 if statements nested together.

to answer question - need join corresponding entities in query - doctrine isn't going you. when perform this:

foreach($productnum->getproductnuminverse() $product) { 

every iteration through loop, doctrine going individually query $product, because didn't select in original query. why see 336 database queries, when should see one. instead of:

select p 'appbundle:productnum' p 

your query should more this:

select p, pi, pip, pir appbundle:productnum p join p.productnuminverse pi join pi.product pip join pi.region pir 

this should drastically reduce number of queries running - ideally should down 1 query retrieving data. in short, doctrine doesn't join associated entities unless tell to.