N+1 Query in `user_show_logs_json`

[agdsn-sentry]

Sentry Issue: PYCROFT-9C

Offending Spans	db - SELECT "user".account_id AS user_account_i...

[lukasjuhrich]

These 6 lazy fetches cause ~300ms in the linked event. This is not negligible!

pycroft/web/blueprints/user/__init__.py

Lines 407 to 420 in 968f2ad

	def user_show_logs_json(user_id: int, logtype: str = "all") -> ResponseReturnValue:
	user = get_user_or_404(user_id)
	if not is_log_type(logtype):
	flash(f"{logtype!r} ist kein valider logtyp", "error")
	abort(404)
	logs = _iter_user_logs(user, logtype)
	def sort_key(l: LogTableRow) -> int | None:
	return l.created_at.timestamp
	return TableResponse[LogTableRow](
	items=sorted(logs, key=sort_key, reverse=True)
	).model_dump()

pycroft/web/blueprints/user/__init__.py

Lines 394 to 402 in 968f2ad

	def _iter_user_logs(user: User, logtype: LogType) -> t.Iterator[LogTableRow]:
	if logtype in ["user", "all"]:
	yield from (format_user_log_entry(e) for e in user.log_entries)
	if logtype in ["room", "all"] and user.room:
	yield from (format_room_log_entry(e) for e in user.room.log_entries)
	if logtype in ["tasks", "all"]:
	yield from (format_task_log_entry(e) for e in user.task_log_entries)
	if logtype in ["hades", "all"]:
	yield from formatted_user_hades_logs(user)

pycroft/web/blueprints/helpers/log.py

Lines 28 to 42 in 968f2ad

	def format_log_entry(entry: LogEntry, log_type: LogType) -> LogTableRow:
	return LogTableRow(
	created_at=datetime_format(entry.created_at, formatter=datetime_filter),
	user=UserColResponseNative(
	title=entry.author.name,
	href=url_for("user.user_show", user_id=entry.author.id),
	),
	message=Message.from_json(entry.message).localize(),
	type=log_type,
	)
	format_user_log_entry = partial(format_log_entry, log_type='user')
	format_task_log_entry = partial(format_log_entry, log_type='task')
	format_room_log_entry = partial(format_log_entry, log_type='room')

It seems to me that these fetches come from entry.author, where entry is in user.log_entries, user.room.log_entries, user.task_log_entries.
We should try to

• joinedload() the user.room
• selectinload() the relevant logentries
• joinedload() the logentries' author
  or something along these lines (measure!)