On Mon, Mar 9, 2009 at 9:22 PM, K*K <xuqingku...@gmail.com> wrote:
>
> 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
> >
>
If you can write a more efficient SQL query than what DJango's ORM can
preform, just write the SQL, Django's ORM aims to work for about 80% of
cases(and probably does a little better) but sometimes you need to write raq
SQL.
Alex
--
"I disapprove of what you say, but I will defend to the death your right to
say it." --Voltaire
"The people's good is the highest law."--Cicero
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
