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 <pinak...@gmail.com> 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+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/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+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/CAL13Cg9k7DhTKEgYi%3DB59NLZck2oP6gzu_Bjckz%3D_wqYwYA%3D3Q%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
