The attached script generates an inefficient query at the end:
SELECT `People`.friday_id AS `People_friday_id`, `People`.parent_id AS
`People_parent_id`, `People`.user_hash AS `People_user_hash`
FROM `People`
WHERE `People`.friday_id = %s AND `People`.user_hash = `People`.user_hash
Please note the user_hash = user_hash clause being generated there. This
query takes a heavy toll on the datastore and has become a huge bottleneck
in our application.
This is the core ORM configuration:
class User(Base):
hash = Column(String(64), primary_key=True)
class UserMixin(object):
@declared_attr
def user_hash(cls):
return Column(String(64), ForeignKey('User.hash'), primary_key=True)
class People(UserMixin, Base):
friday_id = Column(BigInteger, primary_key=True, nullable=False,
autoincrement=False)
# This line mysteriously fixes the query
#user_hash = Column(String(64), ForeignKey('User.hash'),
# primary_key=True)
parent_id = Column(BigInteger)
@declared_attr
def parent(cls):
return relationship(
'People', remote_side=[cls.friday_id, cls.user_hash],
post_update=True)
__table_args__ = (
ForeignKeyConstraint(
['parent_id', 'user_hash'],
['People.friday_id', 'People.user_hash'],
ondelete='CASCADE'),)
As shown in the comments, if I remove the UserMixin, the query becomes sane.
--
Fayaz Yusuf Khan
Cloud architect, Dexetra SS, India
fayaz.yusuf.khan_AT_gmail_DOT_com, fayaz_AT_dexetra_DOT_com
+91-9746-830-823
--
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.
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, backref, relationship
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base, declared_attr
class TableNameMixin(object):
@declared_attr
def __tablename__(cls):
return cls.__name__
Base = declarative_base(cls=TableNameMixin)
class User(Base):
hash = Column(String(64), primary_key=True)
class UserMixin(object):
@declared_attr
def user_hash(cls):
return Column(String(64), ForeignKey('User.hash'), primary_key=True)
class People(UserMixin, Base):
friday_id = Column(BigInteger, primary_key=True, nullable=False,
autoincrement=False)
# This line mysteriously fixes the query
#user_hash = Column(String(64), ForeignKey('User.hash'), primary_key=True)
parent_id = Column(BigInteger)
@declared_attr
def parent(cls):
return relationship(
'People', remote_side=[cls.friday_id, cls.user_hash],
post_update=True)
__table_args__ = (
ForeignKeyConstraint(
['parent_id', 'user_hash'],
['People.friday_id', 'People.user_hash'],
ondelete='CASCADE'),)
engine = create_engine('mysql://root@localhost', echo=True)
engine.execute('CREATE DATABASE test')
engine.execute('USE test')
Session = sessionmaker()
Session.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all()
session = Session()
hash_string = '0' * 64
session.add(User(hash=hash_string))
session.flush()
session.add(People(user_hash='0'*64, friday_id=1, parent_id=1))
session.add(People(user_hash='0'*64, friday_id=2, parent_id=1))
people = session.query(People).filter_by(friday_id=2).one()
print "After query"
print people.parent
engine.execute('DROP DATABASE test')
