Lately I've been noticing severe slowness when instantiating new SA objects. I
can't say for sure, but it seemed to get worse with the 0.3 release.
A test program is attached. Here's some sample output:
Create forward associations
Time to create item 0: 0.06071 sec
Time to create item 1: 0.05977 sec
Time to create item 2: 0.06018 sec
Time to create item 3: 0.05956 sec
Time to create item 4: 0.05969 sec
Time to create item 5: 0.05924 sec
Time to create item 6: 0.05884 sec
Time to create item 7: 0.05995 sec
Time to create item 8: 0.06275 sec
Time to create item 9: 0.06004 sec
Created 610 objects in 0.60074 sec
Create backward associations
Time to create item 0: 0.37236 sec
Time to create item 1: 1.02339 sec
Time to create item 2: 1.67125 sec
Time to create item 3: 2.32101 sec
Time to create item 4: 2.95721 sec
Time to create item 5: 3.61378 sec
Time to create item 6: 4.26243 sec
Time to create item 7: 4.91514 sec
Time to create item 8: 5.55930 sec
Time to create item 9: 6.20524 sec
Created 610 objects in 32.90110 sec OUCH!!
Note: these results were generated on a PIII 733Mhz, so they're a bit slow.
However, I get similar results with an iMac 1.25Ghz.
Notice that the two groups produce exactly the same object graph, but they have
vastly different time characteristics. Something crazy happens when setting the
parent side of a parent/child (one-to-many) relationship (aka "backward
associations"). I profiled the code and it looks like it's getting hung up in
the cascade logic. However, the problem still exists when I remove all cascade
directives (i.e. cascade=None).
~ Daniel
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
from sqlalchemy import *
from timeit import Timer
meta = DynamicMetaData("time_trial")
orders = Table('orders', meta,
Column('id', Integer, Sequence('order_id_seq'), primary_key = True),
)
items = Table('items', meta,
Column('id', Integer, Sequence('item_id_seq'), primary_key = True),
Column('order_id', Integer, ForeignKey(orders.c.id), nullable=False),
)
attributes = Table('attributes', meta,
Column('id', Integer, Sequence('attribute_id_seq'), primary_key = True),
Column('item_id', Integer, ForeignKey(items.c.id), nullable=False),
)
values = Table('values', meta,
Column('id', Integer, Sequence('value_id_seq'), primary_key = True),
Column('attribute_id', Integer, ForeignKey(attributes.c.id),
nullable=False),
)
class Order(object): pass
class Item(object): pass
class Attribute(object): pass
class Value(object): pass
valueMapper = mapper(Value, values)
attrMapper = mapper(Attribute, attributes, properties=dict(
values=relation(valueMapper, cascade="all,delete-orphan",
backref="attribute")
))
itemMapper = mapper(Item, items, properties=dict(
attributes=relation(attrMapper, cascade="all,delete-orphan", backref="item")
))
orderMapper = mapper(Order, orders, properties=dict(
items=relation(itemMapper, cascade="all,delete-orphan", backref="order")
))
class TimeTrial(object):
def create_fwd_assoc(self):
item = Item()
self.order.items.append(item)
for attrid in range(10):
attr = Attribute()
item.attributes.append(attr)
for valueid in range(5):
val = Value()
attr.values.append(val)
def create_back_assoc(self):
item = Item()
item.order = self.order
for attrid in range(10):
attr = Attribute()
attr.item = item
for valueid in range(5):
val = Value()
val.attribute = attr
def run(self, number):
s = create_session()
self.order = order = Order()
s.save(order)
ctime = 0.0
timer = Timer("create_it()", "from __main__ import create_it")
for n in xrange(number):
t = timer.timeit(1)
print "Time to create item %i: %.5f sec" % (n, t)
ctime += t
assert len(order.items) == 10
assert sum(len(item.attributes) for item in order.items) == 100
assert sum(len(attr.values) for item in order.items for attr in
item.attributes) == 500
print "Created 610 objects in %.5f sec" % ctime
if __name__ == "__main__":
tt = TimeTrial()
print "\nCreate forward associations"
create_it = tt.create_fwd_assoc
tt.run(10)
print "\nCreate backward associations"
create_it = tt.create_back_assoc
tt.run(10)
