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_relation_largecollections
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
-~----------~----~----~----~------~----~------~--~---
