TSQL: Выбор родителя на основе условий для ребенка

Question 1

У меня есть родительская таблица Orders и Детский столик Jobs со следующими образцами данных

Я хочу отбирать заказы на основе следующих требований

1>Для каждого заказа может быть 0 или более заданий. Не выбирайте заказ, если в нем нет никакого задания.
2>Пользователь не может работать более чем над одним заданием, принадлежащим одному и тому же заказу.
Например Пользователь 1 не может работать на заданиях, относящихся к заказам 1 и 2, потому что он уже работал на заданиях 1 и 4 из того же заказа.
3>Выбирайте только заказы, в которых есть задания в Requested Статус

У меня есть следующий запрос, который дает мне ожидаемый результат

DECLARE @UserID INT = 2

SELECT O.OrderID
FROM Orders O
JOIN Jobs J ON J.OrderID = O.OrderID
WHERE 
J.JobStatus = 'Requested' AND
NOT EXISTS
(  
    --Must not have worked this Order
    SELECT 1 FROM Jobs J1
    WHERE J1.OrderID = O.OrderID AND J1.UserID = @UserID
)
Group By o.OrderID

Демонстрация скрипки SQL

Запрос присоединяется к Jobs столик дважды. Я пытаюсь оптимизировать запрос и ищу способ достичь ожидаемого результата с помощью Jobs таблица только один раз, если это возможно. Любое другое решение также приветствуется. При необходимости я могу изменить схему таблицы.

Таблица заданий содержит почти 20 млн строк, и некоторые запросы времени показывают низкую производительность. (Да, мы смотрели на индексы). Я думаю, что его таблица заданий сканирования дважды вызывает проблемы с производительностью.

Question 2

Если цель состоит в том, чтобы просто "использовать таблицу заданий только один раз", я бы попробовал что-то вроде:

DECLARE @UserID INT = 2
    
SELECT 
    O.OrderID
FROM 
    Orders O
    INNER JOIN Jobs J ON J.OrderID = O.OrderID  
GROUP BY
    O.OrderID
HAVING
    SUM(CASE WHEN J.JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN J.UserID = @UserId THEN 1 ELSE 0 END) = 0

Скрипка SQL

Для дальнейшей оптимизации я бы предложил заменить varchar тип данных JobStatus колонка с tinyint один (и создайте JobStatuses таблица). Как только ваш Job таблица содержит записи 20 м, затем varchar(10) дает вам 190 Мб, однако с помощью tinyint уменьшает размер столбца до 19 Мб — это дает вам меньше операций ввода-вывода для чтения.

И я бы попытался отделить ребенка от присоединения к нему родителей. Такой подход может использовать меньше памяти для одной операции и выиграть в производительности из-за этого:

DECLARE @UserID INT = 2
DECLARE @OrderIDs TABLE (OrderID INT NOT NULL PRIMARY KEY)

INSERT IGNORE INTO @OrderIDs
SELECT
    OrderID
FROM
    Jobs
GROUP BY
    OrderID
HAVING
    SUM(CASE WHEN JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN UserID = @UserId THEN 1 ELSE 0 END) = 0

SELECT
    O.*
FROM
    Orders O
    INNER JOIN @OrderIDs ids on ids.OrderID = O.OrderID

Question 3

Вы могли бы рассмотреть возможность добавления следующего индекса в Jobs стол:

CREATE INDEX idx_jobs ON Jobs (OrderID, UserID, JobStatus);

Этот индекс, если он используется, должен ускорить выполнение подзапроса "не существует" в вашем запросе выше. Кроме того, он может быть использован для соединения с Orders Для Jobs во внешнем запросе верхнего уровня (хотя SQL Server, вероятно, должен был бы выполнить сканирование индекса).

Vadim Loboda · Answer 1 · 2021-11-16T09:14:13

Если цель состоит в том, чтобы просто "использовать таблицу заданий только один раз", я бы попробовал что-то вроде:

DECLARE @UserID INT = 2
    
SELECT 
    O.OrderID
FROM 
    Orders O
    INNER JOIN Jobs J ON J.OrderID = O.OrderID  
GROUP BY
    O.OrderID
HAVING
    SUM(CASE WHEN J.JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN J.UserID = @UserId THEN 1 ELSE 0 END) = 0

Скрипка SQL

Для дальнейшей оптимизации я бы предложил заменить varchar тип данных JobStatus колонка с tinyint один (и создайте JobStatuses таблица). Как только ваш Job таблица содержит записи 20 м, затем varchar(10) дает вам 190 Мб, однако с помощью tinyint уменьшает размер столбца до 19 Мб — это дает вам меньше операций ввода-вывода для чтения.

И я бы попытался отделить ребенка от присоединения к нему родителей. Такой подход может использовать меньше памяти для одной операции и выиграть в производительности из-за этого:

DECLARE @UserID INT = 2
DECLARE @OrderIDs TABLE (OrderID INT NOT NULL PRIMARY KEY)

INSERT IGNORE INTO @OrderIDs
SELECT
    OrderID
FROM
    Jobs
GROUP BY
    OrderID
HAVING
    SUM(CASE WHEN JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN UserID = @UserId THEN 1 ELSE 0 END) = 0

SELECT
    O.*
FROM
    Orders O
    INNER JOIN @OrderIDs ids on ids.OrderID = O.OrderID

Статус работы на самом деле ID типа int. Просто для понимания цели я сохранил его как нварчар
При таком подходе похоже, что мне даже не нужно объединяться с таблицей заказов. Я могу напрямую использовать таблицу заданий для получения идентификатора заказа

Tim Biegeleisen · Answer 2 · 2021-11-16T08:40:46

Вы могли бы рассмотреть возможность добавления следующего индекса в Jobs стол:

CREATE INDEX idx_jobs ON Jobs (OrderID, UserID, JobStatus);

Этот индекс, если он используется, должен ускорить выполнение подзапроса "не существует" в вашем запросе выше. Кроме того, он может быть использован для соединения с Orders Для Jobs во внешнем запросе верхнего уровня (хотя SQL Server, вероятно, должен был бы выполнить сканирование индекса).

TSQL: Выбор родителя на основе условий для ребенка

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории