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