Birleştirilmiş bir tablonun bir sütununa göre sipariş verirken performansı iyileştirme
Bir arama tablosunun yabancı anahtarını içeren bir üst tablom var (basitleştirilmiş örnek):
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
CREATE TABLE [dbo].[Lookup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](64) NOT NULL
)
Bu durumda, Parent
tabloda 10 milyondan fazla satır vardır ve Lookup
tabloda yaklaşık 5.000 vardır. Gerçek Parent
uygulama, diğer tablolara yönelik bu tür birkaç yabancı anahtar referansına sahiptir ve bu sütunların her biri NULL içerebilir.
Her iki örnek tablonun Id
sütunları için benzersiz kümelenmiş dizinleri vardır, için Parent
kümelenmemiş dizini vardır LookupId
ve için kümelenmemiş dizini Lookup
vardır Name
.
Arama değerini sonuçlara dahil etmek istediğim sayfalı bir sorgu çalıştırıyorum: -
SELECT
P.Id,
L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY P.Id
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
Bu, siparişte olduğu gibi hızlı çalışır P.LookupId
.
Bununla birlikte, sipariş vermeye çalışırsam Name
(veya hatta L.Id
), sorgu oldukça yavaş çalışır:
SELECT
P.Id,
L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY L.Name
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
İkinci sorgu için sorgu planı burada: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
Görünüşte ilgili diğer sorular, uygun bir indeks kullanılarak çözülebilecek ilk tablodaki sütunlara göre sıralamayı içeriyor gibi görünüyor.
Bu sorgu için dizine alınmış bir görünüm oluşturmayı denedim, ancak SQL Server, LookupId
NULL olabileceğinden gerekli olan bir LEFT JOIN içerdiğinden görünümü indekslememe izin vermiyor ve bir INNER JOIN kullanırsam bu kayıtlar hariç tutulacak.
Bu durumu optimize etmenin bir yolu var mı?
DÜZENLE
Rob Farley'in cevabı (teşekkürler!) Harika ve ilk başta sorduğum soru için mükemmel çalışıyor, ki burada tek bir masaya katılacağımı ima ettim.
Olduğu gibi, bu tür birden çok tablom var ve bu çözümü kullanmak için tüm INNER JOIN'leri kullanarak uzlaştıramadım.
Şimdilik, arama tablolarına bir "NULL" satırı ekleyerek, soldaki herhangi bir satırı kaybetmeden bir INNER JOIN kullanabilmek için bu sorunu çözdüm.
Benim durumumda uniqueidentifier
kimlikler kullanıyorum, bu nedenle aşağıdaki gibi dizine alınmış bir görünüm oluşturuyorum:
CREATE VIEW [dbo].[ParentView]
WITH SCHEMABINDING
AS
SELECT
P.Id,
L.Name
FROM [dbo].Parent P
INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id
Daha sonra Lookup
tabloya 00000000-0000-0000-0000-000000000000
for değeri olan bir satır eklerim , Id
böylece birleşimin sağında her zaman bir eşleşme olur.
Daha sonra gerektiğinde bu görünümde dizinler oluşturabilirim.
Ayrıca, Enterprise kullanmadığım için, NOEXPAND
bu dizinlerin kullanıldığından emin olmak için ipucunu kullanmam gerektiğini fark ettim :
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
Yanıtlar
İlk sorguyu düşünerek başlayalım.
Ebeveyn ve Arama arasına katılıyorsunuz, ancak bu bir dış birleşimdir, bu nedenle Ebeveynler sonuçlardan asla çıkarılmaz. Lookup.Id'nin benzersiz olduğunu tahmin edeceğim, bu nedenle hiçbir Ebeveynin katıldığı birden fazla Aramaya sahip olmayacak.
Bu nedenle, Üst Öğe'deki (Parent.Id tarafından sıralanan) 50000. satır, OFFSET cümlesine sahip değilsek sonuçlarda 50000. satır olacaktır.
Bu nedenle, sorgu, uzaklık için 50000 satırı geçebilir, sonraki 50 satıra bakabilir ve bunu Arama tablosuna katılmak için kullanabilir. Birleşimin hiçbir şey bulmaması önemli değil, bu bir sol dış birleşimdir ve sadece NULL döndürür.
Ana öğede farklı bir sütuna göre sipariş verirseniz ve bu, dizine eklenmişse, bu 50000 satırı aynı hızla geçebilir.
Şimdi ikinci sorguyu ele alalım.
Görmezden geldiğiniz 50000 satırın (uzaklığa göre) birleştirmenin sonuçlarına göre ilk 50000 olmasını istiyorsunuz. Bu 50000 satırları, Lookup tablosunda Parent.LookupId değerinin bulunmadığı bazı NULL satırlarını içerebilir. Parent. 50050 bile, ilk sorguda katıldığınız 50 satırdan çok daha fazlasıdır.
Şimdi, eğer bir yabancı anahtarınız varsa, işler biraz farklı olabilir. Daha sonra, SQL motoru bir değere sahipse Lookup.Name'in boş olmayacağını bilmelidir. Yani teorik olarak boş olanları bularak 50000 tane olup olmadığını görmekle başlayabilir. Ancak bu hala biraz zor ve SQL motorunun böyle bir plan üretmesi pek olası değil.
Ama yapabilirsin.
Bu yüzden ikinci sorgunun performansını çözmek için birkaç şey yapardım.
Boş olmayanları dikkate alarak başlayın. Bu, iç birleşimin parçası olan satırlar anlamına gelir. Bunun üzerinde indekslenmiş bir görünüm oluşturabilir, böylece istediğiniz sırada bir indeks sahibi olabilirsiniz.
Ancak, Parent.LookupID'nin boş olduğu yerlere de ihtiyacınız olacak - bunlar dışında, birleştirmeye hiç ihtiyacınız yok.
Bu iki kümede TÜMÜNÜ BİRLEŞTİRME yaparsanız (ve belki de her ikisine de sabit bir sütun eklerseniz, NULL satırlarının NULL olmayanlardan önce görünmesini sağlamak için), bazı gelişmeler görebilmeniz gerekir.
Bunun gibi bir şey:
SELECT ID, Name
FROM
(
SELECT i.ID, i.Name, 2 as SetNumber
FROM dbo.MyIndexedView i
UNION ALL
SELECT p.ID, NULL, 1 as SetNumber
FROM dbo.Parent p
WHERE p.LookupID IS NULL
) u
ORDER BY u.SetNumber, u.Name
OFFSET 50000 ROWS FETCH NEXT 50 ROWS ONLY;
Umarım planınız bir Merge Join (Birleştirme) operatörü içerecektir, böylece yalnızca endekslenmiş görünümde (Ad sırasına göre) bir Dizin Taramasından ve Üstte Dizin Arama'dan (Arama Kimlikleri için) ihtiyaç duyduğu satırları çeker.