From 7fb45bdacb6d4399f56f575301b414e49efbdf92 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Mon, 27 Jun 2022 09:34:26 +0200 Subject: Optimize feeds query --- .../video/shared/abstract-video-query-builder.ts | 7 +++- .../sql/video/shared/video-file-query-builder.ts | 30 +++++++++------- .../sql/video/video-model-get-query-builder.ts | 15 ++++++-- .../sql/video/videos-model-list-query-builder.ts | 41 +++++++++++++++++----- 4 files changed, 69 insertions(+), 24 deletions(-) diff --git a/server/models/video/sql/video/shared/abstract-video-query-builder.ts b/server/models/video/sql/video/shared/abstract-video-query-builder.ts index b79d20ade..3c74b0ea6 100644 --- a/server/models/video/sql/video/shared/abstract-video-query-builder.ts +++ b/server/models/video/sql/video/shared/abstract-video-query-builder.ts @@ -313,7 +313,12 @@ export class AbstractVideoQueryBuilder extends AbstractRunQuery { return result } - protected whereId (options: { id?: string | number, url?: string }) { + protected whereId (options: { ids?: number[], id?: string | number, url?: string }) { + if (options.ids) { + this.where = `WHERE "video"."id" IN (${createSafeIn(this.sequelize, options.ids)})` + return + } + if (options.url) { this.where = 'WHERE "video"."url" = :videoUrl' this.replacements.videoUrl = options.url diff --git a/server/models/video/sql/video/shared/video-file-query-builder.ts b/server/models/video/sql/video/shared/video-file-query-builder.ts index 50c12f627..cc53a4860 100644 --- a/server/models/video/sql/video/shared/video-file-query-builder.ts +++ b/server/models/video/sql/video/shared/video-file-query-builder.ts @@ -1,7 +1,17 @@ -import { Sequelize } from 'sequelize' -import { BuildVideoGetQueryOptions } from '../video-model-get-query-builder' +import { Sequelize, Transaction } from 'sequelize' import { AbstractVideoQueryBuilder } from './abstract-video-query-builder' +export type FileQueryOptions = { + id?: string | number + url?: string + + includeRedundancy: boolean + + transaction?: Transaction + + logging?: boolean +} + /** * * Fetch files (webtorrent and streaming playlist) according to a video @@ -15,26 +25,26 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder { super(sequelize, 'get') } - queryWebTorrentVideos (options: BuildVideoGetQueryOptions) { + queryWebTorrentVideos (options: FileQueryOptions) { this.buildWebtorrentFilesQuery(options) return this.runQuery(options) } - queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) { + queryStreamingPlaylistVideos (options: FileQueryOptions) { this.buildVideoStreamingPlaylistFilesQuery(options) return this.runQuery(options) } - private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) { + private buildWebtorrentFilesQuery (options: FileQueryOptions) { this.attributes = { '"video"."id"': '' } this.includeWebtorrentFiles() - if (this.shouldIncludeRedundancies(options)) { + if (options.includeRedundancy) { this.includeWebTorrentRedundancies() } @@ -43,14 +53,14 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder { this.query = this.buildQuery() } - private buildVideoStreamingPlaylistFilesQuery (options: BuildVideoGetQueryOptions) { + private buildVideoStreamingPlaylistFilesQuery (options: FileQueryOptions) { this.attributes = { '"video"."id"': '' } this.includeStreamingPlaylistFiles() - if (this.shouldIncludeRedundancies(options)) { + if (options.includeRedundancy) { this.includeStreamingPlaylistRedundancies() } @@ -62,8 +72,4 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder { private buildQuery () { return `${this.buildSelect()} FROM "video" ${this.joins} ${this.where}` } - - private shouldIncludeRedundancies (options: BuildVideoGetQueryOptions) { - return options.type === 'api' - } } diff --git a/server/models/video/sql/video/video-model-get-query-builder.ts b/server/models/video/sql/video/video-model-get-query-builder.ts index b0879c9ac..32e5c4ff7 100644 --- a/server/models/video/sql/video/video-model-get-query-builder.ts +++ b/server/models/video/sql/video/video-model-get-query-builder.ts @@ -1,3 +1,4 @@ +import { pick } from 'lodash' import { Sequelize, Transaction } from 'sequelize' import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder' import { VideoFileQueryBuilder } from './shared/video-file-query-builder' @@ -50,15 +51,21 @@ export class VideoModelGetQueryBuilder { } async queryVideo (options: BuildVideoGetQueryOptions) { + const fileQueryOptions = { + ...pick(options, [ 'id', 'url', 'transaction', 'logging' ]), + + includeRedundancy: this.shouldIncludeRedundancies(options) + } + const [ videoRows, webtorrentFilesRows, streamingPlaylistFilesRows ] = await Promise.all([ this.videoQueryBuilder.queryVideos(options), VideoModelGetQueryBuilder.videoFilesInclude.has(options.type) - ? this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(options) + ? this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(fileQueryOptions) : Promise.resolve(undefined), VideoModelGetQueryBuilder.videoFilesInclude.has(options.type) - ? this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(options) + ? this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(fileQueryOptions) : Promise.resolve(undefined) ]) @@ -76,6 +83,10 @@ export class VideoModelGetQueryBuilder { return videos[0] } + + private shouldIncludeRedundancies (options: BuildVideoGetQueryOptions) { + return options.type === 'api' + } } export class VideosModelGetQuerySubBuilder extends AbstractVideoQueryBuilder { diff --git a/server/models/video/sql/video/videos-model-list-query-builder.ts b/server/models/video/sql/video/videos-model-list-query-builder.ts index 2a4afc389..4fe6bc321 100644 --- a/server/models/video/sql/video/videos-model-list-query-builder.ts +++ b/server/models/video/sql/video/videos-model-list-query-builder.ts @@ -1,6 +1,8 @@ -import { VideoInclude } from '@shared/models' +import { pick } from 'lodash' import { Sequelize } from 'sequelize' +import { VideoInclude } from '@shared/models' import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder' +import { VideoFileQueryBuilder } from './shared/video-file-query-builder' import { VideoModelBuilder } from './shared/video-model-builder' import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder' @@ -16,20 +18,46 @@ export class VideosModelListQueryBuilder extends AbstractVideoQueryBuilder { private innerQuery: string private innerSort: string + webtorrentFilesQueryBuilder: VideoFileQueryBuilder + streamingPlaylistFilesQueryBuilder: VideoFileQueryBuilder + private readonly videoModelBuilder: VideoModelBuilder constructor (protected readonly sequelize: Sequelize) { super(sequelize, 'list') this.videoModelBuilder = new VideoModelBuilder(this.mode, this.tables) + this.webtorrentFilesQueryBuilder = new VideoFileQueryBuilder(sequelize) + this.streamingPlaylistFilesQueryBuilder = new VideoFileQueryBuilder(sequelize) } - queryVideos (options: BuildVideosListQueryOptions) { + async queryVideos (options: BuildVideosListQueryOptions) { this.buildInnerQuery(options) this.buildMainQuery(options) - return this.runQuery() - .then(rows => this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include })) + const rows = await this.runQuery() + + if (options.include & VideoInclude.FILES) { + const videoIds = Array.from(new Set(rows.map(r => r.id))) + + if (videoIds.length !== 0) { + const fileQueryOptions = { + ...pick(options, [ 'transaction', 'logging' ]), + + ids: videoIds, + includeRedundancy: false + } + + const [ rowsWebTorrentFiles, rowsStreamingPlaylist ] = await Promise.all([ + this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(fileQueryOptions), + this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(fileQueryOptions) + ]) + + return this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include, rowsStreamingPlaylist, rowsWebTorrentFiles }) + } + } + + return this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include }) } private buildInnerQuery (options: BuildVideosListQueryOptions) { @@ -52,11 +80,6 @@ export class VideosModelListQueryBuilder extends AbstractVideoQueryBuilder { this.includeAccounts() this.includeThumbnails() - if (options.include & VideoInclude.FILES) { - this.includeWebtorrentFiles() - this.includeStreamingPlaylistFiles() - } - if (options.user) { this.includeUserHistory(options.user.id) } -- cgit v1.2.3