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 <[email protected]> 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 <[email protected] <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 <[email protected] >> <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" <[email protected]> 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 [email protected] <javascript:>. >> >> To post to this group, send email to [email protected] >> <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 [email protected] <javascript:>. >> > To post to this group, send email to [email protected] >> <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 [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/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 [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/CALn3ei3L0qc11vo6m0OJKA43UGc%3D0gvzs4fz7naRhV45X54kJg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
