Devexpress Dashboard ve SQL Server’da Büyük Veri ile Dashboard Performansı

Merhabalar

Hem iş zekası hem de veritabanı performansında yeni bir yazı ile daha sizlerle birlikteyim.

Bu yazımızda daha önce “İş Zekası Uygulaması arayanlar için pratik çözüm Devexpress Dashboard”

İsimli makalemizde İş Zekası uygulamalarına alternatif pratik bir çözümden bahsetmiştim.

Devexpress’i diğer iş zekası uygulamalarından ayıran en temel özelliği data modelleri oluşturmadan doğrudan SQL cümlesi yazarak çok pratik şekillerde interaktif dashboardlar oluşturmayı sağlaması idi.

Aslında bunu yaparken de çok pratik bir yöntem kullanıyor ve siz aksini belirtmedikçe tüm işi veritabanı sunucuya yaptırıyor. Ona bir işi yaptırmak için ise onun dilinden iyi anlaması gerekiyor tabi.

Ne demek istediğimi şöyle anlatayım.

Elimizde 10 milyon satırlık bir satış verisi var. Ama biz bölgelere göre satışları getirmek istiyoruz. Yani 7 satırlık bir veri döndürmek istiyoruz. Bunu yapmak için 10 milyon satırı client bilgisayara çekmek yerine

“SELECT REGION,SUM(LINENET) AS TOTALSALE FROM WH_SALES GROUP BY REGION” diyerek toplamda aşağıdaki gibi 7 satırlık veri getirmek mümkün.

Yani biz aşağıdaki gibi bir dashboard yapacak olsaydık. Yani c# ya da benzeri bir uygulamada kendi dashboard ekranımızı yazacak olsaydık, burada

Bölgelere göre satış için :” SELECT REGION,SUM(LINENET) AS TOTALSALE  FROM WH_SALES  GROUP BY REGION”

Şubelere göre satış için:” SELECT BRANCH,SUM(LINENET) AS TOTALSALE  FROM WH_SALES GROUP BY BRANCH”

Ürün kategorilerine göre satış için:”SELECT CATEGORY_NAME1,SUM(LINENET) AS TOTALSALE FROM WH_SALES GROUP BY CATEGORY_NAME1”

Aylara göre satış için ise :”SELECT CATEGORY_NAME1,SUM(LINENET) AS TOTALSALE FROM WH_SALES GROUP BY CATEGORY_NAME1”

Sql cümlelerini kullanır ve grid ya da chart ların içini bu şekilde doldururduk.

İşte Devexpress Dashboard tam olarak bu basit mantıkla çalışıyor ve bizim grid ya da chart’lara sürükleyip bıraktığımız alanlara göre otomatik SQL cümlelerini oluşturuyor ve datanın tamamını çekmek yerine işi client’tan daha kuvvetli olan server üzerinde gerçekleştiriyor. Tabi datanın tamamını client üzerine çekmek de bir seçenek. Eğer sizin veriniz 50.000 satırdan büyük değilse bu yöntem bazen daha performanslı da olabiliyor. Şimdilik bizim konumuz büyük veri ile ilgili olduğu için burada server mode da çalışma üzerine konuşacağız. Zaten sistem default olarak da server mode’un da çalışıyor.

Hadi gelin bu dediğimi ispat edelim.

Yeni bir dashboard oluşturalım.

Burada kullandığım WH_SALES tablosu 10 milyondan fazla satır içeren bir tablo bu arada. İlerleyen bölümlerde bu tablo üzerinde performans denemeleri yapacağız.

Dashboard’umuza datasource ekledik.

Şimdi bölgelere göre satış için bir grid ekliyoruz.

Bu gride REGION alanını sürüklüyoruz. Bu sürükleme işleminden hemen önce SQL Profiler uygulamasını açıyoruz. Bu sayede arka planda hangi sql cümlesinin gittiğini görebileceğiz.

Bunun için management studio içinde Tools>SQL Server Profiler menüsüne tıklıyoruz.

Açılan ekranda File>New Trace diyoruz.

Bağlanacağımız sunucuyu seçiyoruz.

Run dediğimiz zaman artık arka planda çalışan SQL cümlelerini izleyebilecek durumdayız. Burada ben kendi makinemde olduğu için çalışan sadece benim uygulamam olacak ve gereksiz sorguları görmeyeceğim.

Şimdi tekrar dashboard designer ekranına dönüyoruz ve REGION alanını gride sürüklüyoruz.

Şimdi de profiler ekranına bakalım. Bakalım arka planda hangi sql cümlesi çalışmış.

Gördüğümüz gibi

“select “TBL0″.”REGION” from  (SELECT * FROM WH_SALES) “TBL0” group by “TBL0″.”REGION” sorgusu gitmiş durumda. Aslında bu sorgunun pratikte

“SELECT REGION FROM WH_SALES GROUP BY REGION” sorgusundan bir farkı yok. Görüldüğü gibi oldukça basit bir mantık çalıştırıyor arka planda. Tabi biz bunu görmediğimizden gözümüzde büyütüyoruz. Ama görüldüğü gibi çok pratik. Zaten devexpress i bu yüzden seviyorum. Herhangi bir performans sıkıntısında çözüm üretmeniz çok kolay. Zira sizin gibi düşünüyor.

Şimdi “REGION” alanının yanına toplam ciroyu da görmek için “LINENET” alanını da ekleyelim ve profiler’dan hangi sorgunun gittiğine yine bakalım.

Evet şimdi de sorguya LINENET alanını ekledi ve getirdi.

select “TBL0″.”REGION”,sum(“TBL0″.”LINENET”) from  (SELECT * FROM WH_SALES) “TBL0”  group by “TBL0″.”REGION”

Burada duration alanı sorgunun gelme süresini gösteriyor. Sadece bölgelerin satış cirosu için geçen süre 5.7 sn. Buraya yukarıdaki başka bölümleri de getirdiğimizde bu raporun gelme süresi oldukça artacaktır. Şimdi diğer alanların da eklenmiş halini çalıştıralım ve profiler dan bakalım.

Profiler ekranından baktığımızda duration alanları görüldüğü gibi

5.690+5.843+5.830+5.665=23.028 ms=23 saniye sürüyor.

Aynı bilgiyi ekranın en üstündeki starttime ile en altındaki endtime alanlarının farkından da çıkarabiliriz. Sonuç itibariyle raporun çalışması 23 saniye sürüyor.

Bir rapor ekranı için bence uzun bir süre. Hele bir de bu rapor interaktif bir ekran olacak ise, yani ben raporda bölgelere tıkladığımda tüm rapor o bölgeye göre filtrelensin istiyorsam her seferinde bu kadar beklemem gerekir.

İşte bu noktada bu sorguları analiz ederek işi hızlandırmaya bakalım.

Şimdi bu sorguları sırası ile alıp Management Studio ya yapıştıracağız, sorgunun performansına bakacağız ve iyileştirmeye çalışacağız.

İlk sorgumuz ile başlayalım.

Sorgumuz 10 milyon satırdan 7 satır getirdi ve sonucun gelmesi 6 saniye sürdü.

Şimdi bakalım ne kadar okuma yapmış?

SET STATISTICS IO ON komutu ne kadarlık okuma yaptığı bilgisini gösteriyordu. Messages bölümünden de ona bakacak olursak;

379.711 tane page okuduğunu görüyoruz. Her bir page 8 KB ise

379.711*8/1024/1024=2.89 GB veri okuduğunu görebiliyoruz. Gerçekten hatırı sayılır bir okuma tek bir sorgu için.

Şimdi execution plana bakalım ve hangi indexi kullanarak çalıştığını görelim.

Bu arada tablomuzda primary key dışında indeximiz yok.

Display Estimated Execution Plan diyoruz ve execution plana bakıyoruz.

Kullanacak başka index olmadığı için haliyle primary key üzerinden arama yapmış.

Herhangi bir index önerisinde de bulunmuyor. SQL Server bazen bu index önerisi işinde çok başarılı iken bazen de önermiyor. Ama burada kendimiz bir index oluşturmayı deneyelim. Örneğin REGION alanına göre ve LINENET alanını da include edecek şekilde bir index oluşturalım.

Şimdi bakalım execution plana.

Bizim eklediğimiz indexi kullanmaya başladı.

Şimdi de sorgumuzu çalıştırarak sorgumuza bakalım.

Bu kez sorgu 2 saniyede geldi. Önceki süre 6 idi. Süremiz 3 kat kısaldı. Şimdi okunan page sayısına bakalım.

Okunan page sayısı da 41.165 önceki 379.711 idi. Bu da yaklaşık 10 katlık bir okuma iyileşmesi anlamına geliyor. Yani diskimizi 10 kat daha az yoruyoruz ve toplamda 2.89 GB yerine 0.3 GB veri okuyoruz.

Gelelim ikinci bölümde illere göre satış gridine.

O da aynı sayıda page okuyor ve 6 saniye sürüyor. 2.9 GB da bu dersek şu an iki grid için index olmaz ise 5.8 GB okuma söz konusu.

Şimdi buraya da index koyalım.

Burada da yine 6 saniyelik süre 2 saniyeye 379.686 adet okuma 48.348’e düştü.

Diğer iki ekran için de CATEGORY_NAME1 ve DATE_ alanlarına göre index ekliyoruz.

Şimdi dashboard’umuzu tekrar çalıştıralım ve profiler’dan bakalım.

Görüldüğü gibi sürelerin toplamı

2.376+2.477+2.151+2.171=9.175 ms=9 saniye’ye düştü.

Eskisi 23 saniye idi.

Böyle bir ekranı görmek için 9 saniye beklemek fena bir bekleme değil. Zira gerçek zamanlı olarak 10 milyon satırlık bir tablodan kayıt çekiyoruz.

Ama asıl sorun interaktif olarak çalışmak istediğimizde ortaya çıkıyor. Yani ben her bölgeye tıkladığımda rapor ona göre filtrelensin istersem her seferinde bu kadar beklemek güzel olmayacaktır.

Hadi gelin şimdi deneyelim.

Marmara bölgesine tıkladığımda raporda sadece Marmara Bölgesi’nin verilerinin gelmesini istiyorum.

Bunun için en baştaki grid’i seçip master filter olarak işaretliyorum.

Bu işlemi yaptıktan sonra Marmara Bölgesini seçiyorum ve raporumda her şey değişiyor.

Profiler’a tekrar bakıyoruz.

Raporu filtrelemek istememe rağmen süreler uzadı.

İşte bu hiç kullanışlı değil. Peki şimdi profiler’dan giden sorguya bakalım.

Birinci sorgu bu şekilde.

select “TBL0″.”BRANCH”,sum(“TBL0″.”LINENET”) from  (SELECT *  FROM [WHTIGER].[dbo].[WH_SALES]

) “TBL0”

where (“TBL0″.”REGION” = N’Marmara’)

group by “TBL0″.”BRANCH”

Burada görüldüğü gibi sistem otomatik olarak filtrelenecek bilgiyi içeren sorguyu sunucuya gönderiyor.

Şimdi bu sorguyu management studio’ya yapıştırıp display estimated execution plan dediğimizde

Sistem bu kez bize bir index önerisinde bulunuyor.

Bu önerinin scriptini almak için execution plan üzerinde missing index detail diyoruz.

Ve bize scripti çıkartıyor.

Indeximize IX5 ismini vererek çalıştırıyoruz ve sorgumuza bakıyoruz. Sorgumuz 2 sn de geldi bu kez.

Şimdi ikinci sorgumuza bakıyoruz.

select “TBL0”.”CATEGORY_NAME1″,sum(“TBL0″.”LINENET”) from  (SELECT *

  FROM [WHTIGER].[dbo].[WH_SALES]

) “TBL0”

where (“TBL0″.”REGION” = N’Marmara’)

group by “TBL0”.”CATEGORY_NAME1″

Onda da index önerdi.

Şimdi sorgumuzu tekrar çalıştırıyoruz.

Onda da iyileştirme söz konusu.

Şimdi sırada aylara göre satışları çektiğimiz gridimiz var.

Onun sorgusuna bakıyoruz. O da index öneriyor.

Onun da scriptini çalıştırıyoruz.

Gözle görülür bir iyileşme burada da var.

Şimdi asıl iyileşme olup olmadığını anlayacağımız yer dashboard’un kendisi. Yani kullanıcı ekranı. Ona bakalım. Profiler’ı temizleyip tekrardan Marmara bölgesini seçiyoruz.

Bölgeye tıkladığımızda geçen süreler

1.643+1.500+1.084=4.227 ms=4.2 saniye. Önceki 13 saniye idi hatırlarsanız.

Bu tarz bir işlemde 13 saniye beklemek can sıkar ama 4 saniye o kadar üzmez. Kullanıcı interaktif bir şekilde raporlarını çekebilir.

Özetle

Bu yazımızda 10 milyon satırlık bir perakende satış datasında gerçek zamanlı çalışan interaktif bir dashboard ekranını yapılacak ufak tefek küçük dokunuşlar ile nasıl performanslı çalıştırabileceğimizi görmüş olduk.

Umarım faydalı bir yazı olmuştur.

Sağlıcakla…