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