Bilmiyordum, Öğrendim : SQL Merge

Merhaba Arkadaşlar,

Gün geçmiyor ki çevremdeki insanlardan yeni bir şeyler daha öğrenmeyeyim. Bugün o günlerden biriydi...

İş yerinde elimizin her an üzerinde olabileceği binlerce SQL nesnemiz var. Tablolar, fonksiyonlar, sp'ler... Bazen iş biriminden gelen istekler doğrultusunda onlara müdahale etmemiz veya yenilerini yazmamız gerekiyor. Sorun şu ki 2000li yılların başından kalan ve yorum satırlarına bakıldığında üzerinden bir çok geliştiricinin geçtiği spl'lerimiz var. Bazen buradaki kalabalık sorgular arasında samanlıkta iğne ararcasına sorun çözmeye çalıştığımız oluyor. Çok motive edici bir durum değil takdir ederseniz ki. Şükür ki alanlarında yetkin ekip arkadaşlarımız var ve yeri geldiği zaman söyledikleri ufak bir ipucu ile hayatımızı kolaylaştırıyorlar (ki bu etkili yardımlaşmada agile metodolojide koşan bir takım olmamızın da büyük etkisi var)

İşte geçenlerde çok uzun sürdüğü için sorun yaratan bir sp(Stored Procedure) ile cebelleşirken değerli bir yardım geldi. Ekip arkadaşımın bir önerisi üzerine kendimi SQL Merge komutunu araştırırken/öğrenirken buldum. 2008den beri var olan benim bihaber olduğum bu komutu öğrenirken keyifli anlar da yaşadım. Normalde çok kötü bir SQLciyimdir ama Merge komutunu uygulamalı olarak denedikten sonra şirketteki o kallavi sorgunun hem daha da hızlandığını hem de daha okunur hale geldiğini gördüm. Sonunda konuyu kalem alıp paylaşmanın iyi olacağını fark ettim. Hem kendim için kayıt altına almış hem de yazıp çizerek konuyu daha iyi öğrenmiş olmam da ödülüm olacak tabii. Dilerseniz vakit kaybetmeden konumuza geçelim. Başlangıç için aşağıdaki veri içeriklerine sahip iki tablomuz olduğunu düşünelim.

Kaynak tablo içeriği(Book)

BookID      Title                                              ListPrice             StockLevel
----------- -------------------------------------------------- --------------------- ----------
1           Clean Architecture                                 34,55                 5
2           Clean Code                                         20,00                 5
3           Anti-patterns explained                            15,99                 10
4           Programming C#                                     50,40                 20

Hedef tablo içeriği(Store)

BookID      Title                                              ListPrice             StockLevel
----------- -------------------------------------------------- --------------------- ----------
1           Clean Architecture                                 34,55                 5
2           Clean Code                                         10,00                 5
3           Anti-patterns explained                            15,99                 8
6           Cloud for dummies                                  44,44                 3

Veritabanı ile çalışan pek çok uygulamada bu tip birleştirme odaklı tablolara rastlayabiliriz. Genellikle dışarıdan belirli periyotlarla beslenen bir tablo ve bu tablodaki veri içeriğine göre kendini sürekli olarak güncel tutan bir başka tablo olur. Aynen yukarıdaki senaryoda görülen Kaynak ve Hedef tablolar gibi. Özetle hedef tabloyu kaynak tablodaki değişikliklere göre güncel tutmak istediğimizi düşünebiliriz. Kaynaktan silinenlerin hedeften de silinmesi, güncellenenlerin aynı şekilde hedefte de güncellenmesi veya kaynağa yeni gelenlerin hedef tabloya da aktarılması gibi işlemlerden bahsediyoruz.

Book ve Store tablolarını göz önüne aldığımızda Store tablosundan silinen(4 nolu kayıt), eklenen(6 nolu kayıt), güncellenen(2 ve 3 nolu kayıtlar) ve hiç bir değişikliğe uğramayan(1 nolu kayıt) kitap bilgileri olduğunu görüyoruz. Şimdi Book tablosunu Store tablosuna göre güncellememiz gerekiyor. Elbette bunun bir çok yolu var. Örneğin Cursor açıp kaynak tabloyu baştan sona tarayarak bu işlemi gerçekleştirebiliriz. Ya da Select into ifadesi ile birlikte insert, update, delete sorgularını kullanabiliriz. Belki başka çözümler de söz konusu olabilir. Buradaki gibi az sayıda satır içeren veri kümeleri için seçilen tekniğin bir önemi yok aslında. Ancak tablo kayıt sayısı aynen şirketimizdeki senaryodaki gibi milyonlar seviyesine çıkınca performans sorunları yaşayabiliriz. Bir alternatif olarak üzerinde insert, update ve delete işlemlerini uygulamak için tek bir birleştirme maliyeti üzerinden hareket etmek çok daha verimli olabilir. Merge bu noktada devreye giriyor. 

Normal şartlarda yukarıdaki içerikleri eşleştirmek adına pekala aşağıdaki gibi sorgular yazılabilir (Bildiğim kadarı ile yazdım. Bu konuda alternatifler için aydınlatılmaya ihtiyacım var)

Update Book 
Set 
	Title=S.Title,
	ListPrice=S.ListPrice,
	StockLevel=S.StockLevel
from Store S
	inner join Book B
	on B.BookID=S.BookID
where 
	B.ListPrice<>S.ListPrice or B.Title<>S.Title or B.StockLevel <> S.StockLevel;

Delete from Book Where BookID not in 
	(Select S.BookID from Store S where S.BookID in (Select BookID from Book));

İlk olarak farklılıkları bulup gerçekleştirdiğimiz bir Insert işlemi var. Burada alt sorgu kullandığımızı görebilirsiniz. Güncelleme işleminde ise bir inner join kullanımına gittik. En beter sorgu da silme operasyonu için yazdığım olmalı sanıyorum ki. Bu sorguları işlettiğimizde Book ve Store tabloları eşlenecektir. Lakin bir taşla üç kuş vurabiliriz de. Şimdi konuyu merge ifadesini baz alarak ele alalım. Aşağıdaki uçtan uca sorgu işimizi görür(Ben diğer veritabanlarını kirletmemek adına LearningDb isimli ayrı bir veritabanında çalıştım)

Create database LearningDb;
Use LearningDb;

Create Table Book
(
	BookID int primary key,
	Title varchar(50),
	ListPrice money,
	StockLevel smallint
)
Go
insert into Book
Values
(1,'Clean Architecture',34.55,5),
(2,'Clean Code',20.00,5),
(3,'Anti-patterns explained',15.99,10),
(4,'Programming C#',50.40,20)
Go

Create Table Store
(
	BookID int primary key,
	Title varchar(50),
	ListPrice money,
	StockLevel smallint
)
Go
insert into Store
Values
(1,'Clean Architecture',34.55,5), --Değişiklik yok
(2,'Clean Code',10.00,5), -- Fiyat değişti
(3,'Anti-patterns explained',15.99,8), --Stok seviyesi değişti
(6,'Cloud for dummies',44.44,3) -- Yeni geldi
--(4,'Programming C#',50.40,20) -- Silindi
Go

Select * from Book;
Select * from Store;

Merge Book AS T
Using Store As S
on (T.BookID=S.BookID)
When Matched and T.Title <> S.Title Or T.ListPrice<>S.ListPrice Or T.StockLevel<>S.StockLevel Then --Herhangibir güncelleme varsa
Update Set T.Title=S.Title,T.ListPrice=S.ListPrice,T.StockLevel=S.StockLevel
When Not Matched By Target Then -- Yeni eklenmiş kitaplar varsa
Insert (BookID,Title,ListPrice,StockLevel)
Values (S.BookID,S.Title,S.ListPrice,S.StockLevel)
When Not Matched By Source Then -- Silinmiş kitaplar varsa
DELETE
OUTPUT $action [Event], DELETED.BookID as [Target BookID],DELETED.Title as [Target Title],DELETED.ListPrice as [Target ListPrice],DELETED.StockLevel as [Target StockLevel],
INSERTED.BookID as [Source BookID],INSERTED.Title as [Source Title],INSERTED.ListPrice as [Source ListPrice],INSERTED.StockLevel as [Source StockLevel];

Select * from Book;
Select * from Store;

Merge kısmına kadar yapılan hazırlıklarda örnek bir veritabanı oluşturup içerisine Book ve Store isimli tablolarımızı açıyoruz(Buralarda if exist kullanımına gitmekte yarar olabilir ya da başlarda drop table kullanılabilir) Sonrasında ise Merge ifademiz başlıyor. Book ve Store tablolarını BookID alanı üzerinden birleştirdikten sonra When kelimesi ile başlayan üç ayrı kısım yer alıyor.

Eğer bir eşleşme var ve tabloların Title, ListPrice, StockLevel alanlarının en azn birisinde veya tümünde farklılıklar söz konusuysa Then kelimesinden sonra gelen Update ifadesi çalıştırılıyor. Update ifadesinde T ile belirtilen hedef tablo alanlarının S ile belirtilen kaynak tablo alanları ile beslendiğine dikkat edelim. Eğer hedef tabloda kaynaktaki satırlar ile BookID üzerinden bir eşleşme yoksa 'When not matched by Target Then' sonrasında gelen Insert sorgusu çalışıyor. Burada da kaynak tablodaki alan değerlerinin eklendiğine dikkat edelim. Son olarak hedefte olduğu halde kaynakta olmayan satırlar varsa 'When not matched by source then' sonrasındaki Delete ifadesi çalışıyor ve hedef tablodaki ilgili kayıtlar siliniyor.

Merge sorgusunun tamamlanması için mutlaka ; işareti ile ifadeyi bitirmemiz gerekiyor. Bunu yapmadan önce meydana gelen değişiklikleri takip edebilmek adına output ifadesini çalıştırıyoruz. Burada $action değişkeni ile meydana gelen olay yakalanıyor(o satır için insert, update, delete olaylarından hangisi olduysa) DELETED ve INSERTED isimli hazır tabloları kullanaraktan da hangi tabloda ne gibi bir alan değişikliği olduğunu rahatlıkla görebiliyoruz. Sonuçlar aşağıdaki gibi olacaktır.

Event      Target BookID Target Title                                       Target ListPrice      Target StockLevel Source BookID Source Title                                       Source ListPrice      Source StockLevel
---------- ------------- -------------------------------------------------- --------------------- ----------------- ------------- -------------------------------------------------- --------------------- -----------------
UPDATE     2             Clean Code                                         20,00                 5                 2             Clean Code                                         10,00                 5
UPDATE     3             Anti-patterns explained                            15,99                 10                3             Anti-patterns explained                            15,99                 8
DELETE     4             Programming C#                                     50,40                 20                NULL          NULL                                               NULL                  NULL
INSERT     NULL          NULL                                               NULL                  NULL              6             Cloud for dummies                                  44,44                 3

(4 row(s) affected)

BookID      Title                                              ListPrice             StockLevel
----------- -------------------------------------------------- --------------------- ----------
1           Clean Architecture                                 34,55                 5
2           Clean Code                                         10,00                 5
3           Anti-patterns explained                            15,99                 8
6           Cloud for dummies                                  44,44                 3

(4 row(s) affected)

BookID      Title                                              ListPrice             StockLevel
----------- -------------------------------------------------- --------------------- ----------
1           Clean Architecture                                 34,55                 5
2           Clean Code                                         10,00                 5
3           Anti-patterns explained                            15,99                 8
6           Cloud for dummies                                  44,44                 3

(4 row(s) affected)

Artık her iki tablonun verileri de eş.

Gece yayınevlerinden son listeleri alan servis çalıştığında Store tablosunda yapılan değişiklikler, yukarıdaki sorgu sayesinde Book tablosuna da yansıtılacak ve o günün bayilerinin bakacağı asıl içerik eşleştirilmiş olacak. Bu senaryoyu bir düşünüp kurgulamaya çalışın derim. Görüldüğü üzere merge esasında oldukça pratik bir kullanıma sahip ve birleştirme senaryoları için ideal. Pek tabii kurumun iş kuralları gereği bir merge işlemi her zaman için buradaki using ifadesi kadar sade olmayabilir. Örneğimizde doğrudan primary key alanlar üzerinden bir eşleşme yaptık ancak farklı senaryolar olduğu takdirde using ifadesine parantez açılıp daha karmaşık select ifadelerine ait sonuçların kaynak olarak gösterilmesi de sağlanabilir. Lakin maliyeti yüksek olduğu için kaçınmaya çalıştığımız çeşitli sorguları(sub query'ler, çok sayıda tablolu join'ler vb) buraya almanın çok önemli bir pozitif katkısı olmayabilir. Sonuç itibariyle büyük veri kümelerini kullanarak performans testlerini yapmakta ve execution planlara bakıp gerekli müdahaleleri yapmakta yarar var. Bizim senaryomuz için çalışma zamanı planlarına baktığımızda en azından üç iş yerine tek seferlik bir maliyetin altına girdiğimizi görebiliriz.

İlk uygulama biçimimiz için aşağıdaki gibi bir plan oluşur.

Table Spool maliyetleri biraz yüksek görüldüğü üzere. Merge çalışma planında ise durum aşağıdaki gibidir. Şekilde görülmese de %25lik bir Full Outer Join maliyeti var.

İşin aslı konuyu SQL performans yönetimi konusunda uzman birisinin incelemesi daha doğru olabilir. Genellikle şirketlerin veritabanı operasyon ekipleri perfomans arttırımı gerektiren sorgular için destek oluyorlar. Yine de iş oraya gelmeden önce gerekli ön tedbirleri alıp performans iyileştirmelerini yapmak da biz geliştiricilere düşen önemli bir görevdir. Böylece geldik bir makalemizin daha sonuna. Tekrardan görüşünceye dek hepinize mutlu günler dilerim.

Yorumlar (4) -

  • Çiçek gibi olmuş abi işte daha ne olsun. Sayende merge ' ü branchlerden başka yerlerde de kullanabildiğimizi gördük Smile  Eline sağlık.
  • Günlük hayattan örnek vererek konuyu çok iyi anlatmışsınız. Daha önce bunu kullanırken zorlanıyordum ne gerek var diyordum. Şimdi ise ne güzelmiş diyorum. Elinize emeğinize sağlık.
    İyi çalışmalar...
  • Hocam emeğinize sağlık hayat kurtaran bir paylaşım oldu benim için Smile

Yorum ekle

Loading