SQL Server : Tablo Değişkeni Kullanımı (Running Total, Yürüyen Bakiye Örneği)
Merhaba,
İş/güç derken ister istemez biraz ara vermek zorunda kaldım. Tabi bunlara ek olarak “bahar yorgunluğunun da” biraz etkisi olmadı değil… Çok uzatmadan sadede geleyim;
Bu yazımızda SQL Server’da tablo tipindeki değişkenleri irdelemeye, onları anlamaya çalışacağım ve nasıl kullanıldığına dair örnekler vereceğim. Bu örneklemelerde ise forumda son zamanlarda sıkça duymaya başladığımız “yürüyen toplam”, “devreden toplam”, “Kümülatif toplam”, “running total” gibi adlarla bildiğimiz fakat daha çok cari hesap ekstrelerinde gösterdiğimiz devreden bakiye hesaplamaya dair örnekler vermeye çalışacağım. Örnekleri karmaşıklaştırmadan, en basit nasıl olur yönünde tercihler kullanacağım. Başlayalım;
Tablo Değişkeni Nedir?
Tablo değişkenleri, diğer değişkenlerden farklı olarak veriyi bir değişken içerisinde tablo olarak tutar. Bilindik, sıradan tablolarda veya temp tablolarda yaptığınız bir çok işlemi tablo değişkenlerinde de rahatlıkla yapabilirsiniz. Fakat hem normal, hem de temp tablolardan bazı noktalarda farklılaştığını ve yapısal olarak aslında bunların birer değişken olduğunu unutmamalıyız.
Microsoft, SQL Server 2000’den itibaren tablo değişkenlerini temp tablolara (sanırım) bir alternatif oluşturması maksadıyla tanıttı. Fakat sırf alternatif olsun diye bunu yapmak işgüzarlık olurdu, o nedenle buradaki nüansın “performans odaklı” olduğunu belirtmek isterim. Çoğu durumda bir tablo değişkeni ikiz kardeşi olan temp tablolardan çok daha hızlıdır. Bunun temel sebebi tablo değişkenlerinin “neredeyse” (ve hatta mecbur kalmadıkları sürece) hiç bir şekilde disk ile haşır neşir olmaması ve tüm sürecin bellek üzerinde gerçekleştirilmesinden kaynaklanmaktadır. Buna ek olarak arka tarafta, yapısal anlamda (bir temp tabloya nazaran) daha az sistem değişkeni tutması nedeniyle tablo değişkenleri, temp tablolara nazaran çok daha optimize edilmiş bir yapıya sahiptir. Daha optimize olmalarının temel sebebini ise bellek yönetimi ve performans odağı açısından değerlendirmek gerekir… Özetleyecek olursak Tablo Değişkenleri, bellekte işlem gören optimize edilmiş bir çeşit temp tablodur.
Tablo değişkeninin nasıl tanımlandığı ile ilgili ufak bir örnek verelim, örneğimiz running total için de bir temel teşkil etsin bu arada;
DECLARE @TabloDegiskeni TABLE --> Bir tablo değişkeni tanımlıyoruz.
( ID INT IDENTITY(1,1) --> Sıralı artan bir alan tanımlıyoruz. İçi resen (kendiliğinden (otomatik) ) olarak ardışık dolacaktır...
, Tarih DATETIME NULL --> Valör tarihini ifade eder.
, Islem VARCHAR(255) NULL --> Açıklamayı ifade eder.
, Tutar MONEY NULL --> İşlem tutarını ifade eder.
, Bakiye MONEY NULL --> ŞİMDİLİK BOŞ, BU KISMI İLERİDE HESAPLATACAĞIZ...
)
Kapsamı ve Özel Durumlar
Tablo değişkenlerinin bellekte işlem gördüğünden sözettik, peki işleyeceğimiz veriler bellekten daha fazla yer kaplıyorsa o zaman ne olacak? Şahsen tercihim o tür durumlarda tablo değişkenini değil, temp tablonun kullanılması gerektiği yönündedir. Verinin bellekten daha fazla yer kapladığı durumlarda doğal olarak, SQL Server ister istemez diske erişim sağlamak zorunda kalacaktır. Haliyle performans da temp tablo seviyesine kadar düşecektir. Bunda bir behis göremeyebilirsiniz fakat bu durumun (yani büyük bir sql sorgusunun disk ile haşır neşir olmaya başlaması durumunun) ağır bir yan etkisi daha vardır. Özellikle bellekten daha büyük verileri sürekli işlemeye çalıştığınızda sunucunun genel performansının da etkilendiğini gözlemleyebilirsiniz. O nedenle bu uyarımı sadece tablo değişkenleri için değil, tüm query’leriniz için temel bir kural olarak benimsemelisiniz.
SQL Server’daki diğer veri türlerinin bir çoğunun aksine bir tablo değişkenini giriş/çıkış parametresi olarak kullanamazsınız. Malesef, Yani bir fonksiyona veya saklı yordama parametre olarak bunları veremezsiniz. Fakat kullanıcı tanımlı tablo değerli bir fonksiyondan tablo değişkeni döndürebilirsiniz. (Ki bu konu ile ilgili bir örneği başka bir yazımda vermiştim; http://www.delphican.com/sql-server-tablolar-icin-parametrik-sirali-alan-listesi.html )
Buna ek olarak tablo değişkenini oluştururken varlığını kontrol etmenize de gerek yoktur. Bu biraz yanıltıcı bir ifade oldu sanırım; Yani mesela bir temp tabloyu oluşturmadan önce biz veya başka bir kullanıcı aynı isimle bir temp tablo üretmiş mi üretmemiş mi normalde bakmamız gerekir, fakat tablo değişkeninde böyle bir durum sözkonusu değildir. Tablo değişkeni kullanıldığı yerde kendiliğinden SQL Server tarafından yok edilir. Otomatik bir garbage collection durumu söz konusudur.
Performans
Tanım açısından bir Tablo değişkeninin (temp tabloya nazaran) kısıtlı bir kapsamı vardır dolayısıyla SQL Server’a optimizasyon açısından geniş bir hareket alanı sağlar. Yani SQL Server tablo değişkenini temp tablodan daha çok sever de diyebiliriz…
İyi tanımlanmış kapsamı nedeniyle tablo değişkenleri temp tablolara nazaran daha az sistem kaynağına ihtiyaç duyar. Tablo değişkenini birebir, doğrudan etkileyen işlemler ise daha çok UPDATE sürecinde baskın haldedir. Tablo değişkeninin bu davranışının bir yan etkisi olarak stored procedure’lerde tablo değişkenleri kullanıldığında prosedürün yeniden derlenmesini gerektirecek durumlar da oluşabilir. Saklı yordamlar açısından bu pek de istenen bir durum değildir. Saklı Yordamların (Stored Procedure) neden yeniden derlenebileceğine dair çözümleri de içeren daha fazla bilgi için aşağıdaki linke göz atabilirsiniz;
https://www.sqlshack.com/frequent-query-recompilations-sql-query-performance-killer-detection/
Kısıtlamalar ve istisnalar
Tablo değişkenleri için “ALTER TABLE”, “SELECT INTO” ve “INSERT EXEC” gibi ifadeler kullanamayız.
SQL Server 2014’den önceki sürümleri için Tablo değişkenlerinde Non-Clusteded indeks oluşturamayız. Fakat Index, Primary Key veya UNIQUE index’leri bu amaca uygun şekilde kullanmayı “deneyebilirsiniz”. Bununla birlikte SQL Server 2014 CTP1 ve sonraki sürümler için ise böyle bir kısıtlama yoktur. Kişisel tercihim ise non clustered indeksi bir tablo değişkeninde kullanmamanın kullanmaktan çok da kârlı olmadığını gösteriyor. Takdir sizin, isteyen kullanır isteyen kullanmaz. Kullanmak isteseydik şöyle bir şey yapmamız gerekirdi;
DECLARE @TabloDegiskeni TABLE
( ID INT IDENTITY(1,1)
, Tarih DATETIME NULL
, Islem VARCHAR(255) NULL
, Tutar MONEY NULL
, Bakiye MONEY NULL
, INDEX IX_TabloDegiskeniIndeksi NONCLUSTERED(Tarih, Islem desc) --> NON CLUSTERED İNDEKS TANIMI
)
Bir kısıtlama mı yoksa performansa dair bir nimet mi bilemem, nereden baktığınıza göre bu değişir fakat SQL Server, tablo değişkenleri için istatistikler de tutmaz. Sonuç olarak SQL Server, tablo değişkeni ile ilgili sorguyu en iyi nasıl çalıştırabileceğine dair bir çaba içine girmez… Zira buna gerek de olmayabilir. Bunun temel sebebi tablo değişkeninin dar bir kapsama sahip olması olabilir çünkü bu tip değişkenler çok spesifik amaçlar için tanımlanırlar ve genel geçer kullanımlar için pek de uygun değildirler. Bu elzem bir durum ise temp tablo kullanmayı tercih edebilirsiniz.
Tablo Değişkenleri Transaction’lardan da etkilenmez! Bu performans açısından bir avantaj olsa da “veri güvenilirliği” (hesaplamanın doğrulu) açısından çok tehlikeli bir davranıştır. Bu davranıştan haberdar değilseniz hatanın nereden kaynaklandığını bulmak için gerçekten çok zaman kaybedersiniz…
Basit Bir Örnek
Bu kadar teorik bilgiden sonra biraz da örneklemelerle devam edelim, açıklamalar örneğin içinde devam edecek;
DECLARE @TabloDegiskeni TABLE --> Bir tablo değişkeni tanımlıyoruz.
(
ID INT IDENTITY(1,1) --> Sıralı artan bir alan tanımlıyoruz. İçi resen (kendiliğinden (otomatik) ) dolacaktır...
--> ID INT IDENTITY(1,1) PRIMARY KEY --> VEYA ! bu alanı hem sıralı hem de birincil anahtar olarak da tanımlayabilirdik...
, Tarih DATETIME NULL --> İşlemin gerçekleştiği günü ifade eder.
, Islem VARCHAR(255) NULL --> İşlemin Açıklamasını ifade eder.
, Tutar MONEY NULL --> İşlem tutarını ifade eder.
, Bakiye MONEY NULL --> ŞİMDİLİK BOŞ, BU KISMI UPDATE SIRASINDA HESAPLATACAĞIZ...
, INDEX IX_TabloDegiskeniIndeksi NONCLUSTERED(Tarih, Islem desc)
)
--> BEGIN TRANSACTION --> ETKİSİZDİR, tablo değişkenleri bundan etkilenmez (yani bu satırı bu örnek özelinde silmeniz için hiç bir engel yok...)
INSERT INTO @TabloDegiskeni (Tarih, Islem, Tutar) --> Tarih, Açıklama ve işlem tutarını tablo değişkenimize dolduruyoruz...
VALUES ( '2017-01-06', 'Tahsilat' , 100 )
, ( '2017-02-05', 'Tahsilat' , 110 )
, ( '2017-03-04', 'Ödeme' , -150 )
, ( '2017-04-03', 'Ödeme' , -50 )
, ( '2017-05-02', 'Tahsilat' , 360 )
, ( '2017-06-01', 'Ödeme' , -150 ) --> Verilerin tarihe göre sıralı olduğuna dikkat edin. (Sadece devreden bakiye açısından bir önemi vardır)
DECLARE @Devreden MONEY = 0; --> Her bir update işlemi sırasında bu değişken bize bir önceki update işleminde elde kalan bakiyeyi unutmammamızı sağlayacak.
UPDATE @TabloDegiskeni
SET @Devreden = Bakiye = @Devreden + ISNULL(Tutar, 0)
-- ^ ^ ^ <-- Eşittir ifadelerine dikkat edelim. --> ROLLBACK TRANSACTION --> ETKİSİZDİR, tablo değişkenleri bundan etkilenmez, eğer etkilenseydi aşağıdaki select ifadesinde tablonun boş gelmesi gerekirdi... (yani bu satırı bu örnek özelinde silmeniz için hiç bir engel yok...)
SELECT * FROM @TabloDegiskeni
Örneğimizin sonucunda oluşacak olan tablo ise aşağıdaki gibi olacaktır;
ID Tarih Islem Tutar Bakiye -- ----------------------- -------- ------- ------ 1 2017-01-06 00:00:00.000 Tahsilat 100,00 100,00 2 2017-02-05 00:00:00.000 Tahsilat 110,00 210,00 3 2017-03-04 00:00:00.000 Ödeme -150,00 60,00 4 2017-04-03 00:00:00.000 Ödeme -50,00 10,00 5 2017-05-02 00:00:00.000 Tahsilat 360,00 370,00 6 2017-06-01 00:00:00.000 Ödeme -150,00 220,00
Kod örneğimizde devreden bakiyeyi hesaplarken pek sık karşılaşılmayan bir tekniği kullandım. Bu tekniğin bir benzerini MySQL’de de kullanabilirsiniz. Kısaca teknikten bahsetmek gerekirse SQL Server verileri güncellerken (eğer bu bir tablo ise) işleme tepeden, tablonun en başından, yani etkilenecek olan kayıtların en başından sürece başlar ve aşağıya doğru “sırayla” ilerler. “@Devreden” değişkeni de bu noktada devreye giriyor. Biz bakiye sütununu hesaplarken;
UPDATE @TabloDegiskeni
SET @Devreden = Bakiye = @Devreden + ISNULL(Tutar, 0)
Yukarıdaki satırda aslında şunu söylemiş oluyoruz; @Devreden ile Tutar alanını topla, sonucu Bakiye alanına yaz. Bakiye alanının yeni değerini de @Devreden değişkenine aktar. demiş oluyoruz.
Devreden Bakiye, Yürüyen Bakiye, Running Total kavramıyla veya hesaplamalarıyla ilgili bir çok şey söylenebilir, bir çok farklı teknik uygulanabilir, yukarıdaki örneği bunlardan sadece birisi olarak değerlendirebilirsiniz.
Tablo Değişkeni mi, Geçici Tablo mu?
Ben, şahsen temp tabloyu veya tablo değişkenini nerede kullanacağımı şunlara göre belirliyorum (Fakat önerilere de her zaman açığım);
- Sonuç kümesinin boyutu, kapladığı / kaplayacağı alan çok yer kaplıyor mu? Eğer öyleyse temp tablo kullanmak sunucunun performansını düşürmeden sonuç kümesiyle baş etmenin en kolay yoludur. Böyle bir durumda tablo değişkeni yerine temp tablo kullanmayı daha çok tercih ederim.
- Üreteceğim çözümü Stored Procedure kullanarak mı gerçekleştireceğim? Eğer Stored POrocedure kullanacaksam yine temp tabloyu tablo değişkenine tercih ederim.
- Transaction’a ihtiyacım var mı? Varsa yine temp tablo…
Bunların dışındaki durumlarda geçici bir tablo kullanmam gerekiyorsa her zaman tablo değişkenini tercih ederim. Dolayısıyla Tablo Değişkeni candır, Temp tablodan çok daha basit, çok daha hızlı ve başetmesi çok daha kolaydır.
Yorum yapılmamış