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 > LEFT JOIN writers ON news.writer_id = writers.id > LEFT JOIN images ON news.image_id = 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(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 > > > -- > 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/-/Qiley9RqfngJ. > To post to this group, send email to django...@googlegroups.com<javascript:> > . > To unsubscribe from this group, send email to > django-users...@googlegroups.com <javascript:>. > For more options, visit this group at > http://groups.google.com/group/django-users?hl=en. > > > -- 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/-/mRrUOi_UcswJ. 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.
