EPGStation の予約情報更新が重い
大量のルール・チャンネルがある環境では、ルール予約を更新する処理がとても重くなることが知られています
これは予約情報更新のスケジューラーが起動したときのログですが、所要時間が非常に長くなってしまっています (20分 程度!!)
code:log
原因
ルールから予約すべき番組を検索する SQL がヘビーなのが原因のようです
例えば「伊藤美来」を含む番組を検索する場合、以下のようなクエリが発行されます
code:sql
SELECT
program.id AS program_id,
program.updateTime AS program_updateTime,
program.channelId AS program_channelId,
program.eventId AS program_eventId,
program.serviceId AS program_serviceId,
program.networkId AS program_networkId,
program.startAt AS program_startAt,
program.endAt AS program_endAt,
program.duration AS program_duration,
program.isFree AS program_isFree,
program.name AS program_name,
program.halfWidthName AS program_halfWidthName,
program.shortName AS program_shortName,
program.description AS program_description,
program.halfWidthDescription AS program_halfWidthDescription,
program.extended AS program_extended,
program.halfWidthExtended AS program_halfWidthExtended,
program.rawExtended AS program_rawExtended,
program.rawHalfWidthExtended AS program_rawHalfWidthExtended,
program.genre1 AS program_genre1,
program.subGenre1 AS program_subGenre1,
program.genre2 AS program_genre2,
program.subGenre2 AS program_subGenre2,
program.genre3 AS program_genre3,
program.subGenre3 AS program_subGenre3,
program.channelType AS program_channelType,
program.channel AS program_channel,
program.videoType AS program_videoType,
program.videoResolution AS program_videoResolution,
program.videoStreamContent AS program_videoStreamContent,
program.videoComponentType AS program_videoComponentType,
program.audioSamplingRate AS program_audioSamplingRate,
program.audioComponentType AS program_audioComponentType,
case
when id in (
select
P.id
from
program as P,
recorded_history as R
where
P.shortName = R.name
and P.channelId = R.channelId
and R.endAt >= 1718072816632
and R.endAt <= 1733624816632
and P.endAt <= (R.endAt + 15552000000)
) then 1
else 0
end AS overlap
FROM
program program
WHERE
(
(
((((halfWidthName like '%伊藤美来%'))))
or (
(
(
(
COALESCE(halfWidthDescription, '') like '%伊藤美来%'
)
)
)
)
)
)
and ((((week in (0, 1, 2, 3, 4, 5, 6)))))
AND 1733624816632 <= program.endAt
ORDER BY
program.startAt ASC
とまあ大迫力の SQL が実行される
特にサブクエリで overlap を判定するところが重い
これは同名のタイトルの録画を排除する機能を有効にしている場合クエリされます
https://scrapbox.io/files/67550c8bd65a11fce031f487.png
再放送を除外するときなどに便利
recorded_history へのサブクエリになっている
対策
「録画済み番組を排除」機能をやめる
不便にはなりますが、負荷は下がります
recorded_history テーブルにインデックスを貼る
code:sql
CREATE INDEX idx_recorded_history_channelId_endAt ON recorded_history (channelId, endAt);