Symfony2 Doctrine+Twig query taking too long

Question

I'm currently building a query to return a set of records between desired dates as it follows:

public function findBetweenDates(\Datetime $date1,\Datetime $date2)
{
        $date1=$date1->setTime(07,00,00);
        date_modify($date2,'+1 day');
        $date2->setTime(06,59,00);
        $qb = $this->getEntityManager()->createQueryBuilder()
            ->select('e')
            ->from("AppBundle:Movimento","e")
            ->andWhere('e.pesagem1 BETWEEN :from AND :to')
            ->setParameter('from', $date1 )
            ->setParameter('to', $date2)
            ->orderBy('e.id','DESC')

        ;
        $result = $qb->getQuery()->getResult();
        return $result;
    }

the class Movimento has some ManyToOne connections as shown below:

class Movimento
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

  /**
  * @ORM\ManyToOne(targetEntity="Service")
  * @ORM\JoinColumn(name="service", referencedColumnName="id")
  **/
   private $service;

When i get the records and render them in twig:

 {% for item in items %}
    <tr>
         <td>{{ item.id }} </td>
        <td>{{ item.service.name }}</td>
//#MORE CODE BELOW //

by calling servico.name from another entity i get tons of non wanted queries as a result to display the name of the service instead of its id. We are talking about something in the 6k range of records in every response.

I would like some help, if it's possible to optimize this query using the my query builder or should i remake the whole query more of a "SQL" example:

Select a.name, b.id
From service as a, movimento as b
Between bla bla bla

Any Help/suggestions are greatly appreciated.

EDIT 1 i changed my query builder after reading this post Symfony 2/Doctrine: How to lower the num of queries without losing the benefit of ORM?

I did reduce 175 queries to a single one

$qb = $this->createQueryBuilder('e')
            ->addSelect('service')->join('e.service','service')
            ->addSelect('motorista')->join('e.motorista','motorista')
            ->addSelect('residuo')->join('e.residuo','residuo')
          //  ->from("AppBundle:Movimento","e")
            ->andWhere('e.pesagem1 BETWEEN :from AND :to')
            ->setParameter('from', $date1 )
            ->setParameter('to', $date2)
            ->orderBy('e.id','DESC')

But still the page is taking around 8 seconds to load (its 6900 records) and after checking performance the response time for my new query is 177.79 ms, but my twig+ controller is taking the remaining 7.x seconds as it shows the pic my controller is something really simple

 public function getMovimentosAction(Request $request)
    {

        $startDate = $request->request->get('startDate');
        $endDate = $request->request->get('endDate');

        if (empty($startDate))
            $startDate = date("Y-m-d") ;
        if (empty($endDate))
            $endDate = date("Y-m-d");

        $em=$this->getDoctrine()->getRepository('AppBundle:Movimento');
        $dados=$em->findBetweenDates(new \DateTime($startDate),new \DateTime($endDate));

        return $this->render('AppBundle:Movimentos:logtable-movimento.html.twig', array(
            'items' => $dados
        ));
    }

and my twig just iterates over the rows and displays them on a table as i gave a partial example above.

Any help/suggestions would be greatly appreciated.

EDIT2 My view that is passed by ajax to be rendered as datatable.js

<table id="example" class="table table-striped table-bordered table-hover" cellspacing="0" width="100%">
    <thead class="dataTableHeader">
    <tr>
        <th>Talão</th>
        <th>Nº Talão</th>
        <th>Motorista</th>
        <th>Residuo</th>
        <th>Serviço</th>
        <th>Matricula</th>
        <th>1º Pesagem</th>
        <th>Peso Liquido</th>
        <th>Fluxo</th>
        <th>Circuito</th>
        <th>Verificado</th>
        <th></th>
    </tr>
    </thead>
    <tfoot class="dataTableHeader">
    <tr>
        <th>Talão</th>
        <th>Nº Talão</th>
        <th>Motorista</th>
        <th>Residuo</th>
        <th>Serviço</th>
        <th>Matricula</th>
        <th>1º Pesagem</th>
        <th>Liquido</th>
        <th>Fluxo</th>
        <th>Circuito</th>
        <th>Verificado</th>
        <th></th>
    </tr>
    </tfoot>
    <tbody>
    {% for item in items %}
    <tr>
        <td align="center"><a href="{{ path("_movimento_generate_pdf",{ id: item.id }) }}"> <i class="fa fa-print fa-2x" aria-hidden="true"></i>
            </a></td>
        <td>{{ item.id }} <a><i class="fa fa-eye" title="Visualizar Movimento" aria-hidden="true"></i></a>
        </td>
        <td>{{ item.motorista.idFuncionario }} - {{ item.motorista.nome }}</td>
        <td>{{ item.residuo.nome }}</td>
        <td>{{ item.servico.nome }}</td>
        <td>{{ item.matricula }}</td>
        <td>{{ item.pesagem1|date('Y-m-d h:m') }}</td>
        <td>{{ item.liquido }} kg</td>
        <td>{% if item.tipoMovimento == 1 %} Entrada {% else %} Saida {% endif %}</td>
        <td>{{ item.circuito.code | default(" ") }}</td>
        <td class="text-center">{% if item.enable==1 %}
                <span style="color: transparent">&nbsp;</span>
                <i class="fa fa-circle" aria-hidden="true" style="color: green"></i>
            {% else %}

                <i class="fa fa-times" aria-hidden="true" style="color: red;"></i>
            {% endif %}
        </td>
        <td class="text-center">
            <a class="btn btn-default" href="{{ path('_movimentos_edit',{ 'id' : item.id}) }}">
                <i class="fa fa-cog" title="Editar" aria-hidden="true"></i>
                <span class="sr-only">Settings</span>
            </a>
        </td>
    </tr>

    {% endfor %}


    </tbody>
</table>

and in my html

 $("#submitButtonQuery").click(function(event){
            event.preventDefault();
            var l = Ladda.create(this);
            l.toggle();
            $.post( "/movimentos/getList",
                $( "#formAjaxify" ).serialize())
                .done(function(data)
                {
                    $('#example').remove();

                    $("#tabelaLog").html(data);
                    oTable=$('#example').DataTable(
                        {
                            "scrollX": true,
                            responsive: true,

                            "language": {
                                "url": "http://cdn.datatables.net/plug-ins/1.10.11/i18n/Portuguese.json"
                            }
                        }
                    );
                    oTable.order( [ 0, 'desc' ] )
                        .draw();
      })
        .always(function(){
            l.toggle()})
    ;
});

Do you really need to render **all** records at once? Can't you paginate results? — dlondero, Jul 15 '16 at 07:45
Well, the thing about importing all records at once, is that it makes it easier to search using datatables and also the export to pdf/csv feature. — Noize, Jul 15 '16 at 08:36

score 0 · Answer 1 · answered Jul 15 '16 at 11:47

As long as you have a connection between 'Movimento' and 'Service' then for each 'movimento' that you get as a result a 'service' will be serialized together. This means that if you have a query that returns 100 'movimento' then together with it all 'service' objects (100) will be required to be fetched.

If you don't want to have the Service as an object in each item (AKA item.service.blahblah) then you need to have a more direct query.

if you do it with query builder then you will need something like:

$repository = $this->getDoctrine()
        ->getRepository('YourownBundle:Movimento'); //the main repo from which to get data

    $query = $repository->createQueryBuilder('m') // query builder on repo
        ->join('m.service', 's') // join the second object to select from
        ->select('m.id') // select everything from m objet
        ->addSelect('s.name') // select everything from service (s) object
        ->where('e.pesagem1 BETWEEN :from AND :to')
        ->setParameter('from', $date1 )
        ->setParameter('to', $date2)
        ->orderBy('e.id','DESC')

the rest of your code should be as you have it... but then you don't have a serialized object but only the selects that you make (eg. m.id, s.name)

Symfony2 Doctrine+Twig query taking too long

1 Answers1