Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-10247

Optimize access to SQuaSH REST API

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      When using nested serializers in DRF the queries involving multiple tables can be improved using the prefetch_related() method from the Django QuerySet API which automatically retrieve related objects in single batch.

      https://docs.djangoproject.com/en/1.11/ref/models/querysets/#prefetch-related

      Also drf-extensions package can be used to enable cache in SQUASH REST API at the view level.

      http://chibisov.github.io/drf-extensions/docs/#cacheresponsemixin

      A useful tool to monitor the queries that are executed when accessing the API endpoints is the Django debug toolbar:

      https://django-debug-toolbar.readthedocs.io/en/stable/

        Attachments

        1. cache1.png
          cache1.png
          279 kB
        2. cache2.png
          cache2.png
          212 kB
        3. prefetch1.png
          prefetch1.png
          565 kB
        4. prefetch2.png
          prefetch2.png
          380 kB
        5. prefetch3.png
          prefetch3.png
          541 kB

          Issue Links

            Activity

            Hide
            afausti Angelo Fausti added a comment - - edited

            prefetch1.png shows what happens when you have a nested serilaizer like JobSerializer in this case nested with MeasurementsSerializer and VersinedPackagesSerializer and do a query like this

            queryset = Job.objects.order_by('date')

            the database is hit several times.

            prefetch2.png shows the result when you use the prefetch_related() method:

            queryset = Job.objects.prefetch_related('packages', 'measurements').order_by('date')

            that reduces the number of queries from about 200 to 5 when accessing a page with 100 Job objects from the API.

            cache1.png and cache2.png shows the cache in action when accessing the api/jobs endpoint.

            In Django settings the cache response timeout was configured to 15 min.

            Show
            afausti Angelo Fausti added a comment - - edited prefetch1.png shows what happens when you have a nested serilaizer like JobSerializer in this case nested with MeasurementsSerializer and VersinedPackagesSerializer and do a query like this queryset = Job.objects.order_by('date') the database is hit several times. prefetch2.png shows the result when you use the prefetch_related() method: queryset = Job.objects.prefetch_related('packages', 'measurements').order_by('date') that reduces the number of queries from about 200 to 5 when accessing a page with 100 Job objects from the API. cache1.png and cache2.png shows the cache in action when accessing the api/jobs endpoint. In Django settings the cache response timeout was configured to 15 min.
            Hide
            afausti Angelo Fausti added a comment - - edited

            The prefetch_related() was applied to the JobViewSet and did a good optimization in the api/jobs endpoint as shown above. However, the api/measurements endpoint still shows performance problems because the method that lists the code changes was placed on the serializer side and must be moved to the corresponding view so that we can use the prefetch_related() method, this will be addressed in another ticket.

            Show
            afausti Angelo Fausti added a comment - - edited The prefetch_related() was applied to the JobViewSet and did a good optimization in the api/jobs endpoint as shown above. However, the api/measurements endpoint still shows performance problems because the method that lists the code changes was placed on the serializer side and must be moved to the corresponding view so that we can use the prefetch_related() method, this will be addressed in another ticket.
            Show
            afausti Angelo Fausti added a comment - https://github.com/lsst-sqre/qa-dashboard/pull/44
            Hide
            jhoblitt Joshua Hoblitt added a comment -

            Minor comment on GH PR.

            Show
            jhoblitt Joshua Hoblitt added a comment - Minor comment on GH PR.

              People

              • Assignee:
                afausti Angelo Fausti
                Reporter:
                afausti Angelo Fausti
                Reviewers:
                Joshua Hoblitt
                Watchers:
                Angelo Fausti, Frossie Economou, Joshua Hoblitt
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Summary Panel