This is what you said "There is a Model A with 10000 records. Just a simple queryset - A.objects.all() is resulting in CPU hitting almost 100%. Is there a way to optimize this? But why would such a query result in high CPU Usage?"
By that, I assume that you opened a shell and executed "A.objects.all()" If you have other code that is being executed (your for loop or something) you better post it to clarify your question. On 3/10/17, Web Architect <pinak...@gmail.com> wrote: > Hi, > > Thanks for your response. > > Well, its not just the queryset but when the query triggered by an if > condition or a for loop - thats what I meant. I was doing some basic > performance check as well while working on the report generation - I > observed the issue. There's no manager other than the default one. The CPU > usage spikes and it takes some time before the Objects are created. > > But when you say not all objects are created at one go, you mean they can > be deferred? You mean the query results are stored in the memory till each > record is accessed or something using the objects? Could you please > clarify? > > Thanks. > > On Friday, March 10, 2017 at 5:31:14 PM UTC+5:30, Vijay Khemlani wrote: >> >> There is something wrong in your setup >> >> I can query a 400.000 item table in less than a second with the >> typical "Model.objects.all()". Django does not convert all of the >> entries into objects just by that query. >> >> You don't have any managers, or anything that can result in a >> side-effect beyond the query itself? >> >> >> >> On 3/10/17, James Bennett <ubern...@gmail.com <javascript:>> wrote: >> > If all you need is to export data from your database (with or without >> > transforming it along the way) to a CSV, using the normal QuerySet >> methods >> > is probably the wrong approach; you don't need model objects to do that. >> > >> > Some options include: >> > >> > * Use raw SQL to query for the data and push it to CSV (also, some >> > databases natively understand how to export query results to CSV) >> > * Use the values() or values_list() methods to use lighter-weight basic >> > >> > Python data structures (dictionaries and lists) instead of model objects >> > >> > * If you *must* instantiate model objects, use the iterator() method to >> > >> > avoid keeping them around in-memory, and look at server-side cursors as >> > >> an >> > option >> > * If you're fetching related data, make sure you're eager-loading the >> > relations to avoid N+1 problems. >> > >> > >> > On Fri, Mar 10, 2017 at 3:06 AM, Web Architect <pina...@gmail.com >> <javascript:>> wrote: >> > >> >> Hi James, >> >> >> >> Thanks for your response. Melvyn also posed a similar point of not >> >> loading >> >> the whole records. >> >> >> >> But all the records are needed for reporting purposes - where the data >> >> >> is >> >> read from the DB and a csv report is created. I am not quite an expert >> >> >> on >> >> Django but I am not sure if there is a better way to do it. >> >> >> >> The scenario is as follows to make it clearer: >> >> >> >> Ours is an ecommerce site built on Django. Our admin/accounting team >> >> needs >> >> to download reports now and then. We have a Django model for the line >> >> items >> >> purchased. Now there could be 10k line items sold and each line items >> are >> >> associated with other models like payments, shipments etc which is a >> >> complex set of relations. >> >> >> >> We do not yet have a sophisticated reporting mechanism but was working >> >> >> on >> >> building a simplistic reporting system on Django. But I am finding >> issues >> >> with scaling up - as reported with CPU Usage and the amount of time >> >> taken. >> >> If there is a way to optimise this - would be great otherwise we might >> >> >> >> not >> >> have to look for standard methods of reporting tools. >> >> >> >> Would appreciate suggestions/advices on the above. >> >> >> >> Thanks, >> >> >> >> On Friday, March 10, 2017 at 2:52:50 PM UTC+5:30, James Schneider >> wrote: >> >>> >> >>> >> >>> >> >>> On Mar 9, 2017 9:37 PM, "Web Architect" <pina...@gmail.com> wrote: >> >>> >> >>> Would like to further add - the python CPU Usage is hitting almost 100 >> >>> >> >>> %. >> >>> When I run a Select * query on Mysql, its quite fast and CPU is >> normal. >> >>> I >> >>> am not sure if anything more needs to be done in Django. >> >>> >> >>> >> >>> Ironically, things being done in Django is the reason for your CPU >> >>> utilization issue in the first place. >> >>> >> >>> Calling a qs.all() is NOT the same as a SELECT * statement, even more >> >>> >> so >> >>> when speaking to the scale of query that you mention. >> >>> >> >>> Your SQL query is simply listing data in a table. A very easy thing to >> >>> >> >>> do, hence the reason it runs quickly. >> >>> >> >>> The qs.all() call is also running the same query (probably). However, >> >>> >> in >> >>> addition to pulling all of the data, it is performing a transformation >> >>> >> >>> of >> >>> that data in to Django model objects. If you are pulling 10K items, >> then >> >>> Django is creating 10K objects, which is easily more intensive than a >> >>> >> >>> raw >> >>> SQL query, even for simple model objects. >> >>> >> >>> In general, there's usually no practical reason to ever pull that many >> >>> >> >>> objects from a DB for display on a page. Filter down to a reasonable >> >>> number >> >>> (<100 for almost all sane cases) or implement a paging system to limit >> >>> >> >>> returned results. It's also probably using a ton of RAM only to be >> >>> immediately thrown away at the end of the request. Browsers will >> >>> disintegrate trying to render that many HTML elements simultaneously. >> >>> >> >>> >> >>> Look at implementing a paging system, possibly through Django's >> built-in >> >>> mechanism, or something like Datatables and the infinite scroll >> plugin. >> >>> >> >>> https://docs.djangoproject.com/en/dev/topics/pagination/ >> >>> >> >>> https://datatables.net/extensions/scroller/ >> >>> >> >>> -James >> >>> >> >> -- >> >> 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 django-users...@googlegroups.com <javascript:>. >> >> To post to this group, send email to django...@googlegroups.com >> <javascript:>. >> >> 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/566cf05e-babf-456c-91fa-a698f7c7537d% >> 40googlegroups.com >> >> < >> https://groups.google.com/d/msgid/django-users/566cf05e-babf-456c-91fa-a698f7c7537d%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 django-users...@googlegroups.com <javascript:>. >> > To post to this group, send email to django...@googlegroups.com >> <javascript:>. >> > 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/CAL13Cg9k7DhTKEgYi%3DB59NLZck2oP6gzu_Bjckz%3D_wqYwYA%3D3Q%40mail.gmail.com. >> >> >> > 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 django-users+unsubscr...@googlegroups.com. > To post to this group, send email to django-users@googlegroups.com. > 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/165b08da-cc22-4dcb-90a8-6b35d02ac73f%40googlegroups.com. > 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 django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. 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/CALn3ei3L0qc11vo6m0OJKA43UGc%3D0gvzs4fz7naRhV45X54kJg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
