There are a couple options you could try to see which is the best fit for
your data. With DEBUG=True in settings.py you can check the actual queries
and process time. It depends on the sizes of A and B. Another query you can
run is:
A.objects.exclude(id__in=B.objects.all().values_list('a_id', flat=True))
When I tried, it seemed to be about the same speed with my test data as the
one you had A.objects.filter(bs__isnull=True).
To see what queries are generated and the query time with DEBUG=True:
Open your Django Python Shell
>>> A.objects.exclude(id__in=B.objects.all().values_list('a_id', flat=True))
>>> A.objects.filter(bs__isnull=True)
>>> from django.db import connection
>>> for q in connection.queries:
>>> print("{0}: {1}".format(q['sql'], q['time']))
This will show you both queries generated and how long it took to get a
response from your DB.
You can also write raw SQL, if you can make one more efficiently.
Furbee
On Fri, Feb 2, 2018 at 10:56 AM, Vijay Khemlani <[email protected]> wrote:
> "with large of records in A and B, the above takes lot of time"
>
> How long? At first glance it doesn't look like a complex query or
> something particularly inefficient for a DB.
>
> On Fri, Feb 2, 2018 at 11:31 AM, Andy <[email protected]> wrote:
>
>> not that i know of
>>
>>
>> Am Freitag, 2. Februar 2018 15:28:26 UTC+1 schrieb Web Architect:
>>>
>>> Hi Andy,
>>>
>>> Thanks for your response. I was pondering on option a before posting
>>> this query thinking there could be better ways in django/SQL to handle
>>> this. But now I would probably go with a.
>>>
>>> Thanks.
>>>
>>> On Friday, February 2, 2018 at 7:50:53 PM UTC+5:30, Andy wrote:
>>>>
>>>> a) Maybe its an option to put the foreign key to the other model? This
>>>> way you dont need to make a join to find out if there is a relation.
>>>>
>>>> b) Save the existing ralation status to model A
>>>>
>>>> c) cache the A.objects.filter(bs__isnull=False) query
>>>>
>>>> But apart from that i fear you cannot do much more, since this is just
>>>> a DB and not a Django ORM question.
>>>>
>>>>
>>>> Am Freitag, 2. Februar 2018 14:47:45 UTC+1 schrieb Web Architect:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I am trying to optimise Django queries on my ecommerce website. One of
>>>>> the fundamental query is where I have no clue how to make efficient. It
>>>>> could be trivial and probably been known long time back. But I am new to
>>>>> Django and would appreciate any help. This is primarily for one to many or
>>>>> many to many relations.
>>>>>
>>>>> Following is an example scenario:
>>>>> (Please pardon my syntax as I want to put across the concept and not
>>>>> the exact django code unless it's really needed):
>>>>>
>>>>> Model A:
>>>>>
>>>>> class A(models.Model):
>>>>> # Fields of model A
>>>>>
>>>>> Model B (which is related to A with foreign key):
>>>>>
>>>>> class B(models.Model):
>>>>> a = models.ForeignKey('A', related_name='bs')
>>>>>
>>>>> Now I would like to find out all As for which there is atleast one b.
>>>>> The only way I know is as follows:
>>>>>
>>>>> A.objects.filter(bs__isnull=False)
>>>>>
>>>>> But the above isn't an optimal way as with large of records in A and
>>>>> B, the above takes lot of time. It gets more inefficient if it's a many to
>>>>> many relationship.
>>>>>
>>>>> Could anyone please let me know the most efficient way to use django
>>>>> queryset for the above scenario?
>>>>>
>>>>> Thanks.
>>>>>
>>>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at https://groups.google.com/group/django-users.
>> To view this discussion on the web visit https://groups.google.com/d/ms
>> gid/django-users/73ed5ff7-d4db-4057-a812-01c82bf08cf3%40googlegroups.com
>> <https://groups.google.com/d/msgid/django-users/73ed5ff7-d4db-4057-a812-01c82bf08cf3%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit https://groups.google.com/d/
> msgid/django-users/CALn3ei1Q2p31NrcdYC-2EPfH78kBxCXZPZKW1e6k%
> 2BqCTuUgYDw%40mail.gmail.com
> <https://groups.google.com/d/msgid/django-users/CALn3ei1Q2p31NrcdYC-2EPfH78kBxCXZPZKW1e6k%2BqCTuUgYDw%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CACMDPqGT1Yk128hv37cGbbZ2KGvToF9qtQhupdWwNSvEe6m3%3DQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
