[SQL] Query on full lesson data run very frequently in production #106

New issue

Closed

opened 2021-01-19 11:47:05 +01:00 by nik · 5 comments

nik commented

2021-01-19 11:47:05 +01:00

Owner

The following query runs a few million times within a few days in production:

SELECT "chronos_lessonperiod"."id",
       "chronos_lessonperiod"."site_id",
       "chronos_lessonperiod"."extended_data",
       "chronos_lessonperiod"."lesson_id",
       "chronos_lessonperiod"."period_id",
       "chronos_lessonperiod"."room_id",
       "chronos_lesson"."id",
       "chronos_lesson"."site_id",
       "chronos_lesson"."extended_data",
       "chronos_lesson"."validity_id",
       "chronos_lesson"."subject_id",
       "chronos_validityrange"."id",
       "chronos_validityrange"."site_id",
       "chronos_validityrange"."extended_data",
       "chronos_validityrange"."school_term_id",
       "chronos_validityrange"."name",
       "chronos_validityrange"."date_start",
       "chronos_validityrange"."date_end",
       "core_schoolterm"."id",
       "core_schoolterm"."site_id",
       "core_schoolterm"."extended_data",
       "core_schoolterm"."name",
       "core_schoolterm"."date_start",
       "core_schoolterm"."date_end",
       "chronos_subject"."id",
       "chronos_subject"."site_id",
       "chronos_subject"."extended_data",
       "chronos_subject"."short_name",
       "chronos_subject"."name",
       "chronos_subject"."colour_fg",
       "chronos_subject"."colour_bg",
       "chronos_timeperiod"."id",
       "chronos_timeperiod"."site_id",
       "chronos_timeperiod"."extended_data",
       "chronos_timeperiod"."validity_id",
       "chronos_timeperiod"."weekday",
       "chronos_timeperiod"."period",
       "chronos_timeperiod"."time_start",
       "chronos_timeperiod"."time_end",
       "chronos_room"."id",
       "chronos_room"."site_id",
       "chronos_room"."extended_data",
       "chronos_room"."short_name",
       "chronos_room"."name"
FROM "chronos_lessonperiod"
INNER JOIN "chronos_lesson" ON ("chronos_lessonperiod"."lesson_id" = "chronos_lesson"."id")
LEFT OUTER JOIN "chronos_validityrange" ON ("chronos_lesson"."validity_id" = "chronos_validityrange"."id")
LEFT OUTER JOIN "core_schoolterm" ON ("chronos_validityrange"."school_term_id" = "core_schoolterm"."id")
INNER JOIN "chronos_subject" ON ("chronos_lesson"."subject_id" = "chronos_subject"."id")
INNER JOIN "chronos_timeperiod" ON ("chronos_lessonperiod"."period_id" = "chronos_timeperiod"."id")
LEFT OUTER JOIN "chronos_room" ON ("chronos_lessonperiod"."room_id" = "chronos_room"."id")
WHERE ("chronos_lessonperiod"."site_id" = $1
       AND "chronos_lessonperiod"."lesson_id" = $2
       AND ("chronos_lessonperiod"."extended_data" -> $3) = $4)
ORDER BY "chronos_validityrange"."date_start" ASC,
         "chronos_timeperiod"."weekday" ASC,
         "chronos_timeperiod"."period" ASC,
         "chronos_subject"."name" ASC,
         "chronos_subject"."short_name" ASC
LIMIT $5

We should find out where it coems from and if it has to run so often.

The following query runs a few million times within a few days in production: ```sql SELECT "chronos_lessonperiod"."id", "chronos_lessonperiod"."site_id", "chronos_lessonperiod"."extended_data", "chronos_lessonperiod"."lesson_id", "chronos_lessonperiod"."period_id", "chronos_lessonperiod"."room_id", "chronos_lesson"."id", "chronos_lesson"."site_id", "chronos_lesson"."extended_data", "chronos_lesson"."validity_id", "chronos_lesson"."subject_id", "chronos_validityrange"."id", "chronos_validityrange"."site_id", "chronos_validityrange"."extended_data", "chronos_validityrange"."school_term_id", "chronos_validityrange"."name", "chronos_validityrange"."date_start", "chronos_validityrange"."date_end", "core_schoolterm"."id", "core_schoolterm"."site_id", "core_schoolterm"."extended_data", "core_schoolterm"."name", "core_schoolterm"."date_start", "core_schoolterm"."date_end", "chronos_subject"."id", "chronos_subject"."site_id", "chronos_subject"."extended_data", "chronos_subject"."short_name", "chronos_subject"."name", "chronos_subject"."colour_fg", "chronos_subject"."colour_bg", "chronos_timeperiod"."id", "chronos_timeperiod"."site_id", "chronos_timeperiod"."extended_data", "chronos_timeperiod"."validity_id", "chronos_timeperiod"."weekday", "chronos_timeperiod"."period", "chronos_timeperiod"."time_start", "chronos_timeperiod"."time_end", "chronos_room"."id", "chronos_room"."site_id", "chronos_room"."extended_data", "chronos_room"."short_name", "chronos_room"."name" FROM "chronos_lessonperiod" INNER JOIN "chronos_lesson" ON ("chronos_lessonperiod"."lesson_id" = "chronos_lesson"."id") LEFT OUTER JOIN "chronos_validityrange" ON ("chronos_lesson"."validity_id" = "chronos_validityrange"."id") LEFT OUTER JOIN "core_schoolterm" ON ("chronos_validityrange"."school_term_id" = "core_schoolterm"."id") INNER JOIN "chronos_subject" ON ("chronos_lesson"."subject_id" = "chronos_subject"."id") INNER JOIN "chronos_timeperiod" ON ("chronos_lessonperiod"."period_id" = "chronos_timeperiod"."id") LEFT OUTER JOIN "chronos_room" ON ("chronos_lessonperiod"."room_id" = "chronos_room"."id") WHERE ("chronos_lessonperiod"."site_id" = $1 AND "chronos_lessonperiod"."lesson_id" = $2 AND ("chronos_lessonperiod"."extended_data" -> $3) = $4) ORDER BY "chronos_validityrange"."date_start" ASC, "chronos_timeperiod"."weekday" ASC, "chronos_timeperiod"."period" ASC, "chronos_subject"."name" ASC, "chronos_subject"."short_name" ASC LIMIT $5 ``` We should find out where it coems from and if it has to run so often.

nik commented

2021-01-22 17:11:48 +01:00

Author

Owner

changed title from [SQL] Query on full lesson data run very frequently in produc{-it-}on to [SQL] Query on full lesson data run very frequently in produc{+ti+}on

changed title from **[SQL] Query on full lesson data run very frequently in produc{-it-}on** to **[SQL] Query on full lesson data run very frequently in produc{+ti+}on**

Rows
Columns