Giriş

Müşteri ve kullanıcılar veri alma işleminde her zaman hızlı yanıt almak ister. Dolayısıyla, bir uygulamanın veri manipülasyonu sırasında iyi performans sağlayan bir veritabanı tasarımına sahip olması gerekir. Bununla birlikte, en iyi performansı tanımlamanın basit bir yolu yoktur; ancak test edilmiş bazı teknikleri uygulayarak önemli kazanımlar elde edebilirsiniz. Geliştirici olarak, herhangi bir SQL sorgusunun birden fazla yolla yazılabileceğini biliyorsunuzdur, daha iyi sorgu performansı elde etmek için en iyi uygulamaları ve teknikleri takip etmelisiniz. Aşağıda bazılarını paylaşıyoruz;

[sociallocker]

Sadece ihtiyacınız olanı isteyin

Hangi veri tabanı yada programlama dilini kullandığınız önemli değil, performans için bilmeniz gereken ilk ve en önemli konu; istemci ve sunucu arasındaki veri trafiğinin minimumda tutulması gerektiğidir.

Sorgu gönderirken sadece ihtiyacınız olan sütunları isteyin;

Yüzlerce sütun ve milyonlarca satır içeren bir tablo düşünün. Uygulamanız yalnızca birkaç sütuna ihtiyaç duyarsa, tüm verileri sorgulamak mantıklı değildir. Örnek olarak “Ürünler” adındaki bir tablodan [ID] ve [Kodu] alanlarını almak istiyorsanız, sorguda sadece bu alanları talep edin. SELECT * bu senaryo için tamamen gereksizdir ve performansı düşürür.

SELECT *
FROM
[Ürünler] AS [t0]
--Sure: 00:00:430

SELECT [t0].[ID], [t0].[Kodu]
FROM [Ürünler] AS [t0]
--Sure: 00:00:21

Sorgu gönderirken sadece ihtiyacınız olan satırları isteyin;

Sunucudan talep ettiğiniz veriyi mümkün olduğu kadar sunucu tarafında filtreleyin. Sunucudan istemciye transfer edilen veri boyutu ile işlemin tamamlanma süresi genelde doğru orantılıdır. Örnek olarak henüz hiç satılmamış ürünleri bulmaya çalışalım.

Birinci örnekte ürünler ve satışlar tablosundan ürün keylerini okuyup istemci tarafında (C# vb.) karşılaştıralım;

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
try {
    List Urunler = new List();
    List Satislar = new List();
    using (var connection = new SqlConnection("................")) {
        connection.Open();
        using (var cmd = new SqlCommand("SELECT [t0].[ID] FROM [ÜrünHizmet] AS [t0]", connection)) {
            using (var reader = cmd.ExecuteReader())
                if (reader.HasRows)
                    while (reader.Read()) {Urunler.Add(reader.GetInt32(0));}
        }
        using (var cmd = new SqlCommand("SELECT [t0].[ÜrünHizmet] FROM [SatışFaturasıDetay] AS [t0]", connection)) {
            using (var reader = cmd.ExecuteReader())
                if (reader.HasRows)
                    while (reader.Read()) {Satislar.Add(reader.GetInt32(0));}
        }
    }
    List toList = Urunler.Where(x => !Satislar.Contains(x)).ToList();
} finally {
    stopwatch.Stop();
}
Console.WriteLine("Süre: {0}", stopwatch.Elapsed);
// Süre: 00:00:00.4952774

İkinci örnekte ise tek bir sorgu ile veriyi sunucuda filtreleyerek alalım;

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
try {
    List<int> Urunler = new List<int>();
    using (var connection = new SqlConnection("..............")) {
        connection.Open();
        using (var cmd = new SqlCommand("SELECT [t0].[ID] \n"
                                       + "FROM   [ÜrünHizmet] AS [t0] \n"
                                       + "WHERE  EXISTS \n"
                                       + "         (SELECT NULL \n"
                                       + "          FROM   [SatışFaturasıDetay] AS [t1] \n"
                                       + "          WHERE  NOT ([t1].[ÜrünHizmet] = [t0].[ID]))", connection))
        {
            using (var reader = cmd.ExecuteReader())
                if (reader.HasRows)
                    while (reader.Read()) {Urunler.Add(reader.GetInt32(0));}
        }
    }
    List<int> toList = Urunler.ToList();
} finally {
    stopwatch.Stop();
}
Console.WriteLine("Süre: {0}", stopwatch.Elapsed);
// Süre: 00:00:00.0449053

Sonuçlar sunucu tarafında filtrelenen ve tek bir küme halinde alınan verinin çok daha performanslı çalıştığını göstermektedir.

 

Sunucu ile olan iletişiminizi döngü dışına taşıyın

Sunucuya giden her bir isteğin işlenmesi ve nihayetinde istemciye geri gönderilmesi esnasında kayıp zamanlar oluşur. Bu kayıpları minimumda tutmak için bir önceki konuda bahsedildiği üzere sunucu ile istemci arasındaki trafiği minimumda tutmak gerekir. Bu sebepten döngü içerisinde sorgu gönderme, veri ekleme, güncelleme ve silme işlemlerinden uzak durun.

“İyide zaten döngüyü bunun için çalıştırıyorum, döngü içerisinde veriye ulaşmadan nasıl logic çalıştıracağım? “ diye soruyorsanız; aşağıdaki önerileri dikkatli bir şekilde inceleyin!

Select komutu için öneri

Bin adet nesnemiz olduğunu ve her bir nesnenin Test adındaki bir tabloda var olup olmadığını kontrol etmemiz gerektiğini düşünün.

Birinci ve YANLIŞ olan yöntem aşağıdaki örnekte olduğu gibi döngü içerisinde nesne sayısı kadar sorgu göndermektir;

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
try {
    using (var connection = new SqlConnection("................")) {
        connection.Open();
        for (int i = 0; i < 1000; i++) { // YANLIŞ!!
            using (var cmd = new SqlCommand(string.Format("SELECT Test FROM Test where Test = {0}", i), connection)) {
                var değer = cmd.ExecuteScalar();
                if (değer != null) {
                    Console.WriteLine(değer);
                }
            }
        }
    }
} finally {
    stopwatch.Stop();
}
Console.WriteLine("Süre: {0}", stopwatch.Elapsed);
// Süre: Süre: 00:00:00.8364040

İkinci ve DOĞRU olan yöntem ise sorguda kullanacağımız parametreleri bir değişkende toplamak ve akabinde tek bir sorgu ile gerekli bilgileri sunucudan almaktır;

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
try {
    List<int> değerler = new List<int>();
    using (var connection = new SqlConnection("................")) {
        connection.Open();
        for (int i = 0; i < 1000; i++) {değerler.Add(i);} // DOĞRU
        using (var cmd = new SqlCommand(string.Format("SELECT Test FROM Test where Test IN ({0})", string.Join(",", değerler)), connection))
            using (var reader = cmd.ExecuteReader())
                if (reader.HasRows)
                    while (reader.Read()) {
                        //....
                    }
        }
    }
} finally {
    stopwatch.Stop();
}
Console.WriteLine("Süre: {0}", stopwatch.Elapsed);
// Süre: 00:00:00.0904701

IN operatörü kullanılarak tek bir sorguda alınan verinin ilk örneğe göre çok daha performanslı çalıştığı görülmektedir.

Insert komutu için öneri

Bu defa var olan bin adet nesneyi Test adındaki tabloya ekleyelim.

Birinci ve YANLIŞ olan yöntem döngü içerisinde nesne sayısı kadar INSERT komutu çalıştırmaktır;

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
try {
    List<int> değerler = new List<int>();
    using (var connection = new SqlConnection("................")) {
        connection.Open();
        for (int i = 0; i < 1000; i++) {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Test (Test) VALUES(" + i + ")", connection)) {
                cmd.ExecuteNonQuery();
            }
        }
    }
} finally {
    stopwatch.Stop();
}
Console.WriteLine("Süre: {0}", stopwatch.Elapsed);
// Süre: 00:00:00.2238716

İkinci ve DOĞRU olan yöntem ise gerekli parametreleri bir değişkende toplayarak tek bir insert komutu göndermektir.

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
try {
    List<string> değerler = new List<string>();
    using (var connection = new SqlConnection("................")) {
        connection.Open();
        for (int i = 0; i < 1000; i++) {
            değerler.Add(string.Format("({0})", i));
        }
        using (SqlCommand cmd = new SqlCommand(string.Format("INSERT INTO Test (Test) VALUES {0}", string.Join(",", değerler)), connection)) {
            cmd.ExecuteNonQuery();
        }
    }
} finally {
    stopwatch.Stop();
}
Console.WriteLine("Süre: {0}", stopwatch.Elapsed);
// Süre: 00:00:00.0478979

Hız farkı ortadadır.

Arada bazı yapısal farklılıklar olsa da aynı yöntemler UPDATE ve DELETE komutları içinde kullanılabilir.

NOT: MSSQL Server’da IN operatörü maksimum 1000 adet parametre kabul eder. Eğer göndereceğiniz parametre sayısı 1000 den fazla ise sorguları bu limite uyarak parça parça gönderip sonuçları istemci tarafında birleştirebilirsiniz.

 

Count() yerine Exist() kullanın

Veri tabanında bir kaydın bulunup bulunmadığını kontrol etmek isterseniz, COUNT () yerine EXISTS () kullanın. COUNT () tüm tabloyu tararken, EXISTS () koşulunuzla eşleşen satırları sayarak ihtiyaç duyduğu sonucu görür görmez çıkacaktır.

-- Kaçının
IF (SELECT COUNT(1) FROM STOKKARTI WHERE STOKKODU LIKE '%İPL%') > 0
 	PRINT 'BULUNDU' 

-- Kullanın
IF EXISTS(SELECT STOKKODU FROM STOKKARTLARI WHERE STOKKODU LIKE '%İPL%')
        PRINT 'BULUNDU'

 

Indeksleri efektif kullanın

Temel terimlerle, indeksler talep edilen kayıtlara hızlı şekilde erişilmesini sağlamak amacıyla kullanılan bir veri yapısıdır. İndeksler aynı zamanda başka sütunların aynı değerleri taşımayacağını garanti edecek birincil anahtar veya benzersiz bir dizin tanımlamak için de kullanılır. indeksleme bu kısa açıklamayla anlatılamayacak kadar kapsamlı bir konudur. Eğer SQL dünyasında yeni adım attıysanız performans üzerindeki etkisini öğrenmenizi ve indeksler hakkında daha fazla bilgi sahibi olmanızı tavsiye ederim.

  • Sık sık arama işlemlerinizin olduğu tüm tablolarda indeksleri dikkatlice oluşturun.
  • Daha az sayıda arama işlemi, daha fazla sayıda ekleme ve güncelleme işlemi yaptığınız tablolarda indekslerden kaçının.
  • Tam tablo taraması, sorgunun WHERE kısmındaki sütunların kendileriyle ilişkili bir indeks olmadığında gerçekleşir. Bir SQL sorgusunun WHERE kısmında koşullar olarak kullanılan sütunlarda bir indeks oluşturarak tam tablo taramasını önleyebilirsiniz.
  • İndeksleri drop ederek toplu veri yüklemelerini optimize edebilirsiniz. Milyonlarca satır olan bir Log tablosunu hayal edin, bu tabloda bir veya daha fazla indeks olması muhtemel. Toplu veri yüklemesi (INSERT vb.) yapmadan önce indeksleri drop ederek yükleme işlemini hızlandırabilir, işlem bitince tekrar oluşturabilirsiniz.

 

Indekslerdeki parçalanmalara dikkat edin

Bir veritabanı INSERT, UPDATE veya DELETE komutları ile sık sık güncellendiğinde, zamanla parçalanma oluşabilir. Veritabanı indeksleri parçalanırsa, SQL sorgu iyileştiricisi bir sorguyu çözümlemek için indeks kullanırken uygun olmayan bir yürütme planı seçebilir. Bu genel sorgu performansını etkiler ve bir sorgunun normalden daha yavaş çalışmasıyla sonuçlanır.

Aşağıdaki script çalıştırıldığı veri tabanındaki parçalanmış indeksleri raporlar. Bu scripti kullanarak hangi indekslerde parçalanma olduğunu öğrenebilirsiniz. Parçalanan indekslerin tekrar organize edilmesi için bir bakım planı oluşturabilir veya bu linkteki yöntemi deneyebilirsiniz.

SELECT
  dbschemas.[name] AS 'Şema'
 ,dbtables.[name] AS 'Tablo'
 ,dbindexes.[name] AS 'İndex'
 ,indexstats.alloc_unit_type_desc AS 'Ayırma Birimi Türü'
 ,indexstats.avg_fragmentation_in_percent AS 'Ortalama Parçalanma'
 ,indexstats.page_count AS 'Sayfa Sayısı'
FROM
  sys.dm_db_index_physical_stats(
    DB_ID()
   ,NULL
   ,NULL
   ,NULL
   ,NULL) AS indexstats
  INNER JOIN sys.tables dbtables
    ON dbtables.[object_id] = indexstats.[object_id]
  INNER JOIN sys.schemas dbschemas
    ON dbtables.[schema_id] = dbschemas.[schema_id]
  INNER JOIN sys.indexes AS dbindexes
    ON dbindexes.[object_id] = indexstats.[object_id] AND
       indexstats.index_id = dbindexes.index_id
WHERE
  indexstats.database_id = DB_ID() and indexstats.avg_fragmentation_in_percent > 0
ORDER BY
  indexstats.avg_fragmentation_in_percent DESC

 

Diğer öneriler

  • Uygun Veri Türü’nü seçin. Örneğin. string depolamak için Text veri türünün yerine varchar kullanın. Büyük verileri (8000’den fazla karakter) depolamanız gerektiğinde Text veri türünü kullanın.
  • Mümkünse nchar ve nvarcharlardan kaçının, çünkü her iki veri türü de aslında char ve varchar dır ancak iki kat fazla bellek alır.
  • Sabit uzunluklu alanda NULL kullanmaktan kaçının. NULL gereksinimi olması durumunda, NULL için daha az değişken uzunluklu (varchar) alanı kullanın.
  • Having kullanmaktan kaçının. Bir toplama işleminin sonucunu filtrelemek istiyorsanız kullanın.
  • Kümelenmiş (Clustered) ve Kümelenmemiş (Non-Clustered) indeksler oluşturun.
  • Kümelenmiş (Clustered) indeks kullanılan alanlar kümelenmemiş (Non-Clustered) dizinde de kullanılabilir olduğundan kümelenmiş dizini küçük tutun.
  • Kullanılmayan İndeksleri kaldırın.
  • Karakter yerine tamsayı değerleri olan sütunlar üzerinde indeksler oluşturmak daha iyidir. Tamsayı değerleri, karakter değerlerinden daha az ek yük kullanır.
  • Alt sorgular yerine Join kullanın.
  • Join içeren sorgularda tablolarının boyutunu sınırlamak için WHERE ifadelerini kullanın.
  • Bir tabloya ekleme yaparken TABLOCKX ve birleştirirken TABLOCK kullanın.
  • Herhangi bir tablodaki verileri sorgularken WITH (NOLOCK) kullanın.
  • Kilitlenme durumundan kaçınmak için SET NOCOUNT ON ve TRY CATCH kullanın.
  • Cursor çok yavaş olduğu için kaçının.
  • Temp tablosu yerine Tablo değişkenini kullanın. Temp tablolarının kullanılması TempDb veritabanı ile etkileşime geçer.
  • Mümkünse UNION yerine UNION ALL kullanın.
  • Sık kullanılan veriler ve daha karmaşık sorgular için Stored Procedure kullanın.
  • Transaction tablolarda kilitlenmelere neden olabileceğinden transactionları mümkün olduğunca küçük tutun.

 

Sonuç: Bu makalede önerilen teknikleri kullanarak uygulamanızda önemli performans iyileştirmeleri yapabilirsiniz.

Okuduğunuz için teşekkürler, hoşça kalın.

 

Yazar: Volkan Alkılıç

volkanalkilic@volsoft.com.tr

[/sociallocker]