Optimize query in Django for Datatables

Question

Optimize query in Django for Datatables

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

The project uses MySql as a database engine, it makes me slow to show one of all the records in a table that contains about 1500 records.

To send the data to the template I use a ListView in the view and in the template I use a Datatables to show the data.

class ConductorBackendListView(PermissionRequiredMixin, generic.ListView):
    permission_required = 'datos.gestionar'
    model = Conductor
    template_name = 'datos/conductor_backend_list.html'
    context_object_name = 'conductores'
    #paginate_by = 20

    def get_queryset(self):
        return Conductor.objects.all().order_by('-pk')

The option that works best is putting a pagination, which as you see then I commented on the code, since I try to use the advantages of datatables But the result is that the view takes about 5 seconds to load completely using the Datatables, whereas if I use the pagination of django it takes milliseconds, but I can not use the functions of sorting and searching that the Datatables offers me.

I also add the driver part_backend_list.html

<script type="text/javascript" charset="utf-8">
            $(document).ready(function() {
                $('#tabla-conductores-backend').DataTable({
  "columnDefs": [
    { "orderable": false, "targets": 8},

  ]
  ,
  "language":{
       "lengthMenu":"Mostrar _MENU_ registros por página.",
       "zeroRecords": "Lo sentimos. No se encontraron registros.",
             "info": "Mostrando página _PAGE_ de _PAGES_",
             "infoEmpty": "No hay registros aún.",
             "infoFiltered": "(filtrados de un total de _MAX_ registros)",
             "search" : "Búsqueda",
             "LoadingRecords": "Cargando ...",
             "Processing": "Procesando...",
             "SearchPlaceholder": "Comience a teclear...",
             "paginate": {
     "previous": "Anterior",
     "next": "Siguiente", 
     }
      } 
});
            } );
</script>
<table id="tabla-conductores-backend" class="display">
  <thead>
    <tr>
            <th>Id</th>
      <th>Nombre</th>
            <th>Apellidos</th>
            <th>Empresa</th>
            <th>Usuario</th>
            <th>Frontend</th>
            <th>Backend</th>
            <th>Baja</th>
      <th style="text-align:center">Modificar</th>
    </tr>
  </thead>
  <tbody>
{% if conductores %}
{% for conductor in conductores %}
      <tr>
                <td>{{ conductor.pk }}</td>
        <td>{{ conductor.Nombre }}</td>
                <td>{{ conductor.Apellidos }}</td>
                <td>{{ conductor.Empresa }}</td>
                <td>{{ conductor.Usuario }}</td>
                <td>{{ conductor.SituacionFrontend }}</td>
                <td>{{ conductor.SituacionBackend }}</td>
                <td>{{ conductor.Baja }}</td>
        <td align="center"><a href="{% url 'datos.editar_conductor_backend' pk=conductor.pk %}"> <span class="glyphicon glyphicon-pencil"</span> </a></td>
      </tr>
    {% endfor %}
{% endif %}
  </tbody>
</table>

How could this performance improve? Any other ideas? Maybe I'm focusing on them badly.

Thanks!

javascript jquery django datatables

asked by Cecilio Alonso 10.08.2017 в 10:37

source

2 answers

0

You should also use the select_related or prefetch_related , this is going to make you do fewer queries, you have not taught the models but seeing the values, you can put this (adapt it to your models)

Conductores = Conductor.objects.values('pk', 'Nombre', 'Apellidos', 'Empresa__Nombre',
'Usuario__username', 'SituacionFrontend', 'SituacionBackend', 'Baja'
).select_related('Usuario', 'Empresa').all().order_by('-pk')

answered by 14.08.2017 в 00:19

How to select an element of the DOM for its id in ionic2 Selection of checkbox with value equal to the values of the POST

score 0 · Accepted Answer

Following the advice of @FranciscoPuga and @GermanAlzate, thank you very much indeed, I have made changes so that the DataTable receives the data via Json.

The improvement has been very substantial since we have gone from about 5 seconds of load to just over 300ms

At the moment I have not used django-rest-framework and surely this solution can be improved ...

views.py

def conductoresJson(request):
    Conductores = Conductor.objects.values('pk','Nombre','Apellidos','Empresa__Nombre','Usuario__username','SituacionFrontend','SituacionBackend','Baja').all().order_by('-pk')
    datosJson = json.dumps(list(Conductores))
    return HttpResponse("""{"data":"""+datosJson+"}" , content_type='application/json')


def vistaConductoresJson(request):
    return render(request, 'datos/conductor_backend_list_json.html')

conductor_backend_list_json.html

<script>
    $(document).ready(function() {
    var tabla = $('#tabla-conductores-backend-json').DataTable( {
        "processing": true,
        "ajax": " {% url 'datos.conductoresJson' %}",
                "dataSrc": "data",
                "language":{
                        "lengthMenu":"Mostrar _MENU_ registros por página.",
                        "zeroRecords": "Lo sentimos. No se encontraron registros.",
            "info": "Mostrando página _PAGE_ de _PAGES_",
            "infoEmpty": "No hay registros aún.",
            "infoFiltered": "(filtrados de un total de _MAX_ registros)",
            "search" : "Búsqueda",
            "LoadingRecords": "Cargando ...",
            "Processing": "Procesando...",
            "SearchPlaceholder": "Comience a teclear...",
            "paginate": {
                                "previous": "Anterior",
                                "next": "Siguiente", 
                        }
                }, 
                columns: [
                        { data:'pk' },
                        { data:'Nombre'},
                        { data:'Apellidos'},
                        { data:'Empresa__Nombre'},
                        { data:'Usuario__username'},
                        { data:'SituacionFrontend'},
                        { data:'SituacionBackend'},
                        { data:'Baja'},
                        { data : null, defaultContent : "<center><a href='#'><span class='glyphicon glyphicon-pencil'</span></a></center>", orderable:false },
                        ],
    } );
        $('#tabla-conductores-backend-json tbody').on( 'click', 'a', function () {
        var fila = tabla.row( $(this).parents('tr') ).data();
                window.location.href = "/datos/editar-conductor-backend/"+fila['pk'];
    } );
} );
</script>

<table id="tabla-conductores-backend-json" class="display">
  <thead>
    <tr>
            <th>Id</th>
      <th>Nombre</th>
            <th>Apellidos</th>
            <th>Empresa</th>
            <th>Usuario</th>
            <th>Frontend</th>
            <th>Backend</th>
            <th>Baja</th>
      <th style="text-align:center">Modificar</th>
    </tr>
  </thead>
</table>