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]> 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]> 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]. >> 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/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]. > 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/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/CALn3ei2JCmSX_cxQZksFy3gFMKJubei0WJTppZEFaMLbvnxG8Q%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
