Производительность SQLAlchemy "Многие-ко-многим"
4 javex [2013-03-05 05:49:00]
У меня есть отношение к базе данных с ассоциацией Many-To-Many, но сама таблица ассоциаций содержит множество атрибутов, к которым нужно получить доступ, поэтому я сделал три класса:
class User(Base):
id = Column(Integer, primary_key=True)
attempts = relationship("UserAttempt", backref="user", lazy="subquery")
class Challenge(Base):
id = Column(Integer, primary_key=True)
attempts = relationship("UserAttempt", backref="challenge", lazy='subquery')
class UserAttempt(Base):
challenge_id = Column(Integer, ForeignKey('challenge.id'), primary_key=True)
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
Это, конечно, упрощенный случай, когда я оставил другие атрибуты, которые мне нужны для доступа. Цель здесь состоит в том, что каждый User
может попытаться выполнить любое число Challenge
s, а следовательно, таблицу UserAttempt
, которая описала один конкретный пользователь, работающий с одним вызовом.
Теперь проблема: когда я запрашиваю для всех пользователей, а затем смотрю на каждую попытку, я в порядке. Но когда я смотрю на вызов этой попытки, он взрывается в многочисленных подзапросах. Конечно, это плохо для производительности.
То, что я действительно хочу от SQLAlchemy, - это вытащить все (или все релевантные) вызовы сразу, а затем связать их с соответствующими попытками. Это не имеет большого значения, если все проблемы вытащить или только сделать, которые имеют фактическую ассоциацию позже, так как это число проблем составляет только между 100-500.
Мое решение прямо сейчас на самом деле не очень изящно: я пытаюсь разобрать все соответствующие попытки, проблемы и пользователи, а затем связать их вручную: Проведите все попытки и назначьте добавить вызов и пользователя, затем добавьте вызов и пользователя в попытка. Это кажется мне жестоким решением, которое не должно быть необходимым.
Однако каждый подход (например, изменение "ленивых" параметров, измененные запросы и т.д.) приводит к запросам от сотен до тысяч. Я также пытался написать простые SQL
запросы, которые принесли бы мои желаемые результаты и придумали что-то по строкам SELECT * FROM challenge WHERE id IN (SELECT challenge_id FROM attempts)
, и это сработало хорошо, но я не могу перевести его на SQLAlchemy
Заранее благодарю вас за любые рекомендации, которые вы можете предложить.
python sql sqlalchemy
1 ответ
10 Решение zzzeek [2013-03-05 06:58:00]
То, что я действительно хочу от SQLAlchemy, - это вытащить все (или все релевантные) вызовы сразу, а затем связать их с соответствующими попытками. Это не имеет большого значения, если все проблемы вытащили или только сделали, которые имеют фактическую связь позже,
Сначала вы хотите сначала снять эту директиву "lazy = 'subquery" из отношения(); фиксируя отношения, чтобы всегда загружать все, почему вы получаете взрыв запросов. В частности, здесь вы получаете эту задачу → пытается загружать именно для каждой lazyload UserAttempt- > Challenge, поэтому вы можете составить самую худшую возможную комбинацию загрузки здесь:).
С учетом этого существует два подхода.
Следует иметь в виду, что ассоциация "один-к-одному" в обычном случае извлекается из сеанса в памяти сначала первичным ключом, а если присутствует, SQL не испускается. Поэтому я думаю, что вы могли бы получить именно тот эффект, который кажется вам описывающим, используя часто используемую технику:
all_challenges = session.query(Challenge).all()
for user in some_users: # however you got these
for attempt in user.attempts: # however you got these
do_something_with(attempt.challenge) # no SQL will be emitted
Если вы хотите использовать вышеприведенный подход с помощью "Выбрать * от вызова, где id (выберите challenge_id из попытки)":
all_challenges = session.query(Challenge).\
filter(Challenge.id.in_(session.query(UserAttempt.challenge_id))).all()
хотя это, скорее всего, более эффективно, чем JOIN:
all_challenges = session.query(Challenge).\
join(Challenge.attempts).all()
или DISTINCT, я думаю, что объединение вернет тот же вызов.и как он появляется в UserAttempt:
all_challenges = session.query(Challenge).distinct().\
join(Challenge.attempts).all()
Другим способом является более эффективная загрузка. вы можете запросить кучу пользователей/попыток/проблем в одном запросе, который испустит три оператора SELECT:
users = session.query(User).\
options(subqueryload_all(User.attempts, UserAttempt.challenge)).all()
или потому, что UserAttempt- > Challenge многозначно, соединение может быть лучше:
users = session.query(User).\
options(subqueryload(User.attempts), joinedload(UserAttempt.challenge)).all()
только из UserAttempt:
attempts = session.query(UserAttempt).\
options(joinedload(UserAttempt.challenge)).all()