"In the above, the CPU was hitting almost 100% and was taking almost a second"
So, your query was resolved in a second, that's normal, also the spike
in cpu usage because... the cpu is working...
Your problem seems to be in your for loop
for a in As:
retrieve other related data from associated models.
Write Data in a csv report file
if A has 10.000 records then it is reasonable that this part explodes,
especially if you are querying the database in each iteration, also
you are writing multiple csv files?
Can't you find a way to prefetch all the records of the associated
models that you are going to need in a single query before the for
loop and store it in a dictionary or something?
On 3/14/17, Web Architect <pinak...@gmail.com> wrote:
> Hi Daniel,
>
> Thanks for the suggestion. Would look into django-import-export.
>
> Thanks.
>
> On Saturday, March 11, 2017 at 6:29:57 PM UTC+5:30, Daniel Hepper wrote:
>>
>> In additions to the suggestions you already received from others, have a
>> look at django-import-export. It allows you to easily export data in
>> various formats.
>>
>> Hope that helps,
>> Daniel Hepper
>> https://consideratecode.com
>>
>> On Friday, March 10, 2017 at 12:06:13 PM UTC+1, Web Architect 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/29c0528f-57a7-4f73-be77-3158fe6e61dd%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/CALn3ei3ZGNWJ5Ab8t5ZWmUhOtQ-iDFA_R%2BUZD3sBaQuWm%3DyR%3Dg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
