Birleştirilmiş bir tablonun bir sütununa göre sipariş verirken performansı iyileştirme

Aug 17 2020

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, Parenttabloda 10 milyondan fazla satır vardır ve Lookuptabloda yaklaşık 5.000 vardır. Gerçek Parentuygulama, 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 Idsütunları için benzersiz kümelenmiş dizinleri vardır, için Parentkümelenmemiş dizini vardır LookupIdve için kümelenmemiş dizini Lookupvardı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, LookupIdNULL 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 uniqueidentifierkimlikler 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 Lookuptabloya 00000000-0000-0000-0000-000000000000for değeri olan bir satır eklerim , Idbö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, NOEXPANDbu 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

10 RobFarley Aug 17 2020 at 11:12

İ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.