Bir programcının dünyası

C# tarafında rastgele sayıya ihtiyacımız olduğunda Random class’ından yeni bir nesne üretip kullanabiliyoruz. Eğer rastgele sayıya Sql tarafında ihtiyacımız olursa yapmamız gereken, RAND() fonksiyonunu kullanmaktır.

RAND() fonksiyonu ile ilgili detaylı bilgiye MSDN‘de yeralan şu makaleden ulaşabilirsiniz.

RAND([seed]) fonksiyonu parametre olarak seed değeri alabilir ve geriye float tipinde sonuç döner.

seed parametresi int, smallint veya tinyint tipinde olabilir.

RAND() fonksiyonu aynı seed değeri ile her çalıştırıldığında aynı “rastgele” sayıyı döndürür. Eğer seed verilmezse, Sql Server rastgele bir seed değeri atayarak, “rastgele” sayı oluşturur.

Eğer RAND() fonksiyonundan float tipinde noktalı sayı değil, int tipinde tamsayı dönmesini istiyorsak, basit bir convert işlemine tabi tutabiliriz;

SELECT CONVERT(INT, RAND() * 100)

İstatistikler, verinin veritabanı nesnelerinde (tablo, index, vs) nasıl dağıldığı bilgisini içerdiği için, veritabanında en önemli nesnelerden biridir.

Veritabanı Yöneticilerinin (Database Administrators) eskimiş istatistiklerden şikayetlerini çok duydum. Yapılması gereken istatistikleri güncellemektir.

Eğer bir tablodaki indexlere ait istatistiklerin son güncelleme zamanını öğrenmek istiyorsanız, aşağıdaki sql sorgusunu çalıştırabilirsiniz;

SELECT
	NAME AS INDEX_ADI,
	STATS_DATE(OBJECT_ID, INDEX_ID) AS SON_GUNCELLEME
FROM
	SYS.INDEXES
WHERE
	OBJECT_ID = OBJECT_ID('HumanResources.Department')

Eğer tüm indexler için aynı bilgiyi almak istiyorsanız, WHERE filtresini kaldırmanız yeterli.

Benim test bilgisayarımda aldığım sonuç;

PK_Department_DepartmenID 2010-01-20 15:30:17:200
AK_Department_Name 2010-01-20 15:30:26:053

Eğer istatistiklerinizin zamanı geçmiş olduğunu düşünüyorsanız ve güncellemek istiyorsanız, aşağıdaki sql komutunu çalıştırabilirsiniz;

UPDATE STATISTICS HumanResources.Department

Bu komutu çalıştırdığınızda ilgili tablonun istatistik verilerinin güncellendiğini göreceksiniz. Eğer komutu WITH FULLSCAN komutu ile birlikte çalıştırırsanız, tablodaki tüm kayıtlar baştan taranacak ve istatistik baştan oluşturulacaktır.

UPDATE STATISTICS HumanResources.Department
WITH FULLSCAN

Dikkat : İstatistikleri güncellemek için yukarıdaki komutları, sadece istatistiklerinizin gerçekten eskimiş olduğuna inandığınızda, sorgularınız için oluşturulan query plan’ların hatalı olduğunu gördüğünüzde ve sql server’ın yoğun olmadığı zamanlarda çalıştırmanızı tavsiye ederim.

Kendi test bilgisayarımda, istatistik güncelleme komutunu çalıştırdıktan sonra, aşağıdaki sonuçları elde ettim;

PK_Department_DepartmenID 2010-01-30 21:10:06:560
AK_Department_Name 2010-01-30 21:10:06:594

Gördüğünüz gibi, ilgili tablodaki indexlerin istatistikleri güncellendi.

Sql Server‘da her sorgu çalıştırdığımızda, sorgu sonucu, etkilenen satır sayısı ile birlikte, sorguyu çalıştıran uygulamaya geri gönderilir.

Bazı durumlarda bu bilgi işimize yarasa bile, genellikle kullanmayız.

Sql Server‘ın bu bilgiyi hesaplamasını ve uygulamaya geri göndermesini engelleyerek, çok ufakta olsa kazanç sağlayabiliriz.

Yapmamız gereken, sorgudan önce aşağıdaki komutu çalıştırmak olacaktır;

SET NOCOUNT ON

Sql Server sadece ilgili sorgu için, etkilenen satır sayısını hesaplama işlemini yapmayacaktır.

Bilgisayar Programcılığı dünyasında genellikle bir işi yapmanın birden fazla yolu vardır. Özellikle veritabanı tarafında bu yollar daha da fazla olabilir. Uygulama Geliştiriciler olarak yeni bir kod parçası yazacağımız zaman, genellikle bu yollardan en çok kullandığımız bir tanesini seçeriz ve onu kullanırız.

Ne var ki, seçtiğimiz yol herzaman en doğru yol olmayabilir. Sql‘de join‘li sorgulama yaparken, filtreleme için kullandığımız yöntem de bunlardan biri olabilir.

Bu yazımda join‘li sorgularda filtreleme için seçebileceğimiz iki yolu karşılaştıracağım. Karşılaştırma yapmak için kendi bilgisayarımda kurulu olan Sql Server 2008 üzerinde AdventureWorkd2008 R2 veritabanını kullandım.

Eğer birden fazla tablonun join‘ler ile ilişkilendirildiği bir sorgu yazıyorsak, sonuç kümesini filtreleme için kullanabileceğimiz iki yöntem vardır;

WHERE Filtreleme

Sonuç kümesinde olmasını/olmamasını istediğimiz kayıtları sorgunun WHERE cümlesinde tanımlarız. Şablon;

SELECT
	[TABLE1.ALANADLARI],
	[TABLE2.ALANADLARI]
FROM
	[TABLE1]
	JOIN [TABLE2] ON [TABLE1.ALAN1] = [TABLE2.ALAN1]
WHERE
	[TABLE1.ALAN2] = DEGER AND
	[TABLE2.ALAN2] = DEGER

JOIN Filtreleme

Sonuç kümesinde olmasını/olmamasını istediğimiz kayıtları sorgunun JOIN cümlelerinde tanımlarız. Şablon;

SELECT
	[TABLE1.ALANADLARI],
	[TABLE2.ALANADLARI]
FROM
	[TABLE1]
	JOIN [TABLE2] ON [TABLE1.ALAN1] = [TABLE2.ALAN1] AND [TABLE1.ALAN2] = DEGER AND [TABLE2.ALAN2] = DEGER

AdventureWorks2008 R2 veritabanında aşağıdaki iki sorguyu çalıştırdıktan sonra, performans analizini yapalım;

WHERE Filtreleme

SELECT
	*
FROM
	Sales.SalesOrderHeader AS SOH WITH (NOLOCK)
	JOIN Sales.SalesOrderDetail AS SOD WITH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID
	JOIN Sales.SalesOrderHeaderSalesReason AS SOHSR WITH (NOLOCK) ON SOHSR.SalesOrderID = SOH.SalesOrderID
	JOIN Sales.SalesReason AS SR WITH (NOLOCK) ON SOHSR.SalesReasonID = SR.SalesReasonID
WHERE
	SOH.CustomerID > 15000 AND
	SOD.LineTotal > 2000 AND
	SR.SalesReasonID > 5

JOIN Filtreleme

SELECT
	*
FROM
	Sales.SalesOrderHeader AS SOH WITH (NOLOCK)
	JOIN Sales.SalesOrderDetail AS SOD WITH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID AND SOH.CustomerID > 15000 AND SOD.LineTotal > 2000
	JOIN Sales.SalesOrderHeaderSalesReason AS SOHSR WITH (NOLOCK) ON SOHSR.SalesOrderID = SOH.SalesOrderID
	JOIN Sales.SalesReason AS SR WITH (NOLOCK) ON SOHSR.SalesReasonID = SR.SalesReasonID AND SR.SalesReasonID > 5

Her iki sorgu da çalıştıktan sonra 1321 satır geri döndürdü. Performans incelemesini üç alanda yapacağız;

• CPU Kullanımı
• Disk Kullanımı
• Hafıza Kullanımı

Bu üç performans kriteri için verileri  SYS.SYSPROCESSES DMV’sinden alabiliriz. SYS.SYSPROCESSES için detaylı bilgiye MSDN‘deki şu makaleden ulaşabilirsiniz. Aşağıda ilgili üç performans kriteri için verileri elde edebileceğimiz sorgu var;

SELECT
	DB_NAME(SP.DBID) AS VERITABANI,
	EST.TEXT AS SORGU,
	CPU,
	PHYSICAL_IO AS DISK_OKUMA,
	MEMUSAGE AS HAFIZA_KULLANIM
FROM
	SYS.SYSPROCESSES AS SP
	CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) AS EST

Bu sorguyu çalıştırdıktan sonra benim test bilgisayarımda şu verileri elde ettim;

• VERITABANI : AdventureWorks2008R2
• CPU : 1965
• DISK_OKUMA : 211
• HAFIZA_KULLANIM : 2

• VERITABANI : AdventureWorks2008R2
• CPU : 156
• DISK_OKUMA : 0
• HAFIZA_KULLANIM : 2

Gördüğünüz gibi kaynak kullanımı açısından (özellikle Disk Okuma ve CPU Kullanımı açısından) JOIN Filtreleme, WHERE Filtrelemeden daha avantajlı.

JOIN Filtreleme tabloları eşlerken filtrelenmiş verileri kullanıyor, WHERE Filtreleme ise, önce tabloları eşleştiriyor sonra filtreliyor. Özellikle çok kayıt bulunan/bulunacak olan tablolarınıza sorgu yazarken, JOIN Filtreleme’yi kullanmanızı tavsiye ederim.

Sql Server‘da çalıştırılan her sorgu, aslında çalıştırılmadan önce derleme (compile) işlemine tabi tutulur. Bu derleme işlemi sonucunda Sql Server query plan denilen çıktıyı üretir.

Query Plan, query processor‘e (sorguyu işleyen birim), sorgunun ihtiyaç duyduğu veriler için veritabanında bulunan tablo ve index‘lere fiziksel olarak nasıl erişebileceğini söyler.

Fakat, query plan elde etmek için yapılan bu derleme işlemi, bazı sorgular için çok pahalı olabilir.

Sql Server, aynı sql sorgusunun defalarca çalıştırıldığı durumlarda, derleme işleminin yükünü azaltmak için, query plan cache denilen hafıza bölgesinde query plan‘ları önbellekler.

Query plan cache, önbellekleyeceği sorguları basit bir hash tablo‘da saklar. Hash tablo’nun iki alanı vardır, birinde sql sorgusunun kendisini, diğerinde derleme sonucu ortaya çıkan query plan‘ı saklar.

Sql Server, yeni bir sorgu çalıştıracağı zaman, ilk önce query plan cache‘te sorgunun query plan‘ı var mı diye bakar. Eğer bulursa, daha önce önbelleklenmiş bu query plan‘ı kullanır. Bulamazsa, ilk önce sorguyu yazım denetimine tabi tutar, sonra sorguyu derler ve oluşan query plan‘ı bu listeye ekler.

Query plan cache‘in getirdiği performans artışını ölçmek için, öncelikle Sql Server’daki query plan cache’i boşaltacağız;

DBCC FREEPROCCACHE

Dikkat : Bu komutu kullandığınızda, Sql Server üzerinde bulunan tüm query plan cache silinir. Veritabanı veya belli bir sorgu için temizleme mümkün değildir. Bu komutun Canlı Veritabanında (Production Database) kullanılmaması önerilir.

Şimdi Sql Server’ın sorguyu inceleme ve derleme işlemi için ne kadar vakit harcadığını bulmamız lazım. Yapmamız gereken, sorguyu çalıştırmadan önce aşağıdaki komutu çalıştırmak;

SET STATISTICS TIME ON

Şimdi sorgumuzu çalıştırabiliriz. Ben Sql Server 2008 için hazırlanmış örnek veritabanında (AdventureWorks 2008R2) aşağıdaki sorguyu çalıştırıyorum;

SELECT * FROM HumanResources.Employee WHERE BusinessEntityId IN (1, 2);

Sorgu sonucu;

SQL Server parse and compile time:
	CPU time = 0 ms, elapsed time = 12 ms.

(2 row(s) affected)

SQL Server Execution Times:
	CPU time = 0 ms,  elapsed time = 1 ms.

Gördüğünüz gibi, sorgunun parse ve compile işlemine tabi tutulması 12 ms. sürdü. Sorgunun çalıştırılması ise 1 ms. sürdü.

Aynı sorguyu tekrar çalıştırırsak, elde edeceğimiz sonuç;

SQL Server parse and compile time:
	CPU time = 0 ms, elapsed time = 1 ms.

(2 row(s) affected)

SQL Server Execution Times:
	CPU time = 0 ms,  elapsed time = 1 ms.

Query plan cache sayesinde, parse ve compile işlemi 12 ms. yerine 1 ms. sürdü.

Not : Sql Server’ın istatistik toplarken ulaşabileceği en düşük kesinlik süresi 1 ms.’dir. 1 ms.’den kısa süren işler için bile Sql Server 1 ms. raporlar.

Sql Server 2008 ile hayatımıza yeni bir operatör geldi : MERGE.

MERGE Operatörü koşullu olarak INSERT ve UPDATE yerine geçen bir operatördür.

İşlem yapmak istediğiniz kaydın varlığına bağlı olarak INSERT veya UPDATE işlemlerinden birisini gerçekleştirir.

Eskiden bu işi gerçekleştirmek için, her satır için çalışan bir döngü içerisinde satırın varlığını kontrol ettirdiğimiz IF kontrolü olurdu, ya INSERT cümlesini ya da UPDATE cümlesini çalıştırırdık.

MERGE operatörünün ana kullanım alanı DataWarehouse‘lar olacaktır.

OLTP mimarisinde veritabanınız içinde bulunan verilerinizin, OLAP mimarisinde veritabanınıza işlemek istediğinizde, varolan satırları UPDATE etmek, varolmayan satırları INSERT etmek istersiniz.

MERGE operatörü, bu karmaşık yapının oldukça basitleşmesini sağlıyor.

Kullanım şablonu;

MERGE INTO Tablo
USING (SELECT Cümlesi)
	WHEN MATCHED THEN
		UPDATE Cümlesi
	WHEN NOT MATCHED THEN
		INSERT Cümlesi

AdventureWorks 2008 veritabanında bulunan Production.UnitMeasure tablosunda bu özelliği nasıl kullanabileceğimizi bir örnek ile inceleyelim;

MERGE Production.UnitMeasure AS T
USING (SELECT 'KG', 'Kilogram') AS S (UnitMeasureCode, Name) ON (T.UnitMeasureCode = S.UnitMeasureCode)
	WHEN MATCHED THEN
		UPDATE SET Name = S.Name
	WHEN NOT MATCHED THEN
		INSERT (UnitMeasureCode, Name) VALUES (S.UnitMeasureCode, S.Name)

COUNT() sistem fonksiyonu, parametre olarak aldığı alandaki satır sayısını INT tipinde geri döndürür.

COUNT_BIG() sistem fonksiyonunun COUNT() fonksiyonundan farkı, elde ettiği sonucu BIGINT tipinde geri döndürmesidir.

INT veritipinin sınırları : -2^31 (-2,147,483,648) – 2^31-1 (2,147,483,647)

BIGINT veritipinin sınırları : -2^63 (-9,223,372,036,854,775,808) – 2^63-1 (9,223,372,036,854,775,807)

Eğer elde edeceğiniz sonucun INT veritipinin sınırlarına sığmayacağını düşünüyorsanız, yapmanız gereken COUNT_BIG() fonksiyonunu kullanmaktır.

Sql Server 2008 ile birlikte Çoklu Insert (Multiple Insert) özelliği de hayatımıza giriyor.

Microsoft bu özelliğin altında yatan tekniğe, “Table Value Costructor” adını vermiş.

Çoklu Insert (Multiple Insert) özelliği sayesinde Insert yapan DML cümlelerinde, birden fazla satırı işleme tabi tutabiliyoruz.

AdventureWorks 2008 veritabanında bulunan HumanResources.Department tablosunda bu özelliği nasıl kullanabileceğimizi bir örnek ile inceleyelim;

INSERT INTO
	HumanResources.Department
VALUES
	('İnsan Kaynakları', 'IK', GETDATE()),
	('Bilgi İşlem', 'IT', GETDATE()),
	('Muhasebe', 'MUH', GETDATE()),
	('Finans', 'FIN', GETDATE()),
	('Lojistik', 'LOJ', GETDATE()),
	('Kalite ve Eğitim', 'KVE', GETDATE())

Geçenlerde bir öğrencimin sorusu üzerine Sql Server’da en meşgul veritabanını nasıl bulabiliriz, sorusuna cevap arayacağız.

En meşgul veritabanı, muhtemelen en çok disk operasyonu yapan veritabanıdır diye düşünerek, en çok disk aktivitesinde bulunan veritabanını yakalamaya çalışmalıyız.

Sql Server ile ilgili aradığımız birçok sorunun cevabını DMV‘lerde olduğu için, öncelikle logical_read ve logical_write miktarlarını veren DMV bulmalıyız.

MSDN’de şu sayfadan detaylı bilgiye ulaşabileceğiniz SYS.DM_EXEC_QUERY_STATS, ihtiyacımız olan TOTAL_LOGICAL_READS, TOTAL_LOGICAL_WRITES, SQL_HANDLE isminde üç kolona sahip.

SQL_HANDLE kolonunda yer alan veriyi, veritabanını bulmak için kullanabiliriz. Yapmamız gereken SYS.DM_EXEC_SQL_TEXT DMF‘i ile CROSS JOIN işlemine tabi tutarak, DBID kolonuna erişmek. Böylece DB_NAME sistem fonksiyonu sayesinde veritabanının ismine ulaşabiliriz.

SELECT
	SUM(EQS.TOTAL_LOGICAL_READS) AS TOPLAM_OKUMA,
	SUM(EQS.TOTAL_LOGICAL_WRITES) AS TOPLAM_YAZMA,
	ISNULL(DB_NAME(EST.DBID), 'AdhocSQL') AS VERITABANI
FROM
	SYS.DM_EXEC_QUERY_STATS AS EQS
	CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EQS.SQL_HANDLE) AS EST
GROUP BY
	DB_NAME(EST.DBID)

Uygulamalarımızda verileri hash‘lemeye her zaman ihtiyaç duyarız. Verileri hash’lemek için kullanabileceğimiz birçok araç vardır.

Eğer veriyi veritabanı katmanında (Sql Server) hash’lemek istiyorsak, MSDN‘de şu sayfada bulunan makalede okuyabileceğiniz gibi HASHBYTES fonksiyonu ile  yapabiliriz.

HASHBYTES fonksiyonu iki parametre alır;

Algoritma: Hash’leme algoritması. Alabileceği değerler; MD2, MD4, MD5, SHA, SHA1

Veri: Hash işlemine tabi tutulacak veri

HASHBYTES fonksiyonu geriye VarBinary(8000) tipinde değer döndürür.

MD5 algoritması ile hash işlemi yapan örnek kod;

SELECT
	KULLANICI_KODU,
	KULLANICI_ADI,
	HASHBYTES('MD5', KULLANICI_SIFRE) AS KULLANICI_SIFRE
FROM
	T_KULLANICI WITH (NOLOCK)

CREATE PROCEDURE PR_KULLANICI_EKLE
(
	@KULLANICI_KODU VARCHAR(5),
	@KULLANICI_ADI VARCHAR(50),
	@KULLANICI_SIFRE VARCHAR(15)
)
AS
INSERT INTO T_KULLANICI
	(KULLANICI_KODU, KULLANICI_ADI, KULLANICI_SIFRE, KAYIT_TARIHI)
VALUES
	(@KULLANICI_KODU, @KULLANICI_ADI, HASHBYTES('MD5', @KULLANICI_SIFRE), GETDATE())

SELECT SCOPE_IDENTITY()

CREATE PROCEDURE PR_KULLANICI_LOGIN
(
	@KULLANICI_ADI VARCHAR(50),
	@KULLANICI_SIFRE VARCHAR(15)
)
AS
SELECT
	KULLANICI_KODU,
	KULLANICI_ADI,
	HASHBYTES('MD5', KULLANICI_SIFRE) AS KULLANICI_SIFRE
FROM
	T_KULLANICI WITH (NOLOCK)
WHERE
	KULLANICI_ADI = @KULLANICI_ADI AND
	KULLANICI_SIFRE = HASHBYTES('MD5', @KULLANICI_SIFRE)

Güvenlik ihtiyaçlarından ötürü kıymetli bilgilerin network’ten ulaşılmasını önlemek için, veriyi taşımadan önce hash‘lemek isteyebilirsiniz. HASHBYTES fonksiyonu bu işin son derece kullanışlı gözüküyor.

Not : Yazıyı yazdıktan sonra gördüm ki, Emre Ayrılmaz şuradaki makalesinde aynı konuyu işlemiş, okunmasında fayda var.

Özellikle katalog tablolarının CSV çıktılarına sıklıkla ihtiyaç duyarız. Aşağıdaki sql script’ini kullanarak, siz de tablolarınızdan CSV çıktı alabilirsiniz.

Öncelikle tablomuzun orjinal haline bakalım;

SELECT Name
FROM HumanResources.Shift WITH (NOLOCK)

Şimdi öyle bir sql sorgusu yazacağız ki, çıktımız şu şekilde olacak;

Day, Evening, Night

SELECT
	SUBSTRING((
		SELECT ', ' + Name
		FROM HumanResources.Shift WITH (NOLOCK)
		FOR XML PATH('')
	), 3, 8000) AS CSV

Sorguyu FOR XML anahtar kelimeleri ile çalıştırdığımıza ve PATH olarak ” (boş string) verdiğimize dikkat edin.

SELECT ', ' + Name
FROM HumanResources.Shift WITH (NOLOCK)
FOR XML PATH('')

Böylece aşağıdaki sonucu elde etmiş olduk;

Son olarak, sql sorgusunu SUBSTRING fonksiyonu içerisine koyduk, böylece baştaki virgül ve boşluk karakterlerinden (“, “) kurtulmuş olduk.