> > > 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".
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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
