SQL Server Execution Plan’daki JOIN Türleri
Bu makalede SQL Server’da TSQL sorgusu yazarken kullandığımız JOIN türlerinin( yani mantıksal join INNER,LEFT,RIGHT,FULL,CROSS) arka planda fiziksel olarak hangi join’e dönüştüğünü ve arka planda dönüştüğü join işleminin performansını nasıl etkilediğini ve nasıl çözeceğimizi inceleyeceğiz.
Bu makaleyi okumadan önce “Execution Plan Nedir” ve “SQL Server’da Join Türleri” isimli makalelerimi okursanız bu makaleden daha fazla faydalanabilirsiniz.
SQL Server, bizim TSQL ile yazdığımız JOIN ifadelerini arka planda aşağıdaki join türlerine dönüştürür. Bu dönüştürme işlemi yapılırken aşağıdakilerden hangisi daha performanslı çalışacaksa sorguyu o şekilde çalıştırır.
- NESTED LOOPS JOIN
- MERGE JOIN
- HASH JOIN
Önce yukardaki kavramları açıklayacağız. Daha sonrada “SQL Server’da Join Türleri” isimli makalemizdeki sorguları kullanarak arka planda hangi join türlerine dönüştüklerini görebileceğimiz örnekler yapacağız. Ve sorgunun daha performanslı çalışması için bazı kolonlara index ekleyeceğiz. Index ekledikten sonra arka planda yapılan join işleminin(nested loop,merge,hash) nasıl değiştiğini göreceğiz.
NESTED LOOPS JOIN: Tablolardan birini iç(inner) diğerini ise dış(outer) olarak işaretler ve outer olarak işaretlenen tablodaki her satır için inner olarak işaretlenen tablodaki her satırı okur.
Eğer tablolardan biri küçük ve diğeri büyükse ve büyük tablonun join kolonunda index varsa bu join türü çok performanslı çalışacaktır. Nested Loop Join’in mantığını anlamanız için aşağıdaki resim iyi bir örnek olacaktır.
Aşağıdaki resimde gördüğünüz gibi sol taraftaki outer olarak işaretlenen tablodaki her satır için sağ taraftaki inner olarak işaretlenen tablonun her satırına bakılıyor. Eğer outer olarak işaretlenen tablonun join yapılan kolonunda index varsa çok performanslı çalışacaktır.
MERGE JOIN: Eğer join olacak iki tabloda küçük değilse fakat 2 tabloda join’e girecek kolonlarına göre sıralıysa(join’e girecek kolonlarda index varsa) merge join en performanslı seçenek olacaktır.
Bir örnek üzerinden ilerleyelim,
Aşağıdaki gibi bir join’in yapıldığını düşünün.
Select * from tablo1 INNER JOIN tablo2 ON tablo1.a=tablo2.b
Birinci tablodaki a kolonunda bir index var ve sıralı bir yapıda,
İkinci tablodaki b kolonunda da bir index var ve sıralı bir yapıda,
Böyle bir join sonucunda join’e girecek kolonların ikiside sıralı yapıda olduğu için merge join çok performanslın çalışacaktır.
Merge Join sıralı olan bu iki kolon ile yapılan bir join işleminde iki kolonu karşılaştırır ve eşitse sonuç olarak döndürür.
HASH JOIN: En sevmediğimiz join türüdür. Execution planda görmek istemeyiz. Büyük, sırasız ve index olmayan tablolar join işlemine girerse SQL Server bu iki tabloyu birleştirebilmek için HASH JOIN yöntemini kullanmak zorunda kalır.
İki tablodan küçük olan belleğe alınarak bir hash table oluşturulur. Daha sonra büyük tablo taranır ve büyük tablodaki hash değeri ile bellekteki hash table’daki hash değeri karşılaştırılarak eşit olanlar sonuç listesine eklenir.
Hash join’i gösteren resmi aşağıda bulabilirsiniz.
Peki bu join yöntemlerinden hangisi daha performanslıdır?
Hangi join türünü hangi join türüne dönüştürmeye çalışmalıyız?
Hash join’in performanssız bir şekilde çalıştığına hash join’i anlatırken değindik. Peki hash join’i nasıl diğer join türlerine dönüştüreceğiz bir örnek yaparak anlatalım. Bir önceki makalemiz olan “SQL Server’da Join Türleri” isimli makalemizde ki sorguları kullanarak ilerleyelim.
Örneğin INNER JOIN yapmak için kullandığımız sorguyu new query diyerek yeni bir query sayfası açalım ve yapıştıralım.
select s.SehirIsmi,my.YiyecekIsmi from Sehirler s
INNER JOIN MeshurYiyecekler my ON s.ID=my.SehirID
Daha sonra aşağıdaki resimdeki gibi Display Estimated Execution plan’a tıklayarak sorguyu çalıştırmadan tahmini execution plan’ını nasıl oluşturacağına bakalım.
İki tabloda da hiç index olmadığı için iki tabloda Table Scan işlemine tabi tutuldu ve daha sonra yine index olmadığı için Hash Join gerçekleştirildi.
Tablolardan birinde join işlemine girecek kolona index ekleyerek aynı işlemi tekrar yapalım. Örneğin MeshurYiyecekler tablosundaki SehirID kolonuna aşağıdaki script’i kullanarak index ekleyelim.
USE [Test] GO CREATE NONCLUSTERED INDEX [IX_SehirID] ON [dbo].[MeshurYiyecekler] ( [SehirID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO
Index’imizi ekledikten sonra tekrar biraz önce yaptığımız gibi tahmini execution plan’ına bakalım.
Execution plan’ı okurken her zaman sağ taraftan başlayarak okuruz. Aşağıdaki execution plan çıktısına baktığımızda;
Önce MeshurYiyecekler tablosunda oluşturduğumuz IX_SehirID isimli index’ten index seek(aradığı kaydı indexin tamamını taramadan bulması) yaptığını,
Daha sonra Sehirler tablosundan table scan yaptığını(tablonun tamamını tarayarak aradığı kaydı bulması) ve çıkan iki sonucu Nested Loop yöntemiyle birleştirdiğini,
Daha sonra MeshurYiyecekler tablosunda Clustered Index olmadığı için IX_SehirID isimli index’ten aldığı row id’yi kullanarak join sonucunda gelecek diğer kolonları RID Lookup yaparak tablodan getiriyor.
Son olarak bir önceki join ile RID Lookup’tan gelen veriler Nested Loops join türüyle birleştirilerek sonuç üretiliyor.
RID Lookup nedir?,
Clustered Index Nedir,
Clustered ve NonClustered Index farkları nelerdir? Sorularının cevaplarını aşağıdaki makalelerde bulabilirsiniz.(Detaylı olarak anlattım.)
“SQL Server’da Index Kavramı ve Performansa Etkisi“,
“SQL Server’da İstatistik Kavramı ve Performansa Etkisi“,(scan, seek bu makalede detaylı olarak incelendi.)
“Clustered Index ve Non Clustered Index Farkları“
MeshurYiyecekler tablosuna aşağıdaki script yardımıyla birde Clustered Index koyalım ve sonrasında execution plan’a tekrar bakalım.
USE [Test] GO CREATE UNIQUE CLUSTERED INDEX [IX_Clustered] ON [dbo].[MeshurYiyecekler] ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO
Execution plan’a tekrar baktığımızda aşağıdaki gibi RID Lookup yerine Key Lookup geldi. RID Lookup ve Key Lookup farkını “Clustered Index ve Non Clustered Index Farkları” isimli makalemde bulabilirsiniz.
Sorgumuzu iyileştirmeye devam edelim. Sehirler tablosundaki TableScan’ı kaldırmak için Sehirler tablosu için join işlemine giren ID kolonuna index koyalım. Non clustered Index ya da Clustered Index olması isimize yarar. ID kolonu Sehirler tablosu için belirleyici kolon olduğu için bu örnekte Clustered Index koymayı tercih edeceğim.
USE [Test] GO CREATE UNIQUE CLUSTERED INDEX [IX_Clustered] ON [dbo].[Sehirler] ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO
Sehirler tablosuna clustered Index koyduktan sonra execution plan’a tekrar bakalım.
Aşağıda gördüğünüz gibi sehirler tablosuna yapılan Table Scan işlemi Clustered Index seek’e döndü.
SQL Server bir önceki execution plan’da Index Seek + Key Lookup yapıyordu. Bir önceki execution planda hatırlayacağınız gibi önce index seek sonra join sonra key lookup ve sonra tekrar join işlemi gerçekleşiyordu.
Bunun yerine Clustered Index Scan + tek join işleminin maliyetinin daha az olduğuna karar verdi.
Tabi burda tablodaki kolon sayısı, verilerin büyüklüğü sql server’ın karar vermesindeki etkenler.
Sorgumuzda gördüğünüz gibi MeshurYiyecekler tablosunun SehirID kolonu join’e giriyor ve select kısmında da MeshurYiyecekler tablosunun YiyecekIsmi çekiliyor.
Aşağıdaki script yardımıyla daha önce oluşturmuş olduğumuz IX_SehirID index’inin included alanına YiyecekIsmi kolonunu ekleyerek Clustered Index Scan yerine IX_SehirID index’ini kullanmasını sağlayabilecekmiyiz bunu görelim.
USE [Test] GO SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [IX_SehirID] ON [dbo].[MeshurYiyecekler] ( [SehirID] ASC ) INCLUDE ( [YiyecekIsmi]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Script’i çalıştırdıktan sonra hala clustered Index scan işleminin execution planda kaldığını görüyorum.
Peki MeshurYiyecekler tablosuna birkaç kolon ekleyelim ve bu kolonları anlamsız değerlerle dolduralım.
Use Test GO BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.MeshurYiyecekler ADD test nchar(10) NULL, test2 nchar(10) NULL, test3 nchar(10) NULL, test4 nchar(10) NULL, test5 nchar(10) NULL GO ALTER TABLE dbo.MeshurYiyecekler SET (LOCK_ESCALATION = TABLE) GO COMMIT USE [Test] GO UPDATE [dbo].[MeshurYiyecekler] SET [test] = '0123456789' ,[test2] = '0123456789' ,[test3] = '0123456789' ,[test4] = '0123456789' ,[test5] = '0123456789' GO
Daha sonra tablomuzdaki kayıtları büyütmek için tabloya insert işlemi yapalım.
Insert işlemi yapmadan önce tablonun ID değerini otomatik alması için aşağıdaki script’i çalıştıralım.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_MeshurYiyecekler
(
ID int NOT NULL IDENTITY (1, 1),
SehirID int NULL,
YiyecekIsmi varchar(100) NULL,
test nchar(10) NULL,
test2 nchar(10) NULL,
test3 nchar(10) NULL,
test4 nchar(10) NULL,
test5 nchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_MeshurYiyecekler SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_MeshurYiyecekler ON
GO
IF EXISTS(SELECT * FROM dbo.MeshurYiyecekler)
EXEC('INSERT INTO dbo.Tmp_MeshurYiyecekler (ID, SehirID, YiyecekIsmi, test, test2, test3, test4, test5)
SELECT ID, SehirID, YiyecekIsmi, test, test2, test3, test4, test5 FROM dbo.MeshurYiyecekler WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_MeshurYiyecekler OFF
GO
DROP TABLE dbo.MeshurYiyecekler
GO
EXECUTE sp_rename N'dbo.Tmp_MeshurYiyecekler', N'MeshurYiyecekler', 'OBJECT'
GO
CREATE UNIQUE CLUSTERED INDEX IX_Clustered ON dbo.MeshurYiyecekler
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_SehirID ON dbo.MeshurYiyecekler
(
SehirID
) INCLUDE (YiyecekIsmi)
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
Daha sonra tablomuza aşağıdaki script yardımıyla kayıt ekleyelim. 1 dakika çalıştırıp script’i durdurabilirsiniz.
USE [Test] GO while(1=1) BEGIN INSERT INTO [dbo].[MeshurYiyecekler] ([SehirID] ,[YiyecekIsmi] ,[test] ,[test2] ,[test3] ,[test4] ,[test5]) VALUES (1 ,'Test' ,'0123456789' ,'0123456789' ,'0123456789' ,'0123456789' ,'0123456789') END
Script’i durdurduktan sonra execution plan’a tekrar bakalım. Gördüğünüz gibi sadece MeshurYiyecekler tablosundaki kolon sayısını ve kayıt sayısını arttırdım.(ID değerini otomatik artacak şekilde ayarlamamız execution plan’ı etkilemez)
SQL Server tablodaki satır sayısı(veri yoğunluğu) azken MeshurYiyecekler tablosunda oluşturduğumuz nonclustered index’i kullanmayı tercih etmedi. Tablo küçük olduğu için clustered index üzerinden verileri getirmenin daha az maliyetli olacağını hesapladı.
Fakat tablodaki veri büyüdükten sonra sorgumuzu cover eden(select ve where ifadesindeki kolonları barındıran bir index) index’i kullanmayı tercih etti.
Dikkatimizi çeken diğer bir nokta ise arka planda Merge Join yapması. Daha önce merge join yapmıyordu. Çünkü join işlemini yaparken MeshurYiyecekler tablosundaki non clustered index yerine clustered index’i kullanıyordu.
Join’e giren kolonlar olan Sehirler tablosunun ID kolonu ve MeshurYiyecekler tablosunun SehirID kolonu.
Aşağıdaki execution plan’a baktığımızda
Sehirler tablosunun IX_Clustered isimli index’i ClusteredIndexScan yapmış. IX_Clustered index’inde ID kolonu var.
MeshurYiyecekler tablosunun IX_SehirID isimli index’i IndexScan yapmış. IX_SehirID index’inde SehirID kolonu var.
Tablo küçükken Sehirler tablosunun ID kolonu ile MeshurYiyecekler tablosunun ID kolonu join oluyordu.
Tablomuz büyüdüğünde join işlemine giren sıralı(index’li) kolonlar üzerinden join işlemi merge join olarak gerçekleşti.
Gördüğünüz gibi aynı join sorgusu üzerindeki index’lere ve tablodaki kayıt sayısına göre hash join, nested loop ya da merge join olalbiliyor.
Neden Index Scan ve Clustered Index Scan yapıyor? Bunları Clustered Index Seek ve Index Seek’e çeviremez miyiz?
Çünkü join’inin sonucunda nerdeyse tüm satırlar geliyor. Sorguyu aşağıdaki gibi index üzerinden filtreleyecek yapıya getirirsek execution plan index seek ve clustered index seek’e dönecektir.
Neden Index Scan ve Clustered Index Scan yapıyor?
select s.SehirIsmi,my.YiyecekIsmi from Sehirler s INNER JOIN MeshurYiyecekler my ON s.ID=my.SehirID where my.SehirID=42
“Execution Planda Spool Kavramı(Eager Spool, Lazy Spool)” isimli makalemde execution plan’ın detaylarına girmeye devam edeceğiz. Sitemizin arama kısmında merak ettiğiniz konuyu içeren kelimeleri arayarak makalelerimize ulaşabilirsiniz.
Index Scan ve Index Seek farkları nelerdir?
RID Lookup ve KeyLookup farkları nelerdir?
Bu soruların cevaplarını “SQL Server’da İstatistik Kavramı ve Performansa Etkisi” isimli makalemde bulabilirsiniz.
