Adrian, disregard my previous question (you're using your own paginator). Does your paginator use len(set) or does it use set.count() to determine the number of items? You should find that the latter has much better performance.
-- Scott On Wed, May 14, 2008 at 11:36 AM, Adrian R. <[EMAIL PROTECTED]> wrote: > > On 14 Mai, 16:36, Jeremy Dunck <[EMAIL PROTECTED]> wrote: > > On May 14, 2008, at 9:26, "Adrian R." <[EMAIL PROTECTED]> > > wrote: > > > > > the problem is as faras I understood it, that the view is first > > > processed completely in the > > > database before it returns the result-slice. So it takes as long as it > > > takes without a LIMIT or WHERE. That's the reason why I try to use raw > > > SQL because then the execution of the statement is stopped when the > > > LIMIT is reached as far as I'm understanding > > > > In this 'raw SQL', are you still calling the view? If so, your theory > > of limiting being the cause doesn't make sense. If not, you could > > just do raw SQL through django.db.connection.cursor(). > > Okay, I'm sorry, I think I didn't specify the problem enough: > In this context I'm talking about a database view which was created on > the database server (MySQL). To show the performance problem I'll show > you some of the result times as the (approximately) appear when they > are executed in the MySQL Query Browser. > > "SELECT * FROM view" ~4 sec. > "SELECT * FROM view WHERE State='FAILED'" ~4 sec. > "SELECT * FROM view LIMIT 50" ~4 sec. > > So I come to the result that MySQL first generates a TEMPTABLE (MERGE > not possible due to utilization of GROUP BY) and then filters/limits > the TEMPTABLE. > > When I use the SQL which created the view and append WHERE or LIMIT > the times are much better: > > "SELECT * FROM table1 LEFT JOIN table2 on [...] LEFT JOIN [...] GROUP > BY column" ~4 sec. - logical because its the complete listing (like > above) > "SELECT * FROM table1 LEFT JOIN table2 on [...] LEFT JOIN [...] WHERE > State='FAILED' GROUP BY column" ~ 0.13 s. > "SELECT * FROM table1 LEFT JOIN table2 on [...] LEFT JOIN [...] GROUP > BY column LIMIT 50" ~ 0.007 s. > > The last two cases are the important ones because the display is > always limited/filteres. > > On 14 Mai, 16:53, "Scott Moonen" <[EMAIL PROTECTED]> wrote: > > On Wed, May 14, 2008 at 10:26 AM, Adrian R. < > [EMAIL PROTECTED]> > > wrote: > > > > > On 14 Mai, 16:18, "Scott Moonen" <[EMAIL PROTECTED]> wrote: > > > > Adrian, are you displaying all 30,000 entries on the same page? Or > are > > > you > > > > using some sort of pagination? > > > > > No, they aren't on the same page and I'm using pagination (and the > > > result can be filtered by different fields), but the problem is as far > > > as I understood it, that the view is first processed completely in the > > > database before it returns the result-slice. So it takes as long as it > > > takes without a LIMIT or WHERE. That's the reason why I try to use raw > > > SQL because then the execution of the statement is stopped when the > > > LIMIT is reached as far as I'm understanding MySQL;) > > > > Adrian, that's interesting. Are you using a QuerySetPaginator and not a > > Paginator? The QuerySetPaginator should be smart enough to ensure that > only > > the given slice is retrieved from the database. > > The object are received by calling > > MyViewModel.objects.select_related().extra( > select=additionalSelects, > where=filterOptions > )pagination.GetOffset():pagination.GetLimit()] > > where MyViewModel is a model to access the database-view (does not > create tables) and map them to django-objects. The pagination-class > was written by me and the GetOffset() and GetLimit() methods are > returning some integer values (which are 10, 25, 50 or 100 apart from > each other). > > So that leads me to my decision to put this SQL-code into a model so > that the query is performed faster than by the view. Okay, it's > somehow like converting a MySQL-problem to a Django-problem, but this > is the best solution as far as I know. > > Okay, now to the reason why I don't want to use the > django.db.connection.cursor(): > Parts of my code use (as seen above) the extra() and filter()-methods > and I originally didn't want to rewrite the whole QuerySet-Class and > put it into my model;) > > - Adrian > > > -- http://scott.andstuff.org/ | http://truthadorned.org/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-developers@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-developers?hl=en -~----------~----~----~----~------~----~------~--~---
