Hi,
I see that you had quite a bunch of m2m keys.
Have you tried recent version of Django (1.4+) with prefetch_related() [1] ?
[1]
<https://docs.djangoproject.com/en/1.4/ref/models/querysets/#prefetch-related>
22.1.2013 13:57, Matt Andrews kirjoitti:
Hi Jani,
I made a StackOverflow post last year with an example of the ORM stuff I
tried and the poor queries it produced:
http://stackoverflow.com/questions/5843457/django-objects-all-making-360-queries-how-can-i-optimise-this-manytomany
There's also this discussion about how using the same queryset in two
places in the template caused Django to request its data twice:
http://stackoverflow.com/questions/9447053/best-way-to-slice-a-django-queryset-without-hitting-the-database-more-than-once
(this
was easily fixed, but again, not very intuitive and frustrated me)
I don't have the data to hand currently but I also remember seeing weird
things happen where queries would end with stuff like "... LIMIT
234423445" (or some crazy number which I'd never entered and was orders
of magnitude bigger than the number of rows in the table).
I'm aware these are probably edge cases that are down to my own novice
status, but even using things like select_related(), it wasn't doing
what I wanted. I just felt it easier to use my existing SQL (I'm
converting a PHP app over to Django) and I'm not concerned about
database portability (switching to postgres or whatever).
Nik: just realised I missed your final question. For the SQL posted
above, the numbers are approximately: 12,000 rows in the `news` table,
maybe 10 `news_category` rows, about 100 `writers` and around 3000
`images`. All properly indexed and with sensible column types.
On Tuesday, January 22, 2013 10:53:40 AM UTC, Jani Tiainen wrote:
Hi,
From your raw SQL I saw you're doing few joins. So I suppose you do
quite a few foreign key fetches.
You didn't mention anything how you originally tried to solve case with
ORM. Could you please publish what you had when things were slow?
22.1.2013 12:26, Matt Andrews kirjoitti:
> Hi Nik,
>
> Thanks - I do feel like by circumventing the ORM I've just "given
up"
> and perhaps I'll reconsider -- none of my queries are particularly
> "specialist" (as the sample above indicates) - I just found Django
> generating odd things.
>
> To answer your questions:
>
> 1. Yes, reloading the page sees the same time for the queries (it
just
> feels as though the entire process takes a second or two to
start, which
> is perhaps not related to SQL itself).
>
> 2. I believe so, yes (it's shared hosting...).
>
> If you're curious, you can see a sample of the app at
> http://beta.scenepointblank.com <http://beta.scenepointblank.com>
(obviously you won't see the SQL, but
> the "delay" between pages, even though these pages are all cached
for
> 2hrs+, is partly my concern here).
>
> On Tuesday, January 22, 2013 4:24:09 AM UTC, Nikolas
Stevenson-Molnar wrote:
>
> Hi Matt,
>
> Firstly, I encourage you to have another crack a the ORM. It can
> certainly seem a bit aggravating at times if you're coming
from a
> SQL mindset, but really pays off down the road in terms of
> maintainability and readability. Typically you should only
need raw
> queries in Django in cases where you have super-specialized
(that
> uses views or non-standard functions) queries or need some
specific
> optimization. If there's really no way to perform many of your
> "day-to-day" queries with the ORM then that's an indication
that a
> different database design may fit your data model better. I
> understand that you may have a unique situation, but I just
wanted
> to throw that out there as I personally find the ORM to be a
huge
> time saver.
>
> Now, with that out of the way... a couple of considerations:
1) you
> say it's a slow "startup"; if you refresh the page do the
queries
> run just as slow the second time around? and 2) are your
Django app
> and phpMyAdmin running on the same machine? If not, could
transit
> time be an issue?
>
> Finally, can you give an idea about the size of the tables in
> question? How many rows in each?
>
> _Nik
>
> On 1/21/2013 3:25 PM, Matt Andrews wrote:
>> Hi all,
>>
>> Fairly new to Django. I ended up pulling out all of the
>> ORM-generated queries and writing my own SQL directly (I got
fed
>> up trying to work out how to achieve the kind of things I
needed
>> without Django adding in extra joins or unintended WHERE
clauses
>> etc). All my app's SQL uses cursor.execute() and the
>> dictfetchall() method as referenced here
>>
<https://docs.djangoproject.com/en/dev/topics/db/sql/#django.db.models.Manager.raw
<https://docs.djangoproject.com/en/dev/topics/db/sql/#django.db.models.Manager.raw>>.
>>
>>
>> I've found that my app incurs a couple of seconds load time in
>> production, with CPU time at 2532ms and overall time 4684ms
>> (according to the debug toolbar). I'm seeing 8 SQL queries take
>> 380ms, and each one seems to be several times slower when
made by
>> Django versus hitting the database through phpMyAdmin or
>> something: eg, this query:
>>
>> SELECT * FROM news
>> JOIN news_categories ON news.news_category_id =
>> news_categories.id <http://news_categories.id>
<http://news_categories.id>
>> LEFT JOIN writers ON news.writer_id = writers.id
<http://writers.id>
>> <http://writers.id>
>> LEFT JOIN images ON news.image_id = images.id
<http://images.id> <http://images.id>
>> ORDER BY news.is_sticky DESC, news.date_posted DESC
>> LIMIT 10
>>
>>
>> This takes 14.8ms when run in phpMyAdmin (against the
production
>> database) but Django reports it as 85.2ms. The same ratios are
>> true for all my other queries.
>>
>> All I can think of is the note on the dictfetchall() method
in the
>> Django docs which describes a "small performance hit". Is
this it?!
>>
>> I've profiled the app too, although I'm a bit hazy about
what it
>> all means. Here's a dump of the result:
>> http://pastebin.com/raw.php?i=UHE9edVC
<http://pastebin.com/raw.php?i=UHE9edVC>
>> <http://pastebin.com/raw.php?i=UHE9edVC
<http://pastebin.com/raw.php?i=UHE9edVC>> (this is from running on
>> my local server rather than production but performance is
broadly
>> similar).
>>
>> Can anyone help me? I realise I've perhaps gone off-piste by
>> writing raw SQL but I feel it was justified.
>>
>> thanks,
>> Matt
>>
--
Jani Tiainen
- Well planned is half done and a half done has been sufficient
before...
--
You received this message because you are subscribed to the Google
Groups "Django users" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-users/-/H1vytr_AuFIJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
--
Jani Tiainen
- Well planned is half done and a half done has been sufficient before...
--
You received this message because you are subscribed to the Google Groups "Django
users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
