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 use QuerySet to get all of plans and implement the search function,
But the performance issue is always plagued to me. Because the
requirements wrote all of database code should implemented with ORM
code and can not use RAW SQL, and the interactive designer do not want
to make concession. The admin even ask me to change to use SQLAlchemy.
It can't be resolved in Django 1.0 but how about Django 1.1 ?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
