Are there some other solution of it ?
The more than 2000 queries is from the get_latest_text() function, it
will be generate one query each plan. I have more than 2000 plans so
it will generate more than 2000 queries.
def get_latest_text(self):
try:
tptxt = PlanTexts.objects \
.filter(plan_id=self.plan_id) \
.order_by('-plan_text_version')[0]
return tptxt
except:
return None
The most important thing is there are a lot of plans with the same
plan_id but different plan_text_version in table plan_texts. It's
really difficult to model it.
I known QuerySet have a extra function but it looks only used for get
something's count or other thing not important.
http://docs.djangoproject.com/en/dev/ref/models/querysets/#extra-select-none-where-none-params-none-tables-none-order-by-none-select-params-none
On Mar 9, 6:21 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:
> On Mon, 2009-03-09 at 02:47 -0700, K*K wrote:
> > I'm porting a old system to Django architecture. The database schema
> > can not be modified because the data was existed. The new system will
> > running with the old system at the beginning but will run standalone
> > in the future.
>
> > The system have two tables, which one is plan and another is
> > plan_text, plan_text contain two fields with plan_id and
> > plan_text_version, plan_id is a ForeignKey pointed to table plan,
> > plan_text_version is IntegerField. After editing the plan text it will
> > generate a new record with the plan_id and the last plan_text_version
> > + 1.
>
> > So when to show to plan and plan texts should show plan info first and
> > then compare plan_texts's plan_id with plan's plan_id and the max of
> > plan_texts_versions. It's easy to implement with SQL sub query
> > function.
>
> > But I got a big performance issue in the database modeling. The
> > requirements need to show the plan info and plan text in the same
> > time.
>
> > My temporary solution is made a get_latest_text() function in the
> > model file but when the user open the plans page it will generate more
> > than the count of plans.
>
> > The code is following:
>
> > # models.py
>
> > class Plans(models.Model):
> > plan_id = models.AutoField(max_length=11, primary_key=True)
> > name = models.CharField(max_length=255)
> > create_date = models.DateTimeField(auto_now_add=True)
> > author = models.ForeignKey(Accounts)
>
> > class Meta:
> > db_table = u'plans'
>
> > def get_latest_text(self):
> > try:
> > tptxt = PlanTexts.objects \
> > .filter(plan_id=self.plan_id) \
> > .order_by('-plan_text_version')[0]
> > return tptxt
> > except:
> > return None
>
> > class PlanTexts(models.Model):
> > plan = models.ForeignKey(Plans, primary_key=True)
> > plan_text_version = models.IntegerField(max_length=11,
> > db_index=True) # also a primary key; Django can't cope with this
> > editor = models.ForeignKey(Accounts, db_column='who')
> > modify_date = models.DateTimeField(auto_now_add=True)
> > plan_text = models.TextField(blank=True)
>
> > class Meta:
> > db_table = u'plan_texts'
>
> > # End of models.py
>
> > It looks OK when the database server and web server in the same
> > machine, but really slow when the web server to call a remote database
> > server because the plans is more than 2000, so it generate more than
> > 2000 database queries calls.
>
> I don't see that this is going to be solved by multi-column primary keys
> (you cannot have *multiple* primary keys on a database table, but a
> primary key can span multiple columns). Django 1.1 won't support
> multi-column keys, however.
>
> Where are the 2000 queries coming from? Is this only retrieval? Why
> can't you retrieve all the records at once? If it's an update, why do
> all 2000 records need to be updated?
>
> Regards,
> Malcolm
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
