[sqlalchemy] Apparently redundant subqueryloads with single table inheritance
I am subquery loading some related properties for a polymorphic inheritance
use case similar to the script attached. SQLA seems to be issuing several
extra queries that I didn't expect and don't think it needs. In the
attached example, I expected a total of 4 queries issued for the
session.query().get(), but I there are 11 queries instead, most of them
redundant.
Any ideas?
Thanks,
Kent
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/vC69eQMhv10J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
# I expected 4 queries total issued for the get() query below, but I get 11 instead.
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///')
metadata = MetaData(engine)
Session = sessionmaker(bind=engine)
class Employee(object):
pass
class Manager(Employee):
pass
class Engineer(Employee):
pass
class SupportTech(Employee):
pass
class Role(object):
pass
employees_table = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('type', String(1), nullable=False),
Column('data', String(50)),
Column('manager_id', Integer, ForeignKey('employees.employee_id')),
)
roles_table = Table('roles', metadata,
Column('employee_id', Integer, ForeignKey('employees.employee_id'), primary_key=True),
Column('role', String(50), primary_key=True),
)
mapper(Role, roles_table)
employee_mapper = mapper(Employee, employees_table,
polymorphic_on=employees_table.c.type,
polymorphic_identity='E',
properties = {
'roles': relationship(Role),
'staff': relationship(Employee,
cascade='save-update,merge,refresh-expire,delete,delete-orphan',
single_parent=True,
backref=backref('manager', remote_side=[employees_table.c.employee_id])),
}
)
manager_mapper = mapper(Manager, inherits=employee_mapper,
polymorphic_identity='M')
engineer_mapper = mapper(Engineer, inherits=employee_mapper,
polymorphic_identity='E')
supporttech_mapper = mapper(SupportTech, inherits=employee_mapper,
polymorphic_identity='S')
session = Session()
metadata.create_all()
try:
m=Manager()
m.employee_id = 1
session.add(m)
session.flush()
e=Engineer()
e.employee_id = 2
e.manager_id = 1
session.add(e)
session.flush()
s=SupportTech()
s.employee_id = 3
s.manager_id = 1
session.add(s)
session.flush()
session = Session()
engine.echo = 'debug'
e = session.query(Employee).options(
subqueryload(Employee.staff),
subqueryload(Employee.roles),
subqueryload(Employee.staff,Employee.roles)).get(1)
finally:
engine.echo = False
session.rollback()
metadata.drop_all()
Re: [sqlalchemy] Apparently redundant subqueryloads with single table inheritance
On Jun 5, 2012, at 4:21 PM, Kent wrote: I am subquery loading some related properties for a polymorphic inheritance use case similar to the script attached. SQLA seems to be issuing several extra queries that I didn't expect and don't think it needs. In the attached example, I expected a total of 4 queries issued for the session.query().get(), but I there are 11 queries instead, most of them redundant. this is ticket 2480 and has been fixed for several weeks. 0.7.8 is due as we have a memory leak issue but i wont have time for a release until perhaps late weekend. Any ideas? Thanks, Kent -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/vC69eQMhv10J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. subquery_polymorphic.py -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Apparently redundant subqueryloads with single table inheritance
Thank you! On 6/5/2012 4:41 PM, Michael Bayer wrote: On Jun 5, 2012, at 4:21 PM, Kent wrote: I am subquery loading some related properties for a polymorphic inheritance use case similar to the script attached. SQLA seems to be issuing several extra queries that I didn't expect and don't think it needs. In the attached example, I expected a total of 4 queries issued for the session.query().get(), but I there are 11 queries instead, most of them redundant. this is ticket 2480 and has been fixed for several weeks. 0.7.8 is due as we have a memory leak issue but i wont have time for a release until perhaps late weekend. Any ideas? Thanks, Kent -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/vC69eQMhv10J. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. subquery_polymorphic.py -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
