SQL Server'da Lokal Değişkenlerin Performansa Etkisi
SQL Server’da lokal değişkenlerin kullanmanın dezavantajlarına ilişkin çok fazla soru soruluyor. Bu konuyu bir örnekle açıklamanın yararlı olacağını düşündüm. Öncelikle özellikle daha önceden yazılımcı olarak çalışanlar işlerini kolaylaştırdığı için stored procedure, fonksiyon gibi objelerin içinde her fırsatta kullanacakları değerleri değişkenlerde tutuyorlar. Fakat bu değişkenler bazı durumlarda SQL Server’ın hatalı executio plan oluşturmasına sebep olabiliyor. Şimdi örneğimizi yapmak için içinde 50.000 kayıt olacak aşağıdaki gibi bir örnek tablo oluşturalım.
CREATE TABLE LocalVarSample(ID UNIQUEIDENTIFIER,fullName VARCHAR(50), createdDate DATETIME )
Yukarıdaki gibi tablomuzu oluşturduktan sonra rastgele oluşturacağımız 50000 kaydı tablomuza insert edelim.
INSERT INTO LocalVarSample(ID, FullName, createdDate)
SELECT NEWID(),'ismail'+CAST((CAST(50000*RAND() AS INT) % 24) + 1 as VARCHAR) ,GETDATE()
GO 50000
Tablomuzun şuanki hali aşağıdaki gibi olacaktır.
Örnek sorgularımızı tablomuzda bulunan createdDate kolonuna göre yapacağımız için aşağıdaki gibi bir index oluşturalım.
CREATE NONCLUSTERED INDEX IX_createdDate ON LocalVarSample(createdDate) INCLUDE (ID)
Yukarıdaki gibi index’imizi de oluşturduktan sonra tablomuzu sorgulayalım fakat sorguyu çalıştırmadan önce kullancağımız SET STATISTICS IO ON ifadesi ile sorgumuz sonucunda yapılan I/O değerleri görelim. Ayrıca SQL Server’ın nasıl bir execution plan oluşturduğunu görmek için Actual Execution Planı da dahil edebiliriz. Aşağıdaki gibi sorgumuzu çalıştıralım.
SET STATISTICS IO ON
GO
SELECT * FROM LocalVarSample
WHERE createdDate >= '2013-09-27 16:01:15.793'
Yukarıdaki sorgu sonucumuzu aşağıdaki ekranda görebiliriz.
Yukardaki resimde gördüğümüz gibi toplamda 4 kayır listelendi. Şimdi yukarıdaki ekrandaki Messages penceresine geçerek I/O değerleirmize bakalım.
Aynı şekilde bir diğer penceren sorgumuzun Execution Planını da görebilriz.
Yukarıdaki sonuçları yorumlayacak olursak sorgu sonucumuzda toplamda 4 kayıt listelendi. Kayıtlar listelenirken 6 tane logical read yapıldı yani page okundu. Execution plan kısmında ise oluşturduğumuz index sql server tarafında kullanıldı. Ayrıca Index Seek işlemini temsil eden operatörün detaylarına baktığımızda Actual Number of Rows ve Estimated Number of Row değerlerinin kayıt sayımız olan 4'e eşit olduğunu görüyoruz. Şimdi aynı sorgumuzu bir değişken vasıtası ile çalıştıralım. Sorgumuz aşağıdaki gibi olacak.
SET STATISTICS IO ON
GO
DECLARE @RequiredDate DATETIME
SET @RequiredDate = '2013-09-27 16:01:15.793'
SELECT * FROM LocalVarSample
WHERE createdDate >= @RequiredDate
Sorgumuzun sonucu aşağıdaki gibi olacaktır.
Yukarıdaki resimde gördüğümüz gibi yine aynı 4 kaydı erişmiş olduk. Şimdi sorgumuzun peformansını gösteren I/O ve Execution Plan ksıımlarını inceleylim.
I/O değerlerini gördüğümüz yukarıdaki ekranı incelediğimizde logical read değeirnin 379 olduğunu görüyoruz. Halbuki sorgumuzun önceki halinde bu değer 6 idi. Şimdi de Execution Plan kısmını inceleyelim.
Execution Plan kısmında baktığımızda Actual Number of Rows değeri 4 iken Estimated Number of Rows değeri 15000’dir ve dikkat ederseniz aynı değerlerle sorguları çalıştırmamıza rağmen index kullanımı yerine SQL Server tarafından Table Scan işlemi tercih edilmiştir. Bunun sebebi ise Estimated Number of Rows değerinin yanlış hesaplanmasıdır. SQL Server Execution Planı oluşturuken bu değeri kullanır. Bu değer eğer ki statik olarak verilmiş bir değer ise Estimated Number of Rows istatistiklerden elde edilir ve eğer istatistik bilgilerimiz güncel ise hemen hemen Actual Number of Rows değeri ile aynı olacaktır. Fakat değişken kullandığımız SQL Server gelecek olan değeri tahmin edemediği için ise Estimated Number of Rows için aşağıdaki gibi bir formul kullanır.
Estimated Number of Rows=(Tablodaki kayıt sayısı * 30) /100
Yukarıdaki formule bakarsanız bizim Estimated Number of Rows değeirimizin (50000*30)/100 işlemi sonucunda 15000 çıktığını görebiliriz.