On May 22, 12:29 pm, [EMAIL PROTECTED] wrote:
> > On May 22, 2007, at 2:12 PM, [EMAIL PROTECTED] wrote:
>
> >> I have a transaction that uses save_or_update() and takes 2-3 minutes
> >> to execute:
>
> >> There's some setup before the transaction (pseudocode for brevity)
>
> >> contact = Contact()
> >> # ...set props on contact
>
> >> contact.user = User()
> >> # ...set props on user
Just to follow up on the issue....I was able to trace the momentary
deadlock to a call to
user.groups.append(Group.get_by(group_name='somegroup')) during the
setup of new contact.user.
The User obj is mapped to tg_user table that was generated by the
TurboGears quickstart w/ identity framework. User.groups is
(backref) relation to tg_group table through user_group:
assign_mapper(session.context, Group, groups_table,
properties=dict(users=relation(User,secondary=user_group_table,
backref='groups')))
So workaround I'm using (for now) is to do a straight insert()
execution into user_group after the transaction.
>
> >> trans = None
> >> try:
> >> trans = session.create_transaction()
> >> # Save the contact
> >> session.save_or_update(contact)
> >> session.flush()
>
> >> invoice = Invoice()
> >> invoice.contact = contact
> >> # ...set other props on invoice
>
> >> session.save_or_update(invoice)
> >> session.flush()
>
> >> session.save_or_update(invoice)
> >> session.flush()
>
> >> trans.commit()
>
> >> except:
> >> if trans != None:
> >> trans.rollback()
> >> raise
>
> >> The save_or_update() on contact takes several minutes! I have MySQL
> >> (using version 1.2.2 of MySQLdb python DBAPI) db with approx
> >> 20,000-30,0000 contact and user records in each table. If I run an
> >> insert manually on the contact and user tables there's no noticeable
> >> overhead.
>
> >> Any clues where I should look to see what's causing the apparent
> >> momentary deadlock. (I did try echo_uow w/ the session and it does
> >> show a lot of misc nodes that have a relation to contact in the
> >> tree...but none of them have an instance instance in the session that
> >> needs to be saved/updated.)
>
> >> Perhaps I need to use SQL api to execute the statements without going
> >> through ORM. But I was hoping that SQLAlchemy would be a bit more
> >> scalable. (When I run this action on db without many records it takes
> >> less than a second to execute, but as db grows in size the performance
> >> degrades.)
>
> > if you need to bulk insert 20K records, no ORM is going to scale to
> > that, you need to use raw SQL.
>
> > other than that, you need to be a little more savvy about how many
> > entities are actually being loaded into memory via the ORM, and how
> > many are actually being processed by the session. this will require
> > proper usage of "lazy=True/lazy=None" as well as an awareness of the
> > operation of cascade rules. all relationships will cascade "save-
> > update" by default, for example. you might want to try setting
> > cascade to None. but youll get better results if you prevent the
> > entire collection of 20K records from ever being loaded, and for that
> > you should consult the documentation as well as the included example
> > script on the subject of "handling large collections".
>
> Thanks for the reply. I should have been more clear, I insert a single
> record into a table that already contains 20K+ records. So this is not a
> bulk insert. I was merely saying that when the table has relatively few
> records, that the sequence of commands executes without any noticeable
> performance hit.
>
> I will look at your suggestion of 'lazy' in the mapper, and the cascade
> rules. Thanks.
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
