Poor query performance in Instructor section lists

[adamfranco]

Similar to #27, requests that load all of an instructor's sections for all terms result in generally poor performance with the creation of on-disk temporary tables. These generally return in 10-20 seconds when the database is not particularly loaded.

• https://catalog.middlebury.edu/resources/view/resource/resource%2Fperson%2FE2450BA8979164270943B3DF04520BB1
• https://catalog.middlebury.edu/courses/instructorxml/term_catalog/catalog%2FMCUG/instructor/E2450BA8979164270943B3DF04520BB1
• https://catalog.middlebury.edu/offerings/searchxml/catalog/catalog%2FMCUG/term/ANY/instructor/resource%2Fperson%2FB19C3ACFBD148B22EE59295A9EFD6B5F?search=Search

SELECT
SSBSECT_TERM_CODE,
SSBSECT_CRN,
SSBSECT_SUBJ_CODE,
SSBSECT_CRSE_NUMB,
SSBSECT_SEQ_NUMB,
SSBSECT_PTRM_CODE,
SSBSECT_CRSE_TITLE,
SSBSECT_MAX_ENRL,
SSBSECT_ENRL,
SSBSECT_SEATS_AVAIL,
SSBSECT_LINK_IDENT,
SSBDESC_TEXT_NARRATIVE,
SCBCRSE_TITLE,
SCBDESC_TEXT_NARRATIVE,
SSBSECT_CAMP_CODE,
term_display_label,
STVTERM_START_DATE,
STVSCHD_CODE,
STVSCHD_DESC,
SSRMEET_BLDG_CODE,
SSRMEET_ROOM_CODE,
SSRMEET_BEGIN_TIME,
SSRMEET_END_TIME,
SSRMEET_SUN_DAY,
SSRMEET_MON_DAY,
SSRMEET_TUE_DAY,
SSRMEET_WED_DAY,
SSRMEET_THU_DAY,
SSRMEET_FRI_DAY,
SSRMEET_SAT_DAY,
SSRMEET_START_DATE,
SSRMEET_END_DATE,
COUNT(SSRMEET_TERM_CODE) as num_meet,
STVBLDG_DESC,
STVCAMP_DESC,
SCBCRSE_EFF_TERM ,
SCBCRSE_DEPT_CODE,
SCBCRSE_DIVS_CODE,
SSRXLST_XLST_GROUP
FROM
ssbsect_scbcrse_scbdesc
INNER JOIN catalog_term ON SSBSECT_TERM_CODE = catalog_term.term_code
INNER JOIN course_catalog_college ON course_catalog_college.coll_code = SCBCRSE_COLL_CODE
INNER JOIN course_catalog ON course_catalog_college.catalog_id = course_catalog.catalog_id
LEFT JOIN STVTERM ON SSBSECT_TERM_CODE = STVTERM_CODE
LEFT JOIN SSBDESC ON (SSBSECT_TERM_CODE = SSBDESC_TERM_CODE AND SSBSECT_CRN = SSBDESC_CRN)
LEFT JOIN SSRMEET ON (SSBSECT_TERM_CODE = SSRMEET_TERM_CODE AND SSBSECT_CRN = SSRMEET_CRN)
LEFT JOIN STVBLDG ON SSRMEET_BLDG_CODE = STVBLDG_CODE
LEFT JOIN STVSCHD ON SSBSECT_SCHD_CODE = STVSCHD_CODE
LEFT JOIN STVCAMP ON SSBSECT_CAMP_CODE = STVCAMP_CODE
LEFT JOIN SSRXLST ON (SSBSECT_TERM_CODE = SSRXLST_TERM_CODE AND SSBSECT_CRN = SSRXLST_CRN)
LEFT JOIN SYVINST ON (SYVINST_TERM_CODE = SSBSECT_TERM_CODE AND SYVINST_CRN = SSBSECT_CRN)
WHERE
(WEB_ID = 'E2450BA8979164270943B3DF04520BB1')
AND SSBSECT_TERM_CODE IN (
SELECT
term_code
FROM
catalog_term
WHERE
TRUE
)
AND SCBCRSE_COLL_CODE IN (
SELECT
coll_code
FROM
course_catalog_college
WHERE
TRUE
)
AND SSBSECT_SSTS_CODE = 'A'
AND (course_catalog.prnt_ind_to_exclude IS NULL OR SSBSECT_PRNT_IND != course_catalog.prnt_ind_to_exclude)

GROUP BY SSBSECT_TERM_CODE, SSBSECT_CRN
ORDER BY SSBSECT_TERM_CODE DESC, SSBSECT_SUBJ_CODE, SSBSECT_CRSE_NUMB, SSBSECT_PTRM_CODE, SSBSECT_SEQ_NUMB

Profiling these queries indicates that there is file-sort and no index used on the catalog_term table, but adding additional indices didn't seem to help in initial testing, possibly due to the way subqueries are built.

A few initial ideas:

• maybe rework subqueries as joins.
• add/change indices to match query usage.
• possibly require/provide a limited range of terms to prevent quite as large data-sets.

[adamfranco]

At Mark's suggestion I checked the EXPLAIN plan for both the database on saw (MariaDB 10.1.37) and songbird (MariaDB 10.2.17) and they are the same, so that shouldn't be an indication of the performance difference:

[adamfranco]

For the past 5 days since 5e983d9c4084b91604dd4223032f7c39f782d223 was implemented to fix #27, the performance of these queries has improved in production, with instructorxml requests generally returning in less than 1 second. Closing this issue for now.