TSQL: Alt koşullara göre üst öğe seçme

Question 1

Bir üst masam var Orders ve bir Çocuk masası Jobs aşağıdaki örnek verilerle

Aşağıdaki gereksinimlere göre Siparişleri seçmek istiyorum

1 > Her sipariş için 0 veya daha fazla iş olabilir. Herhangi bir işi yoksa sipariş'i seçmeyin.
2 > Bir kullanıcı aynı sıraya ait birden fazla işte çalışamaz.
Örneğin Kullanıcı 1 zaten işlerde çalıştığı için Sipariş 1 ve 2'ye ait işlerde çalışamaz 1 ve 4 aynı emirden.
3 > Yalnızca içinde iş bulunan siparişleri seçin Requested durum

Bana beklenen sonucu veren aşağıdaki sorguya sahibim

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 Keman Demosu

Sorguya katılır Jobs masa iki kere. Sorguyu optimize etmeye çalışıyorum ve kullanarak beklenen sonucu elde etmenin bir yolunu arıyorum Jobs mümkünse sadece bir kez masa. Başka herhangi bir çözüm de takdir edilmektedir. Gerekirse tablo şemasını değiştirebilirim.

İşler tablosunda neredeyse 20M satır bulunur ve bir süre sorgusu düşük performans gösterir. (Evet, indekslere baktık). Tarama işleri tablosunun iki kez performans sorununa neden olduğunu düşünüyorum.

Question 2

Amaç sadece "İş tablosunu yalnızca bir kez kullanmak" ise, şöyle bir şey denerim:

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 Kemanı

Daha fazla optimize etmek için, değiştirmenizi öneririm varchar veri türü JobStatus sütun ile tinyint bir (ve bir tane oluştur JobStatuses Tablo). Bir kez senin Job tabloda 20 Milyon kayıt var, o zaman varchar(10) bununla birlikte, 190 Mb verir tinyint sütun boyutunu 19 Mb'ye düşürür — bu size daha az GÇ Okuma işlemi sağlar.

Ve çocuk filtrelemeyi ebeveynlerle birleştirmekten ayırmaya çalışırdım. Bu yaklaşım, tek bir işlem için daha az bellek kullanabilir ve bu nedenle performansta kazanabilir:

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

Aşağıdaki dizine eklemeyi düşünebilirsiniz Jobs masa:

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

Bu dizin, kullanılıyorsa, yukarıdaki sorgunuzdaki varolmayan alt sorguyu hızlandırmalıdır. Ayrıca, katılmak için kullanılabilir Orders -e doğru Jobs dış üst düzey sorguda (SQL Server muhtemelen bir dizin taraması yapmak zorunda olsa da).

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

Amaç sadece "İş tablosunu yalnızca bir kez kullanmak" ise, şöyle bir şey denerim:

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 Kemanı

Daha fazla optimize etmek için, değiştirmenizi öneririm varchar veri türü JobStatus sütun ile tinyint bir (ve bir tane oluştur JobStatuses Tablo). Bir kez senin Job tabloda 20 Milyon kayıt var, o zaman varchar(10) bununla birlikte, 190 Mb verir tinyint sütun boyutunu 19 Mb'ye düşürür — bu size daha az GÇ Okuma işlemi sağlar.

Ve çocuk filtrelemeyi ebeveynlerle birleştirmekten ayırmaya çalışırdım. Bu yaklaşım, tek bir işlem için daha az bellek kullanabilir ve bu nedenle performansta kazanabilir:

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

İş durumu aslında ID int tipi. Sadece anlama amacı için onu nvarchar olarak sakladım
Bu yaklaşımla, Siparişler tablosuna katılmak zorunda bile değilim gibi görünüyor. Sipariş kimliğini almak için doğrudan İş tablosunu kullanabilirim

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

Aşağıdaki dizine eklemeyi düşünebilirsiniz Jobs masa:

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

Bu dizin, kullanılıyorsa, yukarıdaki sorgunuzdaki varolmayan alt sorguyu hızlandırmalıdır. Ayrıca, katılmak için kullanılabilir Orders -e doğru Jobs dış üst düzey sorguda (SQL Server muhtemelen bir dizin taraması yapmak zorunda olsa da).

TSQL: Alt koşullara göre üst öğe seçme

Soru

En iyi cevabı

Diğer dillerde

Bu sayfa diğer dillerde

Bu kategoride popüler

Popüler soruları bu kategoride