TSQL: Výber rodičov na základe podmienok na dieťa

Question 1

Som rodič tabuľka Orders a Dieťa tabuľka Jobs s ukážkových dát

Chcem vyberte Objednávky na základe nasledujúcich požiadaviek

1>Pri každej objednávke môže byť 0 alebo viac pracovných miest. Nevyberajte objednávke, ak to nemá žiadnu prácu.
2>používateľ nemôže pracovať na viac ako jednu prácu, ktorá patrí k jednej objednávke.
Napríklad Používateľ 1 nemôže pracovať na pracovných Miest, ktoré patrí na Objednávku 1 a 2, pretože on už pracoval na zamestnanosť 1 a 4 od rovnakom poradí.
3>vybrať Iba objednávky, ktoré majú pracovných miest v Requested stav

Mám tieto dotaz, ktorý mi dáva očakávaný výsledok

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

Dotaz pripojí Jobs tabuľky dvakrát. Ja som snaží optimalizovať dotaz a hľadá spôsob, ako dosiahnuť očakávaný výsledok, pomocou Jobs tabuľka len raz, ak je to možné. Akékoľvek iné riešenie je tiež ocenili. Môžem zmeniť tabuľky, schémy, ak je to potrebné.

Pracovných miest tabuľka má takmer 20M riadkov a nejaký čas dotaz ukazuje zlý výkon. (Áno, sme sa zamerali na indexy). Myslím, že jeho skenovanie pracovných miest tabuľky dvakrát spôsobuje výkonu problém.

Question 2

Ak cieľom je len "používanie pracovných Miest tabuľky, iba raz", by som vyskúšať niečo ako:

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 Husle

Ak chcete optimalizovať ďalej, navrhoval by som, výmena varchar typ z JobStatus stĺpec s tinyint jeden (a vytvoriť JobStatuses tabuľka). Akonáhle vaše Job tabuľka má 20M záznamy, potom varchar(10) dáva vám 190 Mb, avšak pomocou tinyint znižuje stĺpec veľkosť 19 Mb — to vám dáva menej IO-Čítať operácie.

A ja by som sa pokúsiť oddeliť dieťa filtrovanie od vstupu s rodičmi. Takýto prístup môže použiť menej pamäte pre jednu prevádzku a vyhrať v predstavenia z dôvodu, že:

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

Môžete zvážiť pridanie nasledujúce index na Jobs tabuľka:

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

Tento index, ak sú použité, by mali urýchliť neexistuje, poddotaz v váš dopyt vyššie. Tiež môže byť použitý pre pripojiť sa z Orders na Jobs vo vonkajšom najvyššej úrovni dopytu (hoci SQL Server by pravdepodobne musieť robiť prehľadávanie indexu).

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

Ak cieľom je len "používanie pracovných Miest tabuľky, iba raz", by som vyskúšať niečo ako:

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 Husle

Ak chcete optimalizovať ďalej, navrhoval by som, výmena varchar typ z JobStatus stĺpec s tinyint jeden (a vytvoriť JobStatuses tabuľka). Akonáhle vaše Job tabuľka má 20M záznamy, potom varchar(10) dáva vám 190 Mb, avšak pomocou tinyint znižuje stĺpec veľkosť 19 Mb — to vám dáva menej IO-Čítať operácie.

A ja by som sa pokúsiť oddeliť dieťa filtrovanie od vstupu s rodičmi. Takýto prístup môže použiť menej pamäte pre jednu prevádzku a vyhrať v predstavenia z dôvodu, že:

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

Prácu stav je vlastne ID typu int. Len pre pochopenie účelu stále som to ako nvarchar
S týmto prístupom vyzerať nemám ani musieť pripojiť sa s tabuľka Objednávky. Môžem priamo použiť pracovných Miest tabuľka sa dostať Objednávky

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

Môžete zvážiť pridanie nasledujúce index na Jobs tabuľka:

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

Tento index, ak sú použité, by mali urýchliť neexistuje, poddotaz v váš dopyt vyššie. Tiež môže byť použitý pre pripojiť sa z Orders na Jobs vo vonkajšom najvyššej úrovni dopytu (hoci SQL Server by pravdepodobne musieť robiť prehľadávanie indexu).

TSQL: Výber rodičov na základe podmienok na dieťa

Otázka

Najlepšiu odpoveď

V iných jazykoch

Táto stránka je v iných jazykoch

Populárne v tejto kategórii

Obľúbené otázky v tejto kategórii