Sql Sorgu Performans İpuçları

Raporlama verileri hazırlarken, uygulamaların içinde sürekli kullanılacak sorgular, stored procedureler yazarken yani günün büyük bir çoğunluğunda Sql sorgularıyla haşır neşir oluyorum. Bu nedenle sql sorgularını çalıştırdıktan sonra, sorgu performansını iyileştirmeye çalışırken baktığım kriterlerin bir listesini çıkarmak istedim. Bunlar, sorguyu canlıya almadan önce sırayla sorgu üzerinde kontrol ettiğim kriterler diyebilirim. Kontrol listem, tam anlamıyla kapsamlı ölçümler olmamakla birlikte, gözlemlerime, deneyimlerime ve araştırmalarıma dayanan çoğu zaman bu basit adımları izleyerek performans iyileştirmeleri sağladığım maddelerdir. Liste aşağıda olup aklıma geldikçe yeni maddeleri ekleyerek genişletmeye devam edeceğim 🙂 .

1.INDENTATION

Herşeyden önce okunabilir kod okunabilir sorgu… Sorgu yazarken o sorgunun sonradan başkası tarafından okunacağını bilerek hatta tekrar kendiniz de dönüp baktığınızda daha hızlı anlayıp sorun çözebilmek için queryi okunabilir yazmak ve indentationlara özen göstermek gerekir.

Aşağıda aynı sorgunun iki farklı şekilde yazılmış hali mevcut. Heralde bu örnek ne demek istediğimi yeterince anlatıyordur 🙂 .

select Order.OrderNo,Product.Description,Material.Description from Order join Product on Order.ProductId = Product.ProductId join ProductMaterialRelation on ProductMaterialRelation.ProductId = Product.ProductId join Material on Material.MaterialId = ProductMaterialRelation.MaterialId where Order.OrderDate>'28.09.2018' and Order.OrderDate < '28.11.2018' and Order.OrderStatus = 5 and Product.IsActive=1 and Material.IsActive=1

select o.OrderNo,
       p.Description,
       m.Description
                      from Order o
                      join Product p on o.ProductId = p.ProductId 
                      join ProductMaterialRelation pmr on pmr .ProductId = p.ProductId 
                      join Material m on m.MaterialId = pmr .MaterialId 
               where o.OrderStatus = 5 and
                     p.IsActive=1 and 
                     m.IsActive=1 and
                     o.OrderDate>'28.09.2018' and o.OrderDate < '28.11.2018'

2.ALIAS KULLANIMI

AS ifadesi ile uzun ve kullanımı zor olan tablo veya alan adlarına geçici olarak kısa isimler vererek bunları querylerimizde kullanabiliriz. Joinli sorgular yazarken hangi field’ların hangi tablodan geldiğini anlamak, fazlalıklardan kurtulmak ve daha okunur bir sorgu elde etmek için alias kullanımına özen göstermeliyiz.

select o.OrderName,
       o.OrderDate,
       p.ProductName,
       p.ProductCode
                    from Product p
                    join Order o on o.FkProductId = p.PkProductId

3.INDEXLEME

Sql’de sorgu performansını artırmak için yapılması gereken öncelikli hareket, tabloları indexlemektir. Indexleme ile sorgu maliyetini minimuma indirebiliriz.

Indexleme kavramını anlayabilmek için öncelikle Sql sorgularının temel olarak nasıl çalıştığını anlamamız gerekir.

İçerisinde 10000000 data bulunan bir tablo üzerinde “Select * from Person where Name =’Ali’ ”  gibi bir sorgu çalıştırdığımızı varsayalım. Hiç bir indexin bulunmadığı durumda veritabanı ilk kayıttan başlayarak tüm kayıtların Name kolonlarına sırayla bakar ve Ali ismini arar. Hatta Ali isminde bir kayıt ile karşılaşsa bile diğer kayıtlarda da olması ihtimaline karşı tabloyu gezmeye devam eder(Full table scan).

İndexlemede ise tablonun her satırı üzerinde uniqe bir alan tanımlanır ve sorgu yüzbinlerce veri üzerinden bile direkt hedefe yönelik bir şekilde O(1) karmaşıklığıyla veriye ulaşır. Bu sebeple üzerinden sıklıkla arama ve joinleme yapacağımız kolonları indexleyebiliriz.

Sql üzerinde indexlemeden bahsettiğimiz zaman karşımıza Clustured index ve Unclustred index olmak üzere 2 farklı indexleme kavramı çıkar. Bu kavramları burada detaylıca anlatmayıp diğer yazıma yönledireyim 🙂 .

Clustered Index ve Non-Clustered Index Kavramı

Indexlemeden ve öneminden kısaca bahsetmişken bir zamanlar yazılım geliştirici olarak girdiğim bir mülakatta indexlemeyle ilgili sorulan ve hoşuma giden iki soruyu da aşağıya iliştirmek istiyorum 🙂 .

    Soru 1: 

İçerisinde çokça kayıt olan 7 -8 tane birbiriyle ilişkili tablomuz olduğunu varsayalım. Bu tablolardan biri esas veriyi barındıran ana tablo, diğerlerini de bu ana tabloyla ve birbirleriyle ilişkili bilgileri taşıyan yan tablolar gibi düşünebilirsin. Kullanırken bu tabloları sürekli zincir şeklinde birbiriyle joinleyerek ilerlemen gerekecek. Bu tabloların performansı için index oluşturacak olsan ilk hangi tablolardan başlarsın (ana tablo, yan tablolar vs) ve nasıl bir yol izlersin?

     Soru 2:

     Elinde 1000000 tane veri var. Aradığın veri de aksi gibi 999999. sırada yer alıyor. Sen de verinin 999999. sırada yer aldığını bilmiyorsun. Bu verileri nasıl bir yapıda tutup içerisinde performanslı bir arama yapabilirsin.

4.VERİLERİ KISITLAMAK ve SADECE İHTİYAÇ DUYULAN VERİLERİ DÖNMEK

Sql üzerinden sorguladığımız tüm datalar bir kaynaktan taşınır ve bize sunulur. Basit bir mantıkla boyutu büyük data uzun sürede ve performanssız boyutu küçük data da daha kısa sürede karşımıza gelir diyebiliriz.

Bu mantıkla 15-20 alanlı bir tabloda çalışıyorsak, sadece 3 4 alan ile işimiz varsa  bu tablodan select çekerken ihtiyacımız olmayan alanları da ekrana getirmek ekstra bir performans kaybına yol açacaktır.

 select * from Person

yerine ihtiyacımız olan kolonlar dahilinde

   select Name, 
          Surname, 
          PhoneNumber from Person

şeklinde bir sorgu hazırlamamız performansı artıracaktır.

Bunun yanında üzerinde çalıştığımız verinin boyutunu da ihtiyaç doğrultusunda koşullar ve tarih aralıklarıyla sınırlandırabilirsek bu da sorgunun performansını oldukça artırıcaktır.

Konu buyken bir noktaya daha değinmek istiyorum 🙂 Pagination… uygulamamızda bir listeleme sayfamız olduğunu ve verileri paginationlu bir şekilde ekrana getirdiğini varsayalım. Gördüğüm çoğu uygulamada, listeleme sayfası verileri ekrana pagination şeklinde sunuyor olsa da aslında arka tarafta bütün verileri tek seferde çekiyor ve sayfanın performansını düşürüyor. Bu gibi durumlarda (her pagination sayfasında 20 kayıt getirdiğini varsayalım ) sayfa numarasına her tıklandığında veritabanından ilgili 20 kaydı çekmek hem sayfa hem de sorgu performansını artıracaktır.

Bunu da aşağıdaki sorgu yapısıyla rahatlıkla yapabiliriz 🙂 .

Sqlde OFFSET keywordu kaçıncı satırdan itibaren verilerin getirileceğini, FETCH NEXT keywordü ise kaç adet satır getirileceğini bize söyler.  Aşağıdaki sorgu için 19. satırdan başlayarak 10 adet kayıt getirilmesi istenmiştir.

SELECT  product_name,
        list_price 
                 FROM products
                  ORDER BY list_price OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

SELECT    product_name,
          list_price 
                FROM products
                ORDER BY list_price OFFSET i*20 ROWS FETCH NEXT 20 ROWS ONLY

5. OR KULLANIMI

Aşağıdaki sorguyu ele alalım. Or ‘un boolean ifadenin tüm kombinasyonları için kontrol yapılması gerektiğinden performans kaybına yol açabilir. Burada hem user_name’i hem de post_time’ı indexlemek kulağa mantıklı bir çözüm gibi gelebilir. Ancak çoğu durumda SQL bu indexleri kullanmayacaktır.

SELECT COUNT(*)
               FROM  fb_posts
               WHERE user_name = 'Mark' OR post_time > '2018-01-01'

SELECT …
     FROM …
     WHERE username = 'Mark'
  UNION
SELECT …
     FROM …
     WHERE post_time > '2018-01-01'

6.MINIMUM SAYIDA SUBSELECT KULLANIMI

Ne kadar gereksiz subselect okadar performans kaybı. Bazen bir tablonun query resultlarına birden fazla kez ihtiyacımız olabilir. Bu durumda tabloyu minimum selecte indirmek için aşağıdaki gibi bir yol seçebiliriz.

Aşağıdaki gibi bir sorgu yerine

SELECT name FROM employee WHERE salary = (SELECT MAX(salary) FROM employee_details) AND  
                                age = (SELECT MAX(age) FROM employee_details) AND
                                emp_dept = 'Electronics'

gibi bir sorgu yerine aşağıdaki gibi bir sorgu işimizi görecektir.

SELECT name FROM employee WHERE  dept = 'Electronics' and
                                (salary, age) = (SELECT MAX(salary), MAX(age) FROM employee_details)

7.SUBQUERY VS JOIN

Join ve subqueryler genel olarak farklı dataları tek bir sorguda kombine etmek için kullanılır.

Aslında sorgularda genel olarak subquery ve eşdeğeri olan join kullanımı arasında performans farkı yoktur. Ancak join ile eşdeğer subqueryi yazmak her zaman mümkün olmaz. Bu sebepe genel bir sonuca varmak istersek.

Aşağıdaki iki tip sorguyu karşılaştırdığımızda da join üstün gelecektir.

select Id, 
       Name,
       Description
                  from tblProducts 
                  where ID IN (select ProductId from tblProductSales)

select p.Id,
       p.Name,
       p.Description
                   from tblProducts p
                   join tblProductSales ps on p.ID = ps.ProductId

Existence check gibi bazı durumlarda da join subquerye göre üstünlük sağlar. Çünkü nested query dış querynin her bir sonucu için tekrar çalıştırılmak zorundadır. Aşağıdaki örnekte joinin subquery’e göre daha performanslı olduğunu söyleyebiliriz.

select Id,
       Name, 
       Descripton
                  from tblProducts
                  where Not Exists (select * from tblPRoductsSales where ProductId = tblProducts.Id)

select Id,
       Name,
       Descripion 
                 from tblProducts 
                 left join tblProductSales on tblProducts.Id = tblProductSales.ProductId 
               where tblProductSales.ProductId IS Null

Tabi insan gözüyle bakıldığında subquery logic olarak kulağa daha mantıklı gelir. Daha okunabilirdir. Bunun yanında çoğu durumda bizi veri çoklanması durumundan da kurtarır. Ancak bazı case’ler joini daha performanslı yapar :). Genel olarak subquery ile joinin hangi caselerde daha verimli olduğunu göz kararıyla kestiremiyorsak veri çoklanmaması için 1’e N tablolar dışında direkt olarak join kullanabiliriz.

8.GEREKSIZ JOINLER

Sql tarafında sorgu hazırlarken hiç bir etkisi olmayacak tabloları da gözden kaçırarak sorguda tutmamız olasıdır. Sql sorgusu doğru sonucu verdikten sonra çoğu developer tekrar dönüp sql sorgusundaki gereksiz parçaları temizleme eğiliminde değildir. Gereksiz joinleri kaldırarak veritabanını gereksiz işlem yükünden kurtarabilirsiniz.

9.ILISKILERI WHERE KOSULU YERINE JOIN ILE OLUSTURMAK

Bazı developerlar tablolar arasında ilişki kurarken join kullanmak yerine where üzerinden ilişki kurmayı tercih ederler.

SELECT Customers.CustomerID, 
       Customers.Name, 
       Sales.LastSaleDate 
                         FROM Customers, Sales 
                         WHERE Customers.CustomerID = Sales.CustomerID

Bu tip joinler kartezyen product veya cross join olarak da adlandırılır. İki tablonun tüm olası ilişkilerini bir araya getirir. Örneğin 1000 kayıtlı bir customer tablonuz var. 1000 kayıtlı da sales tablonuz var. Bu şekilde where üzeriden bir ilişki kurduğunuzda 1000000 kayıtlı bir sonuç çıkarır. Daha sonra customerIdsi koşuldaki gibi olan 1000 kaydı filteler. Bu database için performans kaybıdır. 100x daha fazla çalışmadır.

Kartezyen joini engellemek için aşağıdaki şekilde ilişki kurulabilir.

SELECT Customers.CustomerID, 
       Customers.Name, 
       Sales.LastSaleDate 
                        FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID

Bu sorguda database yalnızca CustomerIdleri birbirine eşit olan 1000 kayıt üretecektir.

Bazı DBMS sistemleri, where ile yapılan joinleri tanıyabilir ve bunları otomatik olarak arkada joine dönüştürebilir. Bu DBMS sistemlerinde, WHERE join ve INNER JOIN arasında performans açısından bir fark olmayacaktır. Ancak, INNER JOIN tüm DBMS sistemleri tarafından tanınır. Bu farkın performans kaybı ihtimalini ortadan kaldırmak ve herkesce alışkın olunan okunabilir sorgu yazabilmek açısından join kullanmakta fayda var.

10.DISTINCT KULLANIMI

Distinct kullanımı ile duplicate kayıtları çok kolay bir şekilde elimine edebiliyor olmamıza rağmen, bu özellik performansı gözle görülebilir derecede düşürür. Distinct ile çekilen sorguların performansını al aşağı eden disctinct ile gelen sorting maliyetidir.

Aşağıda örnek bir sorgunun distinct ile ve distinctsiz çalıştırıldığında ortaya çıkan performans sonucu mevcut.  Distinct ile çekilen kayıtta Execution plana SORT node’u ekleniyor. Bu sort node’u da toplam sorgu maliyetinin %75ine sahip.

Peki disctint kullanımından nasıl kaçınabiliriz?

• Şuana kadar gördüğüm distinct kullanımlarının %70 kadarı, database ve tablo yapısının doğru oluşturulmamasından kaynaklanıyordu. Distinct ile kategorilendirilmek istenen verinin bir katalog tablosu oluşturularak çoğu disticnt kullanımının önüne geçilebilinir.
• Disticnt çekmek yerine spesifik unique bir kolon distinct kolonlarına eklenerek her bir kolonu spesifik hale getirebilir.
• Eğer yine de disticnte ihtiyaç duyuyorsanız ve bunun sizin için SORT maliyeti fazla ise, indexli veriler üzerinden distinct çekebilirsiniz. İndexli veriler üzerinden distinct çekildiğinde Sql sort işlemini atlar.

11.IN YERINE EXIST KULLANIMI

In kullanımı aslında arka planda Or kullanımına eşdeğerdir.Databasede In ile birden fazla opsiyonun kombinasyonlarını bir bir check ederken aynı sorguyu EXIST ile aşağıdaki gibi yazarsak, uygun bir kayıt bulduğu an aramayı kesecektir.

Aşağıdaki gibi bir sorgu yerine

Select * from product p 
         where product_id IN (select product_id from order_items)

Aşağıdaki gibi bir sorgu tercih etmek yerinde olacaktır.

Select * from product p 
         where EXISTS (select * from order_items o where o.product_id = p.product_id)

12.COUNT YERINE EXISTS KULLANIMI

Çoğu developer, tabloda bir kaydın varlığını sorgulamak için EXISTS yerine COUNT kullanır. COUNT bu konuda verimsizdir. Çünkü EXISTS aranan bir kaydın karşılığını bulduğunda scan işlemini sonlandırırken, COUNT aradığı verinin karşılığını bulsa bile tabloyu sonuna kadar scan etmeye devam eder.

Aşağıda bir SP’nin parçası olarak değerlendirebileceğimiz iki sorgudan ikincide uygun kayıt bulunsa bile sorgu tüm tabloyu gerek olmadığı halde scan etmeye devam ederken ilk sorguda ise uygun kayıtla karşılaşıldığında sorgu sonlanır.

IF EXISTS(SELECT * FROM OrderDetails WHERE orderNo = 10248)
PRINT 'yes'
ELSE
PRINT 'no'
END IF

IF (SELECT COUNT(*) FROM OrderDetails WHERE orderNo = 10248) > 0
PRINT 'yes'
ELSE
PRINT 'no'
END IF

13.UNION KULLANIMI

Union ve Union all arasındaki fark, union duplicate valueları elimine ederek iki tabloyu birleştirirken union all iki tablodaki tüm kayıtları birleştirerek getirir. Yani Unionun içerisinde bir yukarıda anlattığım şekilde bir distinct maliyeti vardır ki bu da unionu union all’a göre daha yavaş hale getirir. Eğer ki duplicate valueları elimine etmek gibi bir kaygımız yoksa union all kullanmak sorgu hız ve performansını artıracaktır.

14.WILDCARD’IN STRING BAŞINDA KULLANIMINDAN KAÇINMAK

Aşağıdaki şekilde like kullanımından olabildiğince kaçınmamız gerekir.

SELECT * FROM Customers WHERE address LIKE '%bar%'

Bu şekilde bir sorguda database eğer varsa uygun indexi % yüzüden kullanamaz. Sistem full table scan yapmaya başlar ki bu da performansı düşürürür. Bunun yerine wildcardı (%) aşağıdaki gibi kullanmak uygun olacaktır.

SELECT * FROM Customers WHERE address LIKE 'bar%'

Konuyla ilgili hoşuma giden bir yazıyı da buraya sizin için bırakmak istiyorum.

Sargability: Why %string% Is Slow

15. EXECUTION PLAN OKUMAK

Yukarıda anlattığım herşey genel çıkarımlar olsa da her kullanımın kendine göre avantajlı olduğu farklı case’ler var. Bu caseleri gözlemleyebilmek ve sorgumuzun verimi üzerine düşünebilmek için yapmamız gereken en önemli şey Sql Execution planları gözlemleyip çıkarım yapabilmektir. Çünkü siz her nasıl sorgu yazıyor olursanız olun aslında esas mesele SQL’in sizin sorgunuzu arka planda nasıl bir execution plana çevirdiğidir. Eğer Sql farklı şekilde yazılmış aynı sonucu veren sorgularını arkada tarafta aynı execution plana çevirebiliyorsa bu sorgular aynı performansa sahiptir diyebiliriz.

Execution plan ile ilgili detaylara bu yazımda girmeyip ileride bunun için ayrı bir yazı yazmayı da planlıyorum 🙂 .