TSQL: Výběr rodičů na základě podmínek na dítě

Question 1

Mám nadřazené tabulky Orders a Dítě tabulka Jobs s následující ukázková data

Chci vybrat na základě Objednávky na následující požadavky

1>Pro každou objednávku může být 0 nebo více pracovních míst. Vyberte objednávku, pokud to nemá žádnou práci.
2>uživatel nemůže pracovat na více než jednu práci, která patří do stejné pořadí.
Například Uživatel 1 nemůže pracovat na pracovní Místa, která patří k Pořadí 1 a 2, protože už pracovali na pracovní místa 1 a 4 ze stejné pořadí.
3>vybrat Pouze objednávky, které mají práci v Requested stav

Mám následující dotaz, který mi dává očekávaný výsledek

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

Dotazu připojí Jobs stůl dvakrát. Snažím se optimalizovat dotaz a hledá způsob, jak dosáhnout očekávaného výsledku pomocí Jobs tabulka pouze jednou, pokud je to možné. Jakékoli jiné řešení je také ocenil. Mohu změnit tabulky schématu v případě potřeby.

Míst tabulka má skoro 20M řádky a nějaký čas dotazu ukazuje špatný výkon. (Ano, podívali jsme se na indexy). Myslím, že jeho skenování pracovních míst tabulky dvakrát, což způsobuje problém výkonu.

Question 2

Je-li cílem je jen proto, aby "použití pracovní Místa tabulky pouze jednou", zkusil bych něco jako:

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 Housle

Dále optimalizovat, navrhuji nahrazení varchar datový typ JobStatus sloupec s tinyint jeden (a vytvořit JobStatuses tabulka). Jakmile je váš Job stůl má 20M záznamů, pak varchar(10) dává vám 190 Mb, nicméně pomocí tinyint snižuje sloupec velikost 19 Mb — to vám dává méně IO-operace Čtení.

A já bych zkusit oddělit dítě filtrování od spojení s rodiči. Takový přístup může používat méně paměti pro jedinou operaci a vyhrát ve výkonu 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ážit přidání následující index na Jobs tabulka:

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

Tento index, pokud jsou použity, by měly urychlit not exists poddotaz v dotazu výše. Také to může být použit pro spojení z Orders k Jobs ve vnější horní úrovni dotazu (i když SQL Server by pravděpodobně muset udělat index scan).

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

Je-li cílem je jen proto, aby "použití pracovní Místa tabulky pouze jednou", zkusil bych něco jako:

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 Housle

Dále optimalizovat, navrhuji nahrazení varchar datový typ JobStatus sloupec s tinyint jeden (a vytvořit JobStatuses tabulka). Jakmile je váš Job stůl má 20M záznamů, pak varchar(10) dává vám 190 Mb, nicméně pomocí tinyint snižuje sloupec velikost 19 Mb — to vám dává méně IO-operace Čtení.

A já bych zkusit oddělit dítě filtrování od spojení s rodiči. Takový přístup může používat méně paměti pro jedinou operaci a vyhrát ve výkonu 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

Stav úlohy je ve skutečnosti ID typu int. Jen pro pochopení účelu pořád jsem to jako nvarchar
S tímto přístupem vypadat, že jsem se ani nemusíte připojit s tabulky Objednávky. Mohu přímo použít pracovní Místa tabulky, aby si Kódobjednávky

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

Můžete zvážit přidání následující index na Jobs tabulka:

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

Tento index, pokud jsou použity, by měly urychlit not exists poddotaz v dotazu výše. Také to může být použit pro spojení z Orders k Jobs ve vnější horní úrovni dotazu (i když SQL Server by pravděpodobně muset udělat index scan).

TSQL: Výběr rodičů na základě podmínek na dítě

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii