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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
