TSQL: Wybór rodzica na podstawie warunków dla dziecka

Question 1

Mam nadrzędny tabela Orders i stolik dla Dzieci Jobs z następujących przykładowych danych

Chcę odbierać zamówienia na podstawie następujących wymagań

1>Do każdego zamówienia może być 0 lub więcej zadań. Nie wybieraj zamówienie, jeśli nie ma w nim żadnego zadania.
2>Użytkownik nie może pracować więcej niż nad jednym zadaniem, należących do jednego i tego samego zamówienia.
Na Przykład Użytkownik 1 nie może pracować w zadaniach odnoszących się do zamówień 1 i 2, bo on już pracował na misjach 1 i 4 z tego samego zamówienia.
3>Wybierz tylko zamówienia, w których są zadania w Requested Status

Mam następujący wniosek, który mi daje oczekiwany wynik

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

Demonstracja skrzypce SQL

Wniosek dołącza do Jobs stolik dwukrotnie. Staram się optymalizować zapytania i szukam sposobem na osiągnięcie oczekiwanego rezultatu za pomocą Jobs tabela tylko raz, jeśli to możliwe. Każde inne rozwiązanie również mile widziane. W razie potrzeby mogę zmienić schemat tabeli.

Tabela zadań zawiera prawie 20 mln wierszy, a niektóre wnioski czasu wykazują niską wydajność. (Tak, patrzyliśmy na indeksy). Myślę, że jego tabela zadań skanowania kliknij dwukrotnie powoduje problemy z wydajnością.

Question 2

Jeśli celem jest, aby po prostu "arkusz zadań tylko raz", ja bym spróbował coś takiego:

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

Skrzypce SQL

Dla dalszej optymalizacji ja bym proponował wymienić varchar typ danych JobStatus kolumna z tinyint jeden (i utwórz JobStatuses tabela). Jak tylko twój Job tabela zawiera wpisy 20 m, a następnie varchar(10) daje 190 Mb, jednak z pomocą tinyint zmniejsza rozmiar kolumny do 19 Mb — to daje mniej operacji wejścia / wyjścia do czytania.

I ja bym próbował oddzielić dziecko od przystąpienia do rodziców. Takie podejście może użyć mniej pamięci dla jednej operacji i wygrać w wydajności z tego powodu:

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

Można rozważyć możliwość dodania kolejnego indeksu w Jobs stół:

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

Indeks ten, jeśli jest on używany, musi przyspieszyć wykonanie podrzędnego "nie istnieje" w zapytaniu powyżej. Ponadto, może on być stosowany do połączenia z Orders Dla Jobs w zapytaniu zewnętrznym najwyższego poziomu (choć SQL Server, prawdopodobnie musiałby wykonać skanowanie indeksu).

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

Jeśli celem jest, aby po prostu "arkusz zadań tylko raz", ja bym spróbował coś takiego:

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

Skrzypce SQL

Dla dalszej optymalizacji ja bym proponował wymienić varchar typ danych JobStatus kolumna z tinyint jeden (i utwórz JobStatuses tabela). Jak tylko twój Job tabela zawiera wpisy 20 m, a następnie varchar(10) daje 190 Mb, jednak z pomocą tinyint zmniejsza rozmiar kolumny do 19 Mb — to daje mniej operacji wejścia / wyjścia do czytania.

I ja bym próbował oddzielić dziecko od przystąpienia do rodziców. Takie podejście może użyć mniej pamięci dla jednej operacji i wygrać w wydajności z tego powodu:

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

Status pracy w rzeczywistości ID typu int. Po prostu dla zrozumienia celu zapisałem go jako нварчар
Przy takim podejściu wygląda na to, że mi się nawet nie musimy się zjednoczyć z tabeli zamówień. Mogę korzystać bezpośrednio z tabeli zadań, aby uzyskać identyfikator zamówienia

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

Można rozważyć możliwość dodania kolejnego indeksu w Jobs stół:

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

Indeks ten, jeśli jest on używany, musi przyspieszyć wykonanie podrzędnego "nie istnieje" w zapytaniu powyżej. Ponadto, może on być stosowany do połączenia z Orders Dla Jobs w zapytaniu zewnętrznym najwyższego poziomu (choć SQL Server, prawdopodobnie musiałby wykonać skanowanie indeksu).

TSQL: Wybór rodzica na podstawie warunków dla dziecka

Pytanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii