TSQL: a Seleção principal com base nas condições da criança

Question 1

Eu tenho uma tabela pai Orders e uma tabela Filho Jobs com os seguintes dados de exemplo

Eu quero selecionar Ordens com base nos seguintes requisitos

1>Para cada ordem pode ser 0 ou mais postos de trabalho. Não selecionar a ordem se de não ter qualquer trabalho.
2>Um usuário não pode trabalhar em mais do que um trabalho que pertence à mesma ordem.
Por exemplo Usuário 1 não pode trabalhar em Empregos que pertence à Ordem 1 e 2, porque ele já trabalhou em empregos 1 e 4 da mesma encomenda.
3>selecione Somente as ordens que têm empregos em Requested estado

Eu tenho a seguinte consulta que me dá o resultado esperado

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

Associações de consulta a Jobs a tabela duas vezes. Eu estou tentando otimizar a consulta e procurando uma maneira de atingir o resultado esperado usando Jobs tabela apenas uma vez, se possível. Qualquer outra solução também é apreciado. Eu posso alterar o esquema da tabela, se necessário.

A tabela de tarefas tem quase 20M de linhas e algum tempo de consulta mostra um desempenho ruim. (Sim, nós olhamos índices). Eu acho os seus trabalhos de digitalização de mesa duas vezes causando o problema de desempenho.

Question 2

Se o objectivo for apenas para "use a tabela de Tarefas apenas uma vez", eu gostaria de tentar algo como:

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

Para otimizar ainda mais, gostaria de sugerir a substituição do varchar tipo de dados do JobStatus coluna com tinyint (e criar uma JobStatuses tabela). Uma vez que seu Job a tabela tem 20M de registros e, em seguida, varchar(10) dá-lhe 190 Mb, no entanto, utilizando o tinyint reduz o tamanho da coluna para 19 Mb — isso dá menos IO-operações de Leitura.

E eu gostaria de tentar separar a criança de filtragem de aderir a ele com os pais. Tal abordagem pode usar menos memória para uma única operação e ganhar em desempenho devido a que:

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

Você pode considerar adicionar o seguinte índice para o Jobs tabela:

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

Este índice, se utilizada, deve acelerar o não existe subconsulta na consulta acima. Além disso, ele pode ser usado para a associação de Orders para Jobs no exterior consulta de nível superior (embora o SQL Server provavelmente teria de fazer uma verificação de índice).

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

Se o objectivo for apenas para "use a tabela de Tarefas apenas uma vez", eu gostaria de tentar algo como:

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

Para otimizar ainda mais, gostaria de sugerir a substituição do varchar tipo de dados do JobStatus coluna com tinyint (e criar uma JobStatuses tabela). Uma vez que seu Job a tabela tem 20M de registros e, em seguida, varchar(10) dá-lhe 190 Mb, no entanto, utilizando o tinyint reduz o tamanho da coluna para 19 Mb — isso dá menos IO-operações de Leitura.

E eu gostaria de tentar separar a criança de filtragem de aderir a ele com os pais. Tal abordagem pode usar menos memória para uma única operação e ganhar em desempenho devido a que:

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

O status do trabalho é, na verdade, ID do tipo int. Apenas para a compreensão do propósito eu guardei, como nvarchar
Com esta abordagem parece que não tem mesmo a associação com a tabela Pedidos. Eu posso usar diretamente tabela de Tarefas para obter Códigodopedido

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

Você pode considerar adicionar o seguinte índice para o Jobs tabela:

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

Este índice, se utilizada, deve acelerar o não existe subconsulta na consulta acima. Além disso, ele pode ser usado para a associação de Orders para Jobs no exterior consulta de nível superior (embora o SQL Server provavelmente teria de fazer uma verificação de índice).

TSQL: a Seleção principal com base nas condições da criança

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria