On 08/28/2016 04:29 PM, YKdvd wrote:
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.
I always need a simple test to do anything; it is vastly more expedient
for me to just explain why a succinct test program is doing what it
does, rather than to try to understand the problem from incomplete
fragments.
Here's a test that I think captures what you're doing. It does not emit
SQL for the second lazyload. See if you can work with this test case to
illustrate what's different in your case - but note the most important
thing for "lazy load does a GET" is that the relationship is a simple
many-to-one. Sometimes the detection of "simple many to one" is thrown
off especially if you have an explicit primaryjoin condition.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
assoc = relationship("AB")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
class AB(Base):
__tablename__ = 'ab'
a_id = Column(ForeignKey('a.id'), primary_key=True)
b_id = Column(ForeignKey('b.id'), primary_key=True)
a = relationship("A")
b = relationship("B")
class C(Base):
__tablename__ = 'c'
__table_args__ = (
ForeignKeyConstraint(['k1', 'k2'], ['ab.a_id', 'ab.b_id']), {})
id = Column(Integer, primary_key=True)
k1 = Column(Integer)
k2 = Column(Integer)
assoc = relationship("AB")
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
a, b = A(id=100), B(id=1000)
ab = AB(a=a, b=b)
c1 = C(id=1, assoc=ab)
c2 = C(id=2, assoc=ab)
s.add_all([a, b, ab, c1, c2])
s.commit()
s.close() # clears everything
c1 = s.query(C).get(1)
print "EMITS LAZYLOAD:"
assoc = c1.assoc # note we keep a strong reference here
c2 = s.query(C).get(2)
print "DOES NOT EMIT LAZYLOAD:"
assert c2.assoc is assoc
output (after the create table):
2016-08-28 16:41:25,183 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-08-28 16:41:25,184 INFO sqlalchemy.engine.base.Engine INSERT INTO a
(id) VALUES (?)
2016-08-28 16:41:25,184 INFO sqlalchemy.engine.base.Engine (100,)
2016-08-28 16:41:25,185 INFO sqlalchemy.engine.base.Engine INSERT INTO b
(id) VALUES (?)
2016-08-28 16:41:25,185 INFO sqlalchemy.engine.base.Engine (1000,)
2016-08-28 16:41:25,186 INFO sqlalchemy.engine.base.Engine INSERT INTO
ab (a_id, b_id) VALUES (?, ?)
2016-08-28 16:41:25,186 INFO sqlalchemy.engine.base.Engine (100, 1000)
2016-08-28 16:41:25,187 INFO sqlalchemy.engine.base.Engine INSERT INTO c
(id, k1, k2) VALUES (?, ?, ?)
2016-08-28 16:41:25,188 INFO sqlalchemy.engine.base.Engine ((1, 100,
1000), (2, 100, 1000))
2016-08-28 16:41:25,188 INFO sqlalchemy.engine.base.Engine COMMIT
2016-08-28 16:41:25,189 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-08-28 16:41:25,190 INFO sqlalchemy.engine.base.Engine SELECT c.id
AS c_id, c.k1 AS c_k1, c.k2 AS c_k2
FROM c
WHERE c.id = ?
2016-08-28 16:41:25,190 INFO sqlalchemy.engine.base.Engine (1,)
EMITS LAZYLOAD:
2016-08-28 16:41:25,192 INFO sqlalchemy.engine.base.Engine SELECT
ab.a_id AS ab_a_id, ab.b_id AS ab_b_id
FROM ab
WHERE ab.a_id = ? AND ab.b_id = ?
2016-08-28 16:41:25,192 INFO sqlalchemy.engine.base.Engine (100, 1000)
2016-08-28 16:41:25,193 INFO sqlalchemy.engine.base.Engine SELECT c.id
AS c_id, c.k1 AS c_k1, c.k2 AS c_k2
FROM c
WHERE c.id = ?
2016-08-28 16:41:25,193 INFO sqlalchemy.engine.base.Engine (2,)
DOES NOT EMIT LAZYLOAD:
|
# A1 and A2 are the left/right classes/tables that AssocInfo associates to
classAssocInfo(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)
classStatusBlock(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)
classMyThings(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
printthing1.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
printsession.identity_map.items()# yup
thing2 =session.query(MyThings).get(2)# different thing instance, but
also has a StatusBlock with k1=100 and k2=1000
printsession.identity_map.items()# confirm that (AssocInfo, (100,1000))
is still in map
printthing2.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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
