TSQL: Selezione di genitore in base alle condizioni del bambino

Question 1

Ho una tabella padre Orders e una tabella Figlio Jobs con i seguenti dati di esempio

Voglio selezionare gli Ordini in base ai seguenti requisiti

1>Per ogni ordine non può essere 0 o più posti di lavoro. Non selezionare l'ordine in se non ha alcun lavoro.
2>Un utente può lavorare su più di un lavoro che appartiene allo stesso ordine.
Per esempio Utente 1 può lavorare in posti di Lavoro che appartiene all'Ordine 1 e 2, perché ha già lavorato sui posti di lavoro 1 e 4 dello stesso ordine.
3>selezionare Solo gli ordini che hanno un lavoro, in Requested stato

Ho la seguente query che mi dà il risultato atteso

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 Violino Demo

Query di join il Jobs tabella due volte. Sto cercando di ottimizzare le query e alla ricerca di un modo per ottenere il risultato previsto utilizzando Jobs tabella una sola volta, se possibile. Qualsiasi altra soluzione è anche apprezzato. Posso modificare lo schema della tabella, se necessario.

Tabella jobs ha quasi 20M righe e qualche tempo query mostra un peggioramento delle prestazioni. (Sì, abbiamo guardato gli indici). Credo proprio che la scansione dei lavori tabella due volte causando il problema di prestazioni.

Question 2

Se lo scopo è solo quello di "utilizzare posti di Lavoro solo per la tabella una volta", vorrei provare qualcosa di simile:

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 Violino

Per ottimizzare ulteriormente, vorrei suggerire di sostituire il varchar il tipo di dati del JobStatus colonna con tinyint uno (e creare un JobStatuses la tabella). Una volta che il vostro Job tabella 20M record, quindi varchar(10) ti da 190 Mb, tuttavia utilizzando il tinyint riduce la dimensione della colonna di 19 Mb — questo dà meno IO-le operazioni di Lettura.

E vorrei provare a separare il figlio di filtraggio di entrare con i genitori. Tale approccio può utilizzare meno memoria per una singola operazione e vincere in termini di prestazioni a causa di che:

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

Si potrebbe considerare di aggiungere il seguente indice Jobs tabella:

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

Questo indice, se utilizzato, deve accelerare la non esiste sottoquery nella tua query di cui sopra. Inoltre, può essere utilizzato per il join da Orders per Jobs nella parte superiore esterna a livello di query (anche se SQL Server sarebbe probabilmente fare la scansione di un indice).

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

Se lo scopo è solo quello di "utilizzare posti di Lavoro solo per la tabella una volta", vorrei provare qualcosa di simile:

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 Violino

Per ottimizzare ulteriormente, vorrei suggerire di sostituire il varchar il tipo di dati del JobStatus colonna con tinyint uno (e creare un JobStatuses la tabella). Una volta che il vostro Job tabella 20M record, quindi varchar(10) ti da 190 Mb, tuttavia utilizzando il tinyint riduce la dimensione della colonna di 19 Mb — questo dà meno IO-le operazioni di Lettura.

E vorrei provare a separare il figlio di filtraggio di entrare con i genitori. Tale approccio può utilizzare meno memoria per una singola operazione e vincere in termini di prestazioni a causa di che:

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

Il lavoro è stato effettivamente ID di tipo int. Solo per la comprensione scopo l'ho tenuta come nvarchar
Con questo approccio sembra che non hanno nemmeno bisogno di join con la tabella Ordini. Posso utilizzare direttamente i Lavori di tabella per ottenere Idordine

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

Si potrebbe considerare di aggiungere il seguente indice Jobs tabella:

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

Questo indice, se utilizzato, deve accelerare la non esiste sottoquery nella tua query di cui sopra. Inoltre, può essere utilizzato per il join da Orders per Jobs nella parte superiore esterna a livello di query (anche se SQL Server sarebbe probabilmente fare la scansione di un indice).

TSQL: Selezione di genitore in base alle condizioni del bambino

Domanda

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria