N+1 Query in user.member_request_edit

[agdsn-sentry]

This causes 41(!!) lazy fetches on every request, adding up to ~120ms.

Sentry Issue: PYCROFT-9D

Offending Spans	db - SELECT building.site_id AS building_site_i...

[lukasjuhrich]

I'm really not sure why this happens. We have precisely 41 buildings, so my guess would be that it's the QuerySelectField. However, this basically only uses a Building.q.all() – so why would sqla lazy-load happen that fetches every building given the building id?

Needs investigation.

[lukasjuhrich]

relevant frontend endpoint:

pycroft/web/blueprints/user/__init__.py

Lines 1138 to 1194 in 968f2ad

	@bp.route("member-request/<int:pre_member_id>/edit", methods=['GET', 'POST'])
	def member_request_edit(pre_member_id: int) -> ResponseReturnValue:
	prm = get_pre_member_or_404(pre_member_id)
	form = PreMemberEditForm(
	name=prm.name,
	login=prm.login,
	building=prm.room.building if prm.room else None,
	level=prm.room.level if prm.room else None,
	room_number=prm.room.number if prm.room else None,
	email=prm.email,
	move_in_date=prm.move_in_date,
	birthdate=prm.birthdate,
	person_id=prm.swdd_person_id,
	)
	if form.validate_on_submit():
	old_email = prm.email
	prm.name = form.name.data
	prm.login = form.login.data
	prm.email = form.email.data
	prm.move_in_date = form.move_in_date.data
	prm.birthdate = form.birthdate.data
	if prm.swdd_person_id != form.person_id.data:
	if form.person_id.data is None:
	prm.swdd_person_id = form.person_id.data
	else:
	tenancy = Tenancy.q.filter_by(person_id=form.person_id.data).first()
	if tenancy is not None:
	prm.swdd_person_id = form.person_id.data
	else:
	form.person_id.errors.append("Zu der angegebenen Debitorennummer konnten keine Verträge gefunden werden!",)
	room = Room.q.filter_by(building=form.building.data, level=form.level.data,
	number=form.room_number.data).first()
	prm.room = room
	session.session.commit()
	if old_email != prm.email:
	send_confirmation_email(prm)
	flash("Es wurde eine neue Bestätigungsmail verschickt!", "warning")
	session.session.commit()
	flash("Änderungen wurden gespeichert.", "success")
	return render_template("user/member_request_edit.html",
	page_title="Mitgliedschaftsanfrage Bearbeiten",
	form=form,
	prm=prm,
	is_adult=prm.is_adult)

[lukasjuhrich]

It seems that the slow transactions are the POST ones, GET goes through rather quickly.

[lukasjuhrich]

I think validation might be the culprit, specifically https://github.com/wtforms/wtforms-sqlalchemy/blob/09b3d4745ec98d6d8f769f6794bc217c63d81946/wtforms_sqlalchemy/fields.py#L184-L193 (or the building_query it calls).