When running locally, are you also using a local database instance? IIRC the time that phpMyAdmin reports is the time taken for the database to execute the query, whereas the time reported by the debug toolbar (I /think/) is complete round-trip (send query, execute, return results). So if there is latency between your application and your database, that could account for the discrepancy.
The debug toolbar /does/ add overhead to your request (about doubles the page load time), but the numbers it reports should be more or less accurate (iow a page might take 1s to load with the toolbar, but the toolbar will report 500ms, which is accurate for load-time /without/ the toolbar). _Nik On 1/22/2013 9:26 AM, Matt Andrews wrote: > Hi Nik, > > I see the discrepancy both locally and when deployed, too -- that's > what's so puzzling. If it helps, I'm using MySQL and running it on > Windows locally and under Passenger on my remote Linux server (also on > MySQL). > > Only other thing I can think of is that this "overhead" might > literally be the extra work Django Debug Toolbar does to show queries > etc. Obviously when I turn the toolbar off then I can't see (directly) > the SQL times Django is getting, but could be a factor? Either way, > with the toolbar off on production, there's definitely a slight "lag" > before page requests start to kick in -- maybe this is unrelated to > the SQL, though. > > > On Tuesday, 22 January 2013 16:42:19 UTC, Nikolas Stevenson-Molnar wrote: > > Hi Matt, > > It's unlikely the problem lies with dictfetchall. The "small > performance hit" mentioned by the docs probably refers to the fact > that the function has a time complexity of (number of colulmns) * > (number of rows). Since you're only getting 10 rows back, I can't > see that having an appreciable impact. > > I haven't seen this kind of variation between performing a direct > query, and executing the query as part of a Django request... > especially with such a basic query. Do you see the same difference > in timing locally or is it only when deployed? > > _Nik > > On Jan 22, 2013, at 4:47 AM, Matt Andrews <ma...@mattandrews.info > <javascript:>> wrote: > >> That looks really useful - for future reference, great! Sadly my >> webhost is still bundling Django 1.2.3 :( >> >> In general, though, these issues still come down to the fact that >> a SQL query as executed by Django takes several times longer than >> going directly to the DB. Is this down to my use of >> dictfetchall() or something more low level? >> >> >> On Tuesday, January 22, 2013 12:09:50 PM UTC, Jani Tiainen wrote: >> >> 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 >> >> <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 >> >> <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 >> >> <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/> >> <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> >> >> > >> >> <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 <http://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 <http://writers.id/>> >> > >> <http://writers.id <http://writers.id/>> >> > >> LEFT JOIN images ON news.image_id = >> images.id <http://images.id/> >> > <http://images.id <http://images.id/>> >> <http://images.id <http://images.id/>> >> > >> ORDER BY news.is <http://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>> >> > >> <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 >> <https://groups.google.com/d/msg/django-users/-/H1vytr_AuFIJ>. >> > To post to this group, send email to >> django...@googlegroups.com. >> > To unsubscribe from this group, send email to >> > django-users...@googlegroups.com. >> > For more options, visit this group at >> > http://groups.google.com/group/django-users?hl=en >> <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 view this discussion on the web visit >> https://groups.google.com/d/msg/django-users/-/CxYZKPFVBQgJ >> <https://groups.google.com/d/msg/django-users/-/CxYZKPFVBQgJ>. >> 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 >> <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/-/NvJBbauReMUJ. > 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. -- 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.
