Yeah, I wrote the SQL before, but the requirements writer is a ex-
Turbogears developer, and he said it can be implemented with
SQLAlchemy. -_-#
So the SQL code was deleted by him in early days.
On Mar 10, 10:25 am, Alex Gaynor <alex.gay...@gmail.com> wrote:
> 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-sele...
>
> > 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
-~----------~----~----~----~------~----~------~--~---
