Performance of Queries

[king-11]

In was profiling the SQL queries made using django_debug_toolbar even though I wasn't providing any query_param, the SQL queries are taking up a lot of time.

• with filter and no query params

time: 10.28ms
queries: 7

• with no filter at the same endpoint

time: 0.71ms
queries: 3

So to improve time when not filtering I implemented two separate views, I think those SQL queries are redundant and can really improve the filter performance when not filtering so how can I do that or is it part of django_filter only.

class ImageFilterView(generics.ListAPIView):
    queryset = images.objects.prefetch_related('tag').select_related(
        'person__user').filter(verified=True).order_by('-likes')
    serializer_class = ImageSerializer
    permission_classes = (permissions.IsAuthenticatedOrReadOnly,)
    filter_backends = [DjangoFilterBackend]
    filterset_class = ImageFilter
    pagination_class = ImageListPagination

class ImageFilter(filterset.FilterSet):
    photographer = filters.CharFilter(
        field_name="instaHandle", lookup_expr="icontains")
    place = filters.CharFilter(field_name="place", lookup_expr="icontains")
    likes = filters.NumericRangeFilter(field_name="likes")

    class Meta:
        model = images
        fields = ('place', 'photographer', 'likes', 'person')

[AviKKi]

@king-11 Can you post the screenshots of your django_debug_toolbar ? Looking at the SQL queries would help.

I guess django-filter doesn't change queryset for blank queries.

[carltongibson]

@king-11 See my #1263 (comment) — it's essentially the same issue. If you can provide a better queryset to the filterset's form you can greatly reduce the number of queries you need to make.

[king-11]

@carltongibson as such am already using the most optimised queryset using prefect_related and select_related options which is visible because the number of queries then made are 3 only the issue that I saw was some duplicate queries were made

[king-11]

@AviKKi here

with filterset class and no query parameters

with no filterset class and no additional filtering

[carltongibson]

Check that those are actually duplicate queries first (There's an issue in debug toolbar django-commons/django-debug-toolbar#1239) It's likely only the one is real. (Do they have the exact same trace?)

Also, check the time disabling SQL tracing, as that's pretty slow. You'll likely find it's MUCH faster.

If your SELECT FROM image_store_photographer is already using the best queryset then there's not much else you'll be able to do (indexes maybe) — ultimately it HAS to fetch the values sometime.

(If it's static you can cache...)

Hopefully some of that helps.

[king-11]

Ohh thanks for looking up that issue @carltongibson. Yeah I will lookup caching thanks for your advice :))