TSQL: la Selección de los padres en función de las condiciones del niño

Question 1

Tengo una tabla primaria Orders y un Niño de mesa Jobs con los siguientes datos de ejemplo

Quiero seleccionar Pedidos en base a los siguientes requisitos

1>Para cada orden puede ser 0 o más puestos de trabajo. No seleccione la orden, si no tiene ningún tipo de trabajo.
2>Un usuario puede trabajar en más de un trabajo que pertenece a la misma orden.
Por ejemplo, el Usuario 1 no puede trabajar en los Puestos de trabajo que pertenece a la Orden 1 y 2 porque él ya ha trabajado en puestos de trabajo 1 y 4 de la misma orden.
3>seleccionar Sólo los pedidos que tienen puestos de trabajo en Requested estado

Tengo la siguiente consulta, que me da el 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 Violín Demo

La consulta se une a la Jobs tabla dos veces. Estoy tratando de optimizar la consulta y busca una manera de lograr el resultado esperado por el uso de Jobs tabla sólo una vez si es posible. Cualquier otra solución también es apreciado. Me pueden alterar el esquema de la tabla si es necesario.

La tabla de trabajos tiene casi 20 millones de filas y algunas de las consultas en tiempo muestra un rendimiento deficiente. (Sí, nos fijamos en los índices). Creo que sus trabajos de escaneo de la tabla dos veces causando el problema de rendimiento.

Question 2

Si el objetivo es simplemente el de "uso de la tabla de Trabajos sólo una vez", me gustaría probar algo así 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 Violín

Para optimizar aún más, me permito sugerir la sustitución de la varchar tipo de datos de la JobStatus columna con tinyint uno (y crear un JobStatuses de la tabla). Una vez que su Job la tabla tiene 20M de registros, varchar(10) le da 190 Mb, sin embargo el uso de la tinyint reduce el tamaño de las columnas a las 19 Mb — esto le da menos IO-operaciones de Lectura.

Y me gustaría tratar de separar al niño de filtrado de unirse con los padres. Tal enfoque puede utilizar menos memoria para una sola operación y ganar en rendimiento debido 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

Usted podría considerar agregar el siguiente índice para el Jobs tabla:

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

Este índice, si se utilizan, deben acelerar el no existe subconsulta en la consulta anterior. También, puede ser utilizado para la combinación de Orders a Jobs en la parte superior exterior de consulta de nivel (aunque SQL Server probablemente habría que hacer un recorrido de índice).

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

Si el objetivo es simplemente el de "uso de la tabla de Trabajos sólo una vez", me gustaría probar algo así 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 Violín

Para optimizar aún más, me permito sugerir la sustitución de la varchar tipo de datos de la JobStatus columna con tinyint uno (y crear un JobStatuses de la tabla). Una vez que su Job la tabla tiene 20M de registros, varchar(10) le da 190 Mb, sin embargo el uso de la tinyint reduce el tamaño de las columnas a las 19 Mb — esto le da menos IO-operaciones de Lectura.

Y me gustaría tratar de separar al niño de filtrado de unirse con los padres. Tal enfoque puede utilizar menos memoria para una sola operación y ganar en rendimiento debido 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

El estado del trabajo es en realidad ID de tipo int. Sólo para la comprensión del propósito me la guardé como nvarchar
Con este enfoque parece que incluso no tiene que unirse con la tabla Pedidos. Puedo utilizar directamente Puestos de trabajo de la tabla para obtener Idpedido

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

Usted podría considerar agregar el siguiente índice para el Jobs tabla:

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

Este índice, si se utilizan, deben acelerar el no existe subconsulta en la consulta anterior. También, puede ser utilizado para la combinación de Orders a Jobs en la parte superior exterior de consulta de nivel (aunque SQL Server probablemente habría que hacer un recorrido de índice).

TSQL: la Selección de los padres en función de las condiciones del niño

Pregunta

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría