Multiplication of SQL queries for one django objects.get request

Question

I'm working on Django, an I have a ClientPartner class as Model :

class ClientPartner(BusinessObject):
    charge_account  = models.ForeignKey("ClientAccount", on_delete=models.DO_NOTHING, null=True, blank=True, related_name="partners_as_charge")
    client_account  = models.ForeignKey("ClientAccount", on_delete=models.DO_NOTHING, null=True, blank=True, related_name="partners_as_client")
    legal_entity    = models.ForeignKey("legal_entity.LegalEntity", on_delete=models.CASCADE, related_name="partners")
    partner         = models.ForeignKey("legal_entity.LegalEntity", on_delete=models.CASCADE, related_name="client_legal_entities")
    product_account = models.ForeignKey("ClientAccount", on_delete=models.DO_NOTHING, null=True, blank=True, related_name="partners_as_product")
    vendor_account  = models.ForeignKey("ClientAccount", on_delete=models.DO_NOTHING, null=True, blank=True, related_name="partners_as_vendor")

and I query the table as shown below :

lo_partnership = ClientPartner.objects.get(legal_entity=self.owner, partner=io_vendor)

I use django standard logs and when I run this line I have the SQL queries below :

DEBUG 2021-05-27 21:38:42,237 utils 21776 22144 (0.016) SELECT "client_clientpartner"."id", "client_clientpartner"."creation_date", "client_clientpartner"."creation_user_id", "client_clientpartner"."deleted", "client_clientpartner"."deletion_date", "client_clientpartner"."deletion_user_id", "client_clientpartner"."update_date", "client_clientpartner"."update_user_id", "client_clientpartner"."charge_account_id", "client_clientpartner"."client_account_id", "client_clientpartner"."legal_entity_id", "client_clientpartner"."partner_id", "client_clientpartner"."product_account_id", "client_clientpartner"."vendor_account_id" FROM "client_clientpartner" WHERE ("client_clientpartner"."legal_entity_id" = 2 AND "client_clientpartner"."partner_id" = 935) LIMIT 21; args=(2, 935)
DEBUG 2021-05-27 21:38:42,243 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id" FROM "legal_entity_legalentity" WHERE "legal_entity_legalentity"."id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,248 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,256 utils 21776 22144 (0.016) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,262 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id" FROM "legal_entity_legalentity" WHERE "legal_entity_legalentity"."id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,267 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,275 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,282 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id" FROM "legal_entity_legalentity" WHERE "legal_entity_legalentity"."id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,287 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,296 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,305 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id" FROM "legal_entity_legalentity" WHERE "legal_entity_legalentity"."id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,312 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,320 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 935 LIMIT 21; args=(935,)

I don't understand why I have 13 SQL queries. I only want the first one. And the others are mainly duplicates. Can anyone explain what's happening ?

Then I do that (above logs are generated before running this code):

if lo_partnership.vendor_account_id is None or lo_partnership.charge_account_id is None:
    # Not configured, use default waiting account
    ev_excl_tax_account = self.waiting_account
    ev_incl_tax_account = self.waiting_account
else:
    ev_excl_tax_account = lo_partnership.charge_account.code
    # 3. Check if vendor account is an auxiliary account
    if lo_partnership.vendor_account.general_account_id is not None:
        ev_incl_tax_account = lo_partnership.vendor_account.general_account.code
        ev_auxiliary_account = lo_partnership.vendor_account.code
    else:
        ev_incl_tax_account = lo_partnership.vendor_account.code

Update

As requested, I isolated the function :

@action(methods=['get'], detail=False)
def testing(self, io_request, pk=None):
    """
    Method to get test objects.get outside main code
    """
    from django.db import connection # to check all queries
    connection.queries
    tmp = len(connection.queries) # before the get : 1 query
    result = ClientPartner.objects.select_related('legal_entity').get(legal_entity__id=2, partner__id=379)
    tmp2 = len(connection.queries) # after the get : 17 queries
    break_point_stoper = tmp2 # breakpoint in debug to stop

By calling this function with insomnia, I have 15 queries with select_related and 13 without. Somes queries are still duplicates.

user_4477659 · Answer

The reason why you get so many queries is because Django uses lazy QuerySets. This means that the SQL will not get executed until you use it. Except for some cases, like the get() method, because this method already returns an object and not a QuerySet. But the same principle applies.

Because of this, depending on what you use later on it may execute one or more additional queries (that is why I asked you what do you do after the query in a comment).

Try this to see how it works:

from django import db

lo_partnership = ClientPartner.objects.get(legal_entity=self.owner, partner=io_vendor)
# Check the last SQL executed
print(db.connection.queries[-1:])
# You should see something similar to:
# SELECT {fields} FROM {ClientPartner table} WHERE legal_entity.id = {id} AND partner.id={another id}
# I.e. a simple SQL on a single table

# Now use that same object to access data from a related table:
print(lo_partnership.vendor_account.code)
# The previous query did not return any of this info, so Django will need to execute a new Query.
# So, again, check for the last SQL executed:
print(db.connections.queries[-1:])
# You will see Django performed a new query to fetch that value.
# SELECT {fields} FROM {ClientAccount table} where id = {the id}

To avoid this you should use select_related in such a manner:

lo_partnership = ClientPartner.objects.select_related('vendor_account').get(legal_entity=self.owner, partner=io_vendor)
print(db.connections.queries[-1:])
# Now you will see Django is producing an INNER JOIN to return values from the related_table.

So, if later on you want to use data from any of the ForeignKeys in the object, you should add all those FK fields to the select_related method. Otherwise, Django will be executing multiple queries.

This does not explain why you see the exact same SQL executed more than once. Having various fields pointing to the same Models might. I'm not so sure about this, but f you have the same id as product_account and vendor_account (and you access both) it may end up executing the same query twice.

Check Django documentation to learn more about the details of how Django Query system works.

user_14097830 · Answer

after look that quest, have 2 solution

First : use

ClientPartner.objects.select_related('legal_entity', 'partner')

on queryset and get list of object

Second : use legal_entity_id=self.owner_id, partner_id=io_vendor_id (call with id, you can call id with list like this :

ClientPartner.objects.filter(legal_entity_id__in=[owner_id_list],partner_id__in=[io_vendor_id_list])

)

Multiplication of SQL queries for one django objects.get request

2 Answers