I've got a situation where SQLAlchemy (1.0.14, accessing MySQL via PyMySQL
0.7.6) seems to be issuing unnecessary SELECTs to an Association Object
table for instances that are already in the identity map.
Hopefully I've extracted the essentials of my setup in the pseudo-example
below. StatusBlock uses a composite foreign key constraint into AssocInfo,
which is the association object; MyThings are a main object that contain
many StatusBlock instances via the "statuses" relationship.
Things work fine functionally, but it seems that every time a StatusBlock
lazyloads and requires a specific AssocInfo instance, say with composite
key (100,1000), a SELECT is issued for it, even if that AssocInfo instance
is already in the identity map from having been loaded by another
StatusBlock.
You can see at the comment "#TROUBLE HERE" below, when SQLAlchemy issues a
SELECT to lazyload the StatusBlock from the second thing, it also issues a
SELECT to get the (100,1000) AssocInfo record, even though it is in the
identity map, having just been loaded for the first StatusBlock at the
"FIRST LOAD" comment. It is almost as if it thinks the (100,1000)
AssocInfo record it needs for the second StatusBlock instance is different
from the one it has from the first. The problem comes if I loop through a
couple thousand MyThing records, all indirectly accessing that same
AssocInfo record, there's a couple thousand unnecessary SELECTs issued,
which are probably contributing to a slowness I'm trying to speed up.
Is there something goofy about the way I've got this set up, or my
understanding of the identity map and SELECT issuing, that I could change
to eliminate the unnecessary SELECTs? I'm checking to see if they might be
some side effect of other things my full setup has (trying to model an
existing system), and I'll see if I can set up a test system with the exact
setup below to make sure the SELECTS are really generated in this
simplified case.
# A1 and A2 are the left/right classes/tables that AssocInfo associates to
class AssocInfo(MyAlchemyBase): # our Association Object
__tablename__ = "assoc"
key1 = Column(Integer, ForeignKey("A1.id"), primary_key=True)
key2 = Column(Integer, ForeignKey("A2.id"), primary_key=True)
id = Column(Integer)
# ... more fields
itsA1 = relationship(A1, backref="assocs")
itsA2 = relationship(A2)
class StatusBlock(MyAlchemyBase):
__table_args__ = (ForeignKeyConstraint(['k1', 'k2'], ['assoc.key1',
'assoc.key2']), {})
id = Column(Integer, primary_key=True)
thing_id = Column(Integer, ForeignKey('thing.id'))
# (k1,k2) is the composite key into AssocInfo, as per __table_args__
k1 = Column(Integer, ForeignKey("A1.id")
k2 = Column(Integer)
itsAssoc = relationship(AssocInfo)
class MyThings(MyAlchemyBase):
__tablename__ = "thing"
id = Column(Integer, primary_key=True)
statuses = relationship("StatusBlock", collection_class=
attribute_mapped_collection('k1'))
__mapper_args__ = {'polymorphic_identity': 0} # hopefully unrelated
polymorphic stuff
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
session = getASession()
thing1 = session.query(MyThings).get(1) # assume thing1 has a related
StatusBlock with k1=100 and k2=1000
print thing1.statuses[100].itsAssoc # FIRST LOAD
# lazyload will do a SELECT on "assoc" table to get (100,1000) instance
# confirm that the AssocInfo instance (100,1000) is in map
print session.identity_map.items() # yup
thing2 = session.query(MyThings).get(2) # different thing instance, but
also has a StatusBlock with k1=100 and k2=1000
print session.identity_map.items() # confirm that (AssocInfo, (100,1000))
is still in map
print thing2.statuses[100].itsAssoc # "TROUBLE HERE"
# this shouldn't have had to SELECT to "assoc" to get (100,1000) record,
but it did
"""just like it did as part of the first load, something like:
INFO:sqlalchemy.engine.base.Engine:SELECT AssocInfo.key1 AS
AssocInfo_key1...
FROM AssocInfo
WHERE AssocInfo.key2 = %(param_1)s AND AssocInfo.key1 = %(param_2)s
INFO:sqlalchemy.engine.base.Engine:{u'param_1': 1000, u'param_2': 100}
DEBUG:sqlalchemy.engine.base.Engine:Col (u'AssocInfo_key1',
u'AssocInfo_key2', ...)
DEBUG:sqlalchemy.engine.base.Engine:Row (100, 1000, ...)
"""
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
