TSQL: Selectarea părinte bazează pe condiții privind copilul

Question 1

Am o tabela părinte Orders și un Copil de masă Jobs cu următoarele date eșantion

Vreau pentru a selecta Comenzi pe baza următoarelor cerințe

1>Pentru fiecare comanda nu poate fi 0 sau mai multe locuri de muncă. Nu selectați comanda, dacă nu aveți nici o slujbă.
2>Un utilizator nu poate lucra pe mai mult de un loc de muncă care aparține aceluiași ordin.
De exemplu Utilizatorului 1 nu pot lucra pe locuri de Muncă, care aparține Ordinul 1 și 2 pentru că el deja a lucrat la locuri de muncă 1 și 4 din același ordin.
3>selectați Doar comenzile care au locuri de muncă în Requested starea

Am urmatoarea interogare care-mi dă rezultatul așteptat

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

Interogarea se alătură Jobs tabelul de două ori. Eu sunt încercarea de a optimiza interogare și în căutarea pentru o modalitate de a atinge rezultatul scontat prin utilizarea Jobs masa o singură dată, dacă este posibil. Orice altă soluție este, de asemenea, apreciat. Pot modifica schema de masă, dacă este necesar.

Locuri de muncă masa are aproape 20M rânduri și unele interogare în timp arată o performanță slabă. (Da, ne-am uitat la indici). Cred sale de scanare de locuri de muncă de masă de două ori cauzează problema de performanță.

Question 2

Dacă scopul este doar de a "utiliza Locuri de munca masă numai o dată", as incerca ceva de genul:

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 Vioara

Pentru a optimiza în continuare, aș sugera înlocuirea varchar tipul de date al JobStatus coloana cu tinyint unul (și de a crea un JobStatuses tabelul). Odată ce Job masa are 20M înregistrări, apoi varchar(10) vă oferă 190 Mb, cu toate acestea, folosind tinyint reduce dimensiunea coloanei pentru a 19 Mb — acest lucru vă oferă mai puțin IO-operațiuni de Citire.

Și aș încerca să separe copilul de filtrare de la aderarea cu părinții. O astfel de abordare poate utiliza mai puțină memorie pentru o singură operațiune și de a câștiga în performanță din cauza asta:

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

S-ar putea lua în considerare adăugarea următoarele indexul Jobs tabel:

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

Acest indice, dacă este folosit, ar trebui să accelereze nu există subinterogare în interogare de mai sus. De asemenea, acesta poate fi folosit pentru a adera la Orders pentru a Jobs în exterior de nivel superior interogare (deși SQL Server ar trebui, probabil, să facă un index scan).

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

Dacă scopul este doar de a "utiliza Locuri de munca masă numai o dată", as incerca ceva de genul:

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 Vioara

Pentru a optimiza în continuare, aș sugera înlocuirea varchar tipul de date al JobStatus coloana cu tinyint unul (și de a crea un JobStatuses tabelul). Odată ce Job masa are 20M înregistrări, apoi varchar(10) vă oferă 190 Mb, cu toate acestea, folosind tinyint reduce dimensiunea coloanei pentru a 19 Mb — acest lucru vă oferă mai puțin IO-operațiuni de Citire.

Și aș încerca să separe copilul de filtrare de la aderarea cu părinții. O astfel de abordare poate utiliza mai puțină memorie pentru o singură operațiune și de a câștiga în performanță din cauza asta:

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

Starea de locuri de muncă este de fapt ID de tip int. Doar pentru a înțelege scopul l-am păstrat ca nvarchar
Cu această abordare pare că nu au nici măcar să se alăture cu masa de Comenzi. Nu pot folosi direct Locuri de munca masă pentru a obține OrderID

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

S-ar putea lua în considerare adăugarea următoarele indexul Jobs tabel:

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

Acest indice, dacă este folosit, ar trebui să accelereze nu există subinterogare în interogare de mai sus. De asemenea, acesta poate fi folosit pentru a adera la Orders pentru a Jobs în exterior de nivel superior interogare (deși SQL Server ar trebui, probabil, să facă un index scan).

TSQL: Selectarea părinte bazează pe condiții privind copilul

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie