TSQL: la Sélection d'un parent sur la base des conditions de l'enfant

Question 1

J'ai une table parent Orders et une table d'Enfant Jobs avec les données d'exemple suivantes

Je veux sélectionner des Commandes basées sur les conditions suivantes

1>Pour chaque commande, il peut y avoir 0 ou plusieurs emplois. Ne sélectionnez pas la commande si elle n'a pas de travail.
2>Un utilisateur ne peut pas travailler sur plus d'un travail qui appartient à la même commande.
Par exemple l'Utilisateur 1 ne peut pas travailler sur les Emplois qui appartient à l'Ordre 1 et 2 parce qu'il a déjà travaillé sur la création d'emplois 1 et 4 de la même commande.
3>sélectionner Uniquement les commandes qui ont des emplois dans Requested statut

J'ai la requête suivante qui me donne le résultat attendu

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 Violon Démo

Requête rejoint le Jobs table deux fois. Je suis en train d'optimiser la requête et à la recherche d'un moyen pour atteindre le résultat visé par l'aide de Jobs le tableau qu'une seule fois si possible. Toute autre solution est également apprécié. Je peux modifier le schéma de la table si nécessaire.

Les travaux de la table a près de 20M de lignes et de certains temps de requête affiche des performances médiocres. (Oui, nous avons regardé les index). Je pense que ses travaux de numérisation de la table deux fois l'origine du problème de performances.

Question 2

Si le but est juste de "l'utilisation des Emplois de la table qu'une seule fois", je voudrais essayer quelque chose comme:

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 Violon

Pour optimiser davantage, je vous suggère de remplacer le varchar type de données de l' JobStatus colonne avec tinyint un (et de créer un JobStatuses tableau). Une fois que votre Job table a 20M des dossiers, puis varchar(10) vous donne 190 Mo, cependant, l'utilisation de la tinyint réduit la taille de la colonne à 19 Mb — ce qui vous donne moins de IO-les opérations de Lecture.

Et je voudrais essayer de séparer l'enfant de filtrage de se joindre avec les parents. Une telle approche peut utiliser moins de mémoire pour une seule opération et de gagner en performance à cause de ça:

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

Vous pouvez envisager l'ajout de l'index suivant à la Jobs tableau:

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

Cet indice, si elle est utilisée, doit accélérer la n'existe pas de sous-requête dans votre requête ci-dessus. Aussi, il peut être utilisé pour la jointure de Orders pour Jobs dans le haut extérieur requête de niveau (bien que SQL Server serait probablement de faire une analyse d'index).

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

Si le but est juste de "l'utilisation des Emplois de la table qu'une seule fois", je voudrais essayer quelque chose comme:

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 Violon

Pour optimiser davantage, je vous suggère de remplacer le varchar type de données de l' JobStatus colonne avec tinyint un (et de créer un JobStatuses tableau). Une fois que votre Job table a 20M des dossiers, puis varchar(10) vous donne 190 Mo, cependant, l'utilisation de la tinyint réduit la taille de la colonne à 19 Mb — ce qui vous donne moins de IO-les opérations de Lecture.

Et je voudrais essayer de séparer l'enfant de filtrage de se joindre avec les parents. Une telle approche peut utiliser moins de mémoire pour une seule opération et de gagner en performance à cause de ça:

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

Le statut de la tâche est en fait ID de type int. Juste pour but compréhension je l'ai gardé comme nvarchar
Avec cette approche, je n'ai même pas à se joindre à la table Commandes. Je peux utiliser directement les Travaux de la table pour obtenir OrderID

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

Vous pouvez envisager l'ajout de l'index suivant à la Jobs tableau:

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

Cet indice, si elle est utilisée, doit accélérer la n'existe pas de sous-requête dans votre requête ci-dessus. Aussi, il peut être utilisé pour la jointure de Orders pour Jobs dans le haut extérieur requête de niveau (bien que SQL Server serait probablement de faire une analyse d'index).

TSQL: la Sélection d'un parent sur la base des conditions de l'enfant

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie