Makale Özeti

SQL server da lokal değişkenlerin sorgu performansına etkisi ve Option (Recompile) ile execution planların tekrar oluşturulması anlatılmaktadır.

Makale

 

SQL – Lokal Değişkenler ve Sorgu Performansı | OPTION ( RECOMPILE)

Bir dile hakim olmanın olmazsa olmaz kaidesi, o dili geliştirdiğiniz platformun yazıklarınızı nasıl yorumladığını bilmenizden geçer. Asp.net ile yazılım geliştiriyorsanız, en basitinden IIS’in bir web sayfasına gelen isteği (request) nasıl karşıladığını,  nasıl cevaplandırdığını bilmeniz gerekir. Geliştirdiğiniz C# kodunun nasıl derlendiğini (compile) ve run time’da nasıl bir hal aldığı konusunda fikir sahibi olmanız gerekir.

Benzer bir durum tabi ki veritabanı tarafında da söz konusu. Teknik olarak aynı olmasını ümit ederek yazdığımız her kod aynı sonuçlar veremiyebiliyor. Özellikle de performans konusunda ciddi farklılıklar ortaya çıkabiliyor.

Bu yazımda, lokal değişken kullanımının, sorgu performansında yarattığı inanılmaz düşüşü, sebeplerini ve çözümlerini anlatacağım.

SELECT * FROM   tabloA WHERE  sutun1 = ‘XXX’;

ile

DECLARE @X CHAR(3) = ‘XXX’;
SELECT * FROM tabloA WHERE sutun1 = @X;

birbirinden ne kadar farklı olabilir ki? Lokal değişkenler sorgunuzun çalışma süresini ne kadar etkileyebilir? Ayrıca niye etkiler?

Programlama kültürü tarafından bakacak olursak, kod içinde hard-coded olarak string ifadeleri kullanmak, “temiz kodlama” kurallarına göre hatalıdır. C,C++,C#,VB.Net, Delphi,PHP… programlama kültürlerinden hangisinden geliyor olursa olsun bir yazılımcının, lokal değişkenleri tercih ediyor olması son derece normaldir. Hem daha düzenli, hem de daha yönetilebilirdir. Fakat T-SQL bu kültüre uygun bir programlama dili değildir. Dolayısıyla, alışı geldiğiniz programlama kültürünce doğru ve sağlıklı bulduğunuz bazı kavramlar, bu dünyada kabul görmez.

Yukarıdaki sorgulara ait Execution Plan hazırlanırken, sql server eriştiği tablolardaki istatistiklere bakarak yol haritasını netleştirir. Bu yüzden özellikle de index kullanılarak oluşturulacak planlarda, verinin hangi aralığı kapsadığının tespiti son derece önemlidir. Index kullanıp kullanmayacağına karar vermesinde bile hazırladığınız sorgu önemlidir. (SQL Server 2008′de maliyeti en az olan (cost based) Execution Plan otomatik olarak seçilmektedir.)

Eğer siz lokal değişken kullanırsanız, kullandığınız lokal değişkenlerin değerleri henüz derleme zamanında  (compile time)  belli olmadığı için, execution planlar mümkün olan en geniş aralığa göre hazırlanır.  Bu yüzden örneğin siz  sadece 30 ile 50 arasındaki bir filtre uygulayarak verileri çekmek isteseniz dahi, o bu alandaki en geniş aralığa göre bir plan oluşturacaktır. Bu da bazı durumlarda tam bir tablo taraması (Full Table Scan) yapmasına bile yol açabilir.  Bu da doğal olarak ciddi bir performans kaybına yol açacaktır.

Oluşturduğu bariz performans farklılığını ben ilk defa, tarih alanları üzerine gerçekleştirdiğim sorgularda fark ettim.

DECLARE @BUGUN DATETIME = GETDATE();
SELECT * FROM tabloA WHERE TARIH > @X;

ile

SELECT * FROM tabloA WHERE TARIH > GETDATE();

(İki sorgunun bire bir aynı olması için GETDATE() yerine CONVERT(VARCHAR(4), YEAR(GETDATE())) + ‘/’ + CONVERT(VARCHAR(2), MONTH(GETDATE())) + ‘/’ + CONVERT(VARCHAR(2), DAY(GETDATE()) kullanabilirsiniz.)

Lokal değişken kullandığım sorgu, diğerine göre çok yavaş çalışıyordu. Halbuki alışı geldiğim programlama kültürüne göre, yazım şekli olarak ilk sorgu daha doğruydu ve netice olarak da ikincisinden hiç bir farkı olmaması gerekiyordu. Ama oldu… Farkı oldu…

Çözümü için lokal değişken kullanarak hazırladığınız sorgularınızın sonuna OPTION(RECOMPILE) satırını eklemek.

DECLARE @BUGUN DATETIME = GETDATE();
SELECT * FROM tabloA WHERE TARIH > @X  OPTION(RECOMPILE)

Bu sayede lokal değişkenlerini kullandığınız sorgunuzun execution plan’ının, yeniden derlenen ve değerleri artık bilinen değişkenlere göre oluşturulmasını sağlarsınız.

Ya da…

Bu sorguyu içeren bir SP yazıp, lokal değişkenlerinizi parametre olarak SP’ye geçebilirsiniz. Netice de parametre olarak geçeceğiniz değerler, önceden bildiği için Execution plan buna göre oluşturulacaktır.

Başka bir alternatif de sp_executesql yordamını kullanarak olacaktır. Bu yöntem ile de lokal değişkenlerinizi parametre olarak geçmiş olacağınız için yine lokal değişken kullanımına göre daha performanslı olacaktır.

Bu konu aslında, teorik olarak çok daha detaylı ve geniş bir konu… Hiç bir şey anlatmaya çalıştığım kadar yüzeysel ve basit değil malesef. Index yapıları, algoritmaları, execution plan oluşturma, tablo istatistikleri… gibi bir çok temel konuyla ilişkili…

Sadece lokal değişken kullanımındaki performans kaybının neden olduğunu ve nasıl girebileceğini görmüş olduk.

Görüşmek üzere,

Mehmet Özakan

www.mehmetozakan.com