SQL Server’da Selective XML Index: Büyük XML Kolonlarını Hızlı Sorgulamak

Birçok uygulama SQL Server’da XML saklar: e-fatura ve e-irsaliye belgeleri, entegrasyon mesajları, yapılandırma kayıtları, denetim izleri. Tablo büyüdükçe bu XML’lerin içinde arama yapan sorgular yavaşlar; çünkü SQL Server her satırdaki XML’i çalışma anında ayrıştırmak zorundadır. Klasik çözüm olan primary XML index ise belgenin tamamını parçalara ayırarak saklar ve çoğu zaman tablonun kendisinden daha büyük bir index üretir.

Selective XML index bu iki uç arasında daha akıllı bir yol sunar: XML’in yalnızca gerçekten sorguladığınız yollarını index’ler. Bu yazıda nasıl çalıştığını, ne zaman doğru seçim olduğunu, nasıl oluşturulduğunu ve sınırlarını anlatıyoruz.

Primary XML Index ile Farkı

Microsoft da çoğu durumda primary XML index yerine selective XML index önerir. İstisnalar: çok sayıda farklı yolun index’lenmesi gerektiğinde ya da belgede konumu önceden bilinmeyen elemanlar sorgulandığında.

Ön Koşullar

• Tablonun primary key’i üzerinde bir clustered index bulunmalıdır.
• Primary key en fazla 128 bayt, clustering key en fazla 15 kolon olabilir.
• Her XML kolonunda yalnızca bir selective XML index olabilir.

Örnek: E-Belge Tablosunda Arama

Belge numarası, alıcının vergi numarası, tarih ve kalemlerdeki ürün kodlarının bulunduğu bir XML kolonunu düşünelim. Sorgular yalnızca bu dört yolu kullanıyorsa, yalnızca bunları index’lemek yeterlidir:

CREATE TABLE dbo.EBelge
(
    BelgeId  bigint IDENTITY PRIMARY KEY CLUSTERED,
    Icerik   xml NOT NULL
);

CREATE SELECTIVE XML INDEX SXI_EBelge
ON dbo.EBelge (Icerik)
FOR
(
    belgeNo  = '/Belge/BelgeNo'         AS SQL nvarchar(30) SINGLETON,
    aliciVkn = '/Belge/Alici/Vkn'       AS SQL varchar(11)  SINGLETON,
    tarih    = '/Belge/Tarih'           AS XQUERY 'xs:date' SINGLETON,
    urunKodu = '/Belge/Kalem/UrunKodu'  AS XQUERY 'xs:string'
);

Her yol üç parçadan oluşur: benzersiz bir ad, yolu tanımlayan XQuery ifadesi ve isteğe bağlı optimizasyon ipuçları.

• AS SQL tip: Değer doğrudan bir SQL veri tipiyle saklanır; value() ile okunan ve karşılaştırılan tekil alanlar için en verimli seçimdir. Tarih ve saat tipleri (date, datetime, datetime2) burada kabul edilmez ve Msg 6375 hatası verir; tarihleri AS XQUERY 'xs:date' ile index’leyin.
• SINGLETON: Yolun her belgede en fazla bir kez geçtiğini belirtir; optimizer’ın daha basit planlar üretmesini sağlar. Yalnızca gerçekten tekil alanlarda kullanın.
• AS XQUERY tip: Bir belgede birden çok kez geçebilen yollar (örneğin fatura kalemleri) için.

Index’ten yararlanan sorgular, XML’i her satırda ayrıştırmak yerine index’lenmiş değerleri kullanır:

-- Belirli bir alıcıya ait belgeler
SELECT BelgeId, Icerik.value('(/Belge/BelgeNo)[1]', 'nvarchar(30)') AS BelgeNo
FROM dbo.EBelge
WHERE Icerik.exist('/Belge/Alici[Vkn = "1234567890"]') = 1;

-- Belirli bir ürünü içeren belgeler
SELECT BelgeId
FROM dbo.EBelge
WHERE Icerik.exist('/Belge/Kalem[UrunKodu = "PRD-100"]') = 1;

Secondary Selective XML Index

Çok seçici bir yol üzerinde sık filtreleme yapıyorsanız, o yola ek bir secondary index ekleyebilirsiniz:

CREATE XML INDEX SXI_EBelge_AliciVkn
ON dbo.EBelge (Icerik)
USING XML INDEX SXI_EBelge FOR (aliciVkn);

Hangi Sorgular Yararlanır?

Selective XML index’ler exist(), value() ve nodes() yöntemlerindeki ifadeleri tamamen karşılayabilir. query() ve modify() yöntemlerinde yalnızca satır filtrelemede kullanılır; sonuç üretmede ya da XML güncellemede kullanılmaz.

Desteklenmeyenler arasında /a/b/* gibi sonu joker karakterle biten yollar, child, attribute ve descendant dışındaki eksenler, base64Binary gibi ikili tipler ve yorum ile işlem talimatı düğümleri bulunur.

Sınırlar

• Index, XML’i tek bir ilişkisel tabloya eşler ve sparse kolonlar kullanır. Pratikte index’lenebilecek düğüm sayısı, veriye ve // kullanımına bağlı olarak 60 ile birkaç yüz arasındadır.
• View’lardaki XML kolonlarında, tablo değişkenlerinde, XML değişkenlerinde ve hesaplanmış XML kolonlarında oluşturulamaz.
• 128 seviyeden derin iç içe XML’lerde kullanılamaz.

Kontrol Listesi

• En sık çalışan XML sorgularının kullandığı yollar belirlendi.
• Tablonun primary key’i üzerinde clustered index var.
• Tekil alanlar AS SQL ve SINGLETON ile, tekrarlayan alanlar AS XQUERY ile eşlendi.
• Index oluşturma bakım penceresinde planlandı.
• Sürekli sorgulanan alanlar için ilişkisel kolonlara taşıma değerlendirildi.