Query Optimization (Huge Data in the Database)
Hi Experts,
I am trying to execute a Django query (ORM) but my query is taking more
than 4 mins as the table have huge records.
Here are the details
1> I am using left outer join to get count from other table
2>Django query
subject = Table1.objects.all().annotate(numItems=Count('table2.field')).
order_by('name')
3>Here I am converting the above query set to Raw sql
SELECT "table1"."id", "table1"."created_at", "table1"."name", COUNT("table2"
."id") AS "numItems" FROM "table1"
LEFT OUTER JOIN "table2" ON ("table1"."id" = "table2"."subdomain_id") GROUP
BY "table1"."id" ORDER BY "table1"."name" ASC
and I am returning response to template index.html
response = render(request, 'search_home.html', context)
My template looks like this
{% if context.subject %}
{% for item in context.subject %}
{{ item }}
{% endfor %}
{% endif %}
Please let me know how can i reduce the performance time or how can i
optimize the query .
Thanks & Regards,
Karthik
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/ab90edb2-7d5e-4e01-9fad-0c71cb916a67o%40googlegroups.com.
Django Query optimizing performance (HUGE DATA)
Hi Experts,
I am trying to execute the below query and the query is taking more than 5
minutes.
Here are the details
Model.py
class Url(models.Model):
subdomain = models.ForeignKey(Subdomain, null=True, blank=True,
related_name='url_subdomain', on_delete=models.SET_NULL,db_index=True)
full_url = models.CharField(max_length=1000, unique=True, db_index=True)
class Meta:
ordering = ['full_url']
def __str__(self):
return self.full_url
1>Django query ORM
subdomains = Subdomain.objects.all().annotate(numItems=Count('url_subdomain'
)).order_by('name')
2>SQL Query
SELECT "urls_subdomain"."id", "urls_subdomain"."created_at",
"urls_subdomain"."name", COUNT("urls_url"."id") AS "numItems"
FROM "urls_subdomain" LEFT OUTER JOIN
"urls_url" ON ("urls_subdomain"."id" = "urls_url"."subdomain_id") GROUP BY
"urls_subdomain"."id" ORDER BY "urls_subdomain"."name" ASC
3>Templates
{% if subdomains %}
Subdomain
{% for item in subdomains %}
item
{% endfor %}
{% endif %}
Please let me know how can i optimize the preformance Thanks & Regards,
Karthik
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/996db826-fe18-4557-977b-58493c1f9481o%40googlegroups.com.
Re: Django Query optimizing performance (HUGE DATA)
My Apologies .
On Sunday, July 19, 2020 at 3:06:57 AM UTC-4, Aymeric Augustin wrote:
>
> Hello,
>
> I think you've found the wrong mailing list for this post. This mailing
> list is for discussing the development of Django itself, not for support
> using Django. This means the discussions of bugs and features in Django
> itself, rather than in your code using it. People on this list are unlikely
> to answer your support query with their limited time and energy.
>
> For support, please follow the "Getting Help" page:
> https://docs.djangoproject.com/en/3.0/faq/help/ . This will help you find
> people who are willing to support you, and to ask your question in a way
> that makes it easy for them to answer.
>
> Thanks for your understanding!
>
> --
> Aymeric.
>
>
>
> On 19 Jul 2020, at 05:38, karthik challa > wrote:
>
> Hi Experts,
>
> I am trying to execute the below query and the query is taking more than 5
> minutes.
>
> Here are the details
>
> Model.py
>
> class Url(models.Model):
> subdomain = models.ForeignKey(Subdomain, null=True, blank=True,
> related_name='url_subdomain', on_delete=models.SET_NULL,db_index=True)
> full_url = models.CharField(max_length=1000, unique=True, db_index=True)
> class Meta:
> ordering = ['full_url']
>
> def __str__(self):
> return self.full_url
>
> 1>Django query ORM
>
> subdomains = Subdomain.objects.all().annotate(numItems=Count(
> 'url_subdomain')).order_by('name')
>
> 2>SQL Query
>
> SELECT "urls_subdomain"."id", "urls_subdomain"."created_at",
> "urls_subdomain"."name", COUNT("urls_url"."id") AS "numItems"
> <http://localhost:8000/urls/search/#> FROM "urls_subdomain" LEFT OUTER
> JOIN "urls_url" ON ("urls_subdomain"."id" = "urls_url"."subdomain_id")
> GROUP BY "urls_subdomain"."id" ORDER BY "urls_subdomain"."name" ASC
>
> 3>Templates
>
> {% if subdomains %}
>
> Subdomain
>
> {% for item in subdomains %}
> item
> {% endfor %}
>
>
>
> {% endif %}
>
> Please let me know how can i optimize the preformance Thanks & Regards,
> Karthik
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-d...@googlegroups.com .
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/996db826-fe18-4557-977b-58493c1f9481o%40googlegroups.com
>
> <https://groups.google.com/d/msgid/django-developers/996db826-fe18-4557-977b-58493c1f9481o%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/fbbfa8d0-0763-4924-abc7-39c4c6197f68o%40googlegroups.com.
