Re: [Sqlalchemy-users] 0.2.2 slowdown

[Michael Bayer]

the object relational mapper is not optimized for batching.  this is mostly a product of the instrumentation SQLAlchemy applies to the attributes on objects, so that changes to attributes are tracked, and i see you are using the "threadlocal" mod below so it is also going to automatically add new objects to the Session, which then incurs more overhead because of cascading rules being fired off as well as each object is added. this is not to say it will always be impossible to do efficient batched inserts of large numbers of objects, but it would require the construction of some alternate methods of doing things internally that might require some flags or switches to be set for a particular session, for example.  SA's focus within 0.1, 0.2 (plenty more version numbers to go....) has been on being as correct and bug free as possible.  (im a believer in construct first, optimize second...SA is complicated enough without being constructed in a "pre-optimized" way). the "attributes" package is responsible for most of the instrumentation of attributes and ive been considering ways that it could be essentially redesigned to do what it does in a more lightweight fashion, although im not sure if the total length of codepaths would ultimately go down in such a case. also, the "session" idea plus all the hibernate-style cascading concepts are totally new to 0.2 and those features are still in their infancy; i havent put those through code profiling yet. there is a test script called "test/masscreate.py" in 0.1 and "test/perf/masscreate.py" in 0.2 which tests just the attribute package alone, which I spent a lot of time with profiling in order to speed up the attribute instrumentation.  On my machine the 0.2 version is only 20% slower, so its possible the session stuff is adding the overhead. if you want to insert 1000 rows really fast, at the moment you might consider dropping into SQL expressions rather than ORM usage to do it, pending some more maturity of the 0.2 series. On Jun 7, 2006, at 10:38 PM, Robert Taylor wrote: Hi All, I'm in the process of upgrading an application that was using SA 0.1.5 to SA 0.2.2.  One section of the code that is structurally similar to the following code is now running many times slower under .2 than it was with .1. On my fast laptop with 2G RAM this test code takes 52 seconds to create 1000 rows, most of which is spent in the nested loops creating the objects. I'm using Postgres 8.1.4 under Win XP. Perhaps my approach is flawed? I'd be glad to hear of a better way to do this sort of thing, if anyone has any ideas. Best, Bob import sqlalchemy.mods.threadlocal from sqlalchemy import * dbEngine = create_engine('postgres://[EMAIL PROTECTED]:5432/mydb') metadata = BoundMetaData(dbEngine) t1s = Table( 't1s', metadata,     Column( 'id', Integer, primary_key=True)) t2s = Table( 't2s', metadata,     Column( 'id', Integer, primary_key=True),     Column( 't1id', Integer, ForeignKey("t1s.id"), nullable=True )) t3s = Table( 't3s', metadata,     Column( 'id', Integer, primary_key=True),     Column( 't2id', Integer, ForeignKey("t2s.id"), nullable=True )) t4s = Table( 't4s', metadata,     Column( 'id', Integer, primary_key=True),     Column( 't3id', Integer, ForeignKey("t3s.id"), nullable=True )) class T1( object ): pass class T2( object ): pass class T3( object ): pass class T4( object ): pass assign_mapper( T1, t1s ) assign_mapper( T2, t2s )       assign_mapper( T3, t3s )       assign_mapper( T4, t4s )       T1.mapper.add_property( 't2s', relation(T2, backref="t1")) T2.mapper.add_property ( 't3s', relation(T3, backref="t2")) T3.mapper.add_property( 't4s', relation(T4, backref="t3")) metadata.drop_all() metadata.create_all() print "start"        o1 = T1() for i2 in range(10):     o2 = T2()     o1.t2s.append( o2 )         for i3 in range( 10 ):         o3 = T3()         o2.t3s.append( o3 )                 for i4 in range( 10 ):             o3.t4s.append ( T4() )             print i2, i3, i4             print "flushing" objectstore.flush() print "done"             _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users