Thank you Michael - I had completely missed the backref full load.
On Dec 5, 5:27 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> hi martin -
>
> the issue is that each Tag object contains a collection of 1000
> employees on it, and when you make an assignment in the forwards
> direction (i.e. employee.tag.append(sometag)), the corresponding
> reverse relation needs to be fully loaded and then updated according
> to backref semantics. since you're using eager loading by default
> between employees and tags, there is a load of 20,000 rows each time
> an uninitialized "tags.employees" collection is touched.
>
> To prevent the backref from being unnecessarily loaded, and since it
> is a large collection, you should use a "dynamic" collection for the
> reverse:
>
> mapper(Employee, employees, properties={
> 'tags': relation(Tag,
> secondary=employeesTags,backref=backref('employees', lazy='dynamic'),
> lazy=False)
>
> })
>
> mapper(Tag, tags)
>
> the "employees" collection on Tag is now a filterable Query object
> which only queries when read from, and you'll see that the time goes
> down to nothing. you can also append and delete from a "dynamic"
> collection like a regular list.
>
> large collection techniques are discussed
> at:http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio...
>
> we do have a ticket in trac to try improving upon backrefs to not load
> unloaded collections in any case, this is ticket #871.
>
> On Dec 5, 12:07 pm, Martin Pengelly-Phillips
>
> <[EMAIL PROTECTED]> wrote:
> > Hello again,
>
> > I have recently noticed that a particular assignment seems to be
> > taking a "relatively" long time.
> > Not being a database expert I am confused as to whether the last
> > assignment 'person.tags = tags' should be so slow when referencing
> > existing tags that are used by other entities - it seems to try and
> > get a list of all employees that use the given tag and then spends the
> > time doing something with the resulting set, but why?
>
> > Test case below.
>
> > If the slow assignment is expected do you have any advice on how to
> > speed up such a statement?
>
> > Thank you in advance,
>
> > Martin
>
> > ----------------------------------------------------------------------------------
>
> > import os, datetime, time
> > from sqlalchemy import *
> > from sqlalchemy.orm import *
>
> > file = '/tmp/test.db'
> > if os.path.isfile(file): os.remove(file)
> > engine = create_engine('sqlite:///%s' % file, echo=True)
> > metadata = MetaData()
> > Session = scoped_session(sessionmaker(autoflush=True,
> > transactional=False, bind=engine))
> > mapper = Session.mapper
>
> > # Classes
> > #----------------------------------------------
> > class Employee(object):
> > def __init__(self, name=None):
> > self.name = name
>
> > def __repr__(self):
> > return '%s:%s' % (self.id, self.name)
>
> > class Tag(object):
> > def __init__(self, label):
> > self.label = label
>
> > # Setup tables
> > #----------------------------------------------
> > employees = Table('employees', metadata,
> > Column('id', Integer, primary_key=True),
> > Column('name', String, nullable=False,
> > default='bob'),
> > Column('dob', DateTime, nullable=False,
> > default=datetime.datetime.now),
> > )
>
> > tags = Table('tags', metadata,
> > Column('id', Integer, primary_key=True),
> > Column('label', String, nullable=False),
> > )
>
> > employeesTags = Table('employeesTags', metadata,
> > Column('employee_id', Integer,
> > ForeignKey('employees.id')),
> > Column('tag_id', Integer,
> > ForeignKey('tags.id')),
> > )
>
> > # Mappers
> > #----------------------------------------------
> > mapper(Employee, employees, properties={
> > 'tags': relation(Tag, secondary=employeesTags,
> > backref='employees', lazy=False)})
>
> > mapper(Tag, tags)
>
> > # Test
> > #----------------------------------------------
> > metadata.create_all(engine)
> > session = Session()
> > session.begin()
>
> > tags = []
> > for i in xrange(20):
> > tag = Tag(str(datetime.datetime.now()))
> > tags.append(tag)
>
> > for i in xrange(1000):
> > p = Employee('john%d' % i)
> > p.tags = tags
>
> > session.commit()
> > session.clear()
>
> > session.begin()
> > tags = session.query(Tag).all()[:2]
> > person = Employee('bob')
>
> > started = time.time()
> > person.tags = tags
> > print 'Took:', time.time()-started
>
> > session.commit()
> > session.clear()
> > Session.remove()
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
