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 (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>.
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>
LEFT JOIN writers ON news.writer_id = writers.id
<http://writers.id>
LEFT JOIN images ON news.image_id = 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> (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 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.
