Veritabanı sorguları yazılırken Türkiye’de bir çok yazılımcı maalesef sorgunun çalışma performansını uzun vadede düşünmüyor ve ilk aklına gelen çözümü üretiyor. Bu çözüm çoğunlukla en hızlı ve en düşük performanslı çözümdür. Geliştirdiği kod ilk başlarda çok hızlı çalışsa da veritabanındaki kayıt sayısı arttıkça sorguların çalışma performansı düşecektir. Bu durumu öngörüp ilk aşamada hızlı kod yazmak yerine hızlı çalışan sorguyu biraz daha uzun sürede yazmak uzun vadede fayda getirecektir.
Sorguların performansını etkileyen bir çok faktör vardır: Veritabanına gelen bağlantı sayısı, tablolar arasındaki ilişkilerin doğru kurgulanması, veritabanı normalizasyonu (en normalize edilmiş bir veritabanı bile çözüm olmayabilir; örneğin 3. normal formdaki bir veritabanını sorguların performansını artırmak için denormalize etmek zorunda da kalabilirsiniz), veritabanı sunucusunun gücü vs.
Yukarıda listelediğim faktörlerin yanı sıra veritabanından alınan toplu verilerde, tablo boyutları büyüdükçe sorgular yavaşlayabilmektedir. Örneğin müşterimize bir alışveriş sitesi yaptığımızı düşünelim. Sitede tüm ürünlerin listelendiği bir sayfa yapmanız istendi. İlk bakışta yeni başlayan bir yer olduğu için “en fazla 1000 ürün eklenir, bu da performansı etkilemez” diye düşünülüp ilk akla gelen yöntem olan Grid (tablo) üzerinde verileri 15’erli gruplar halinde listelemeyi düşünebilirsiniz. Hatta projeniz bir Asp.Net projesi ise şunu düşünebilirsiniz: “Her sayfa geçişinde niye bir daha veritabanına gideyim ki, zaten en fazla 1000 ürün eklenir. Tüm ürünleri veritabanından tek seferde sorgulayıp ViewState’te ya da Session’da tutayım. Hem sürekli veritabanına gidilip sayfa yavaşlamaz, çok hızlı çalışır. Sadece ilk başta belki 1-2 saniye bekler”. Bu çözüm 200 kayıt için belki kurtarıcı olabilir fakat 1000 kayıt olduğunda ve sisteme eş zamanlı yüzlerce kullanıcı giriş yaptığında performans kaybı yaşanacaktır. Performans kaybının yaşanacağı ikinci durum ise müşterinin işleri büyütüp 100.000 ürün girişi yaptığını düşündüğümüzde (ki HepsiBurada’nın sadece “Kitap” kategorisinde şu an 115.815 ürün var) bu sayfa çalışmaz hale gelecektir.
Peki bu durumda sayfanın veya sitenin performansını artırmak için neler yapılabilir?
Birden fazla çözüm üretilebilir. Benim aklıma gelen çözümleri aşağıda listeliyorum. Sizin aklınıza gelen çözümler varsa yorum olarak ekleyebilirsiniz :)
- Tüm ürünleri listelemekten vazgeçebilir, kategori bazında ürünleri listeleyebilirsiniz. Hatta daha güzeli, kategorilerin altında varsa alt kategoriler bazında da ürünleri listeleyebilirsiniz (Kitap > Eğitim > Bilgisayar > Programlama ... ). Örneğin kitap kategorisindeki tüm kitapları listeleyebileceği gibi Kitap kategorisinin altındaki Eğitim, onun altındaki Bilgisayar, onun altındaki Programlama kategorilerindeki kitapları da listeleyebilsin. Bu örnekte kitap bizim ana kategorimizdir. Kitap kategorisi gibi bir çok kategori de olabilir (Telefon, Bilgisayar, Beyaz Eşya ... ). Bu durumda tüm ürünleri listelemek yerine sadece bu ana kategorilerdeki ürünlerin kategori bazında listelenmesine izin verilmesi performansı bir miktar artıracaktır.
- İlk maddede sorgu performansını artırmak için bir çalışma yapmadık, sadece sorgu sonucunda gelecek olan kayıt sayısını azaltarak sitenin biraz daha hızlı çalışmasını (20 ana kategorimiz ve her ana kategoride 100.000 ürünümüz olduğunu düşünürsek, tüm ürünleri listelemek istediğimizde 2.000.000 ürün sorgulayacağımıza ana kategori bazında 100.000’er ürünü farklı sayfalarda sorgulayarak) sağladık. Performansı en iyi artıracak yöntem, ürünlerin listelenme sorgusundaki değişim olacaktır. Kullanıcıya ürün listesindeki her sayfada (paging) 20 ürün gösterdiğimizi düşünelim. İlk sayfada 1-50. sıradaki ürünleri, 2. sayfaya geçtiğinde 51-100. sıradaki ürünleri, 3. sayfaya geçtiğinde 101-150. sıradaki ürünleri ... görecektir. Tüm kayıtları ilk başta çektiğimizde 100.000 kaydı alıp bir yerde tutuyoruz ve 50’şer 50’şer kullanıcıya gösterdiğimizi (sorgumuzu bu şekilde yazdığımızı) düşünürsek, bizim hesabımıza göre kullanıcı tüm sayfaları ya da sayfaların çoğunu (yani 50.000 + tane kaydı) geziyor olması lazım ki yapılan iş gereçkten verimli olsun. Fakat gerçek hayatta kullanıcıların bir çok 3. sayfadan öteye çok geçmiyor. Yani 150 kayıt için 100.000 kaydı çekip performansı düşürmüş oluyoruz. Bu yöntemin yerine şunu yapabiliriz: Liste üzerinde 1. sayfadayken sadece ilk 50 (1-50. sıradaki ürünler), 2. sayfaya geçtiğinde sadece ikinci 50 (51-100. sıradaki ürünler), 3. sayfaya geçtiğinde sadece üçüncü 50 (101-150. sıradaki ürünler) ... listelensin. Böylelikle kullanıcı kaç sayfa gezdiyse o kadar ürün listelenmiş olacak ve performans artacaktır. “Bu yöntemle veritabanından çok sayıda sorgu çekilir, ilk yöntemle 1 kez gidiliyor, daha iyi değil mi?” diye düşünüyorsanız şunu söyleyim: Veritabanından 50 kayıt ile 100.000 alma işleminde hem veritabanı sorgulamasında hem de alınan kayıtların kod tarafında nesneye aktarılması (örneğin C#’ta DataTable’a aktarılması) sırasında zaman kaybı yaşanacaktır. Bunun yerine 50 kayıt almak hem sorgunun hızlı çalışmasını hem de kod tarafında nesneye aktarım süresinin azalmasını sağlayacak, böylelikle performansı gözle görülür bir şekilde artıracaktır.
Bu konuyla ilgili bir sorgu örneği yapalım. Örneğimizde 23 kayıtlık bir ürün tablomuz (URUN) olsun. Bu ürün listesindeki kayıtları 5’erli gruplar halinde, belirleyeceğimiz bir sayfa numarası değişkenine bağlı olarak sorgulayalım. Sayfa numarası 0 olduğunda 1-5. sıradaki 5 kayıt, 1 olduğunda 6-10. sıradaki 5 kayıt, 2 olduğunda 11-15. sıradaki 5 kayıt, 3 olduğunda 16-20. sıradaki 5 kayıt, 4 olduğunda 21-23. sıradaki 3 kayıt listelensin. Sorguyu öncelikle SQL Server için hazırlayacağım. Oracle ve diğer veritabanları için benzer mantığı kullanabilirsiniz.
Öncelikle değişkenlerimizi tanımlayalım ve ardından adım adım sorgumuzu yazalım.
- Her sayfada kaç (5) kaydın olacağını belirleyeceğim değişkeni tanımlayalım: @SayfadakiKayitSayisi
- Sayfa numarasını belirteceğimiz değişkeni tanımlayalım: @SayfaNo
Öncelikle veritabanındaki tüm ürünleri listeleyelim.
DECLARE @SayfadakiKayitSayisi INT = 5, @SayfaNo INT = 0 SELECT * FROM URUN AS U GO
Şimdi ürünleri listelediğimiz sorgu sonucuna satır numarasını ROW_NUMBER() fonksiyonuyla ekleyelim. Satır numarasını ürünün Id’si (UrunId) üzerinden alalım ve bu sütuna SiraNo diyelim (ürünün sıra numarası).
DECLARE @SayfadakiKayitSayisi INT = 5, @SayfaNo INT = 0 SELECT *, ROW_NUMBER() OVER (ORDER BY U.UrunId) AS SiraNo FROM URUN AS U GO
Kayıtlarımızı (kaç kaydı alacağımız) SiraNo sütununa göre belirleyeceğimiz için, oluşturduğumuz sorguyu bir SELECT sorgusu içine alalım ve SiraNo sütununu bu sorgu içinde kullanacak hale getirelim. Şu anki sorgu sonucunun ismine Urunler diyelim.
DECLARE @SayfadakiKayitSayisi INT = 5, @SayfaNo INT = 0 SELECT Urunler.SiraNo, Urunler.UrunId, Urunler.Ad, Urunler.Fiyat FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY U.UrunId) AS SiraNo FROM URUN AS U ) AS Urunler GO
NOT: Eğer sorgu içinde sıralama, filtreleme vb işlemleriniz varsa, URUN tablosunda yazdığınız sorgu sırasında yapmanız gerekecektir.
Artık hangi kayıtları alacağımızı Urunler içinden belirleyebileceğiz. Alınacak ürünlerin sıra numarası, (SayfaNo x SayfadakiKayitSayisi + 1)’den başlar, (SayfadakiKayitSayisi x (SayfaNo + 1))’de biter. Bu formülleri 2 sayfa üzerinde örnekleyelim:
- 0. (ilk) sayfada 1. ile 5. arasındaki 5 ürünün listelenmesini istiyoruz. Listelenecek ilk ürünün sıra numarası bulmak için, sayfanın numarası (0’dan başladığı için sayfa numarasını aldık, eğer sayfa numarası 1’den başlasın isteseydik her iki formülde de sayfa numarasına yapılan işlemleri 1 eksiltecektik) ile sayfadaki kayıt sayısını çarpıyoruz ve şimdiye kadar sorgulanmış kayıt sayısını buluyoruz: 0 x 5 = 0. Sayfadaki ilk kayıt, şimdiye kadar sorgulanan kayıtlardan bir sonraki kayıt olması gerekiyor: 0 + 1 = 1. Alınacak son sıra numarası ise sayfa numarasının bir fazlası (sayfa numarası 1 olsaydı kendisi olacaktı) ile sayfadaki kayıt sayısının çarpımı olacaktır: 5 x (0 + 1) = 5. Yani, 1. İle 5. kayıtlar arasını (5 dahil) listeliyoruz.
- 1. (ikinci) sayfada 6. ile 10. arasındaki 5 ürünün listelenmesini istiyoruz. Listelenecek ilk ürünün sıra numarasını bulmak için, sayfanın numarası ile sayfadaki kayıt sayısını çarpıyoruz ve şimdiye kadar sorgulanmış kayıt sayısını buluyoruz: 1 x 5 = 5 (ilk sayfada son alınan kaydın sıra numarası 5’ti). Sayfadaki ilk kayıt, şimdiye kadar sorgulanan kayıtlardan bir sonraki kayıt olması gerekiyor: 5 + 1 = 6 (sıra numarası 5 olan kaydı ilk sayfada gösterdiğimiz için bu sayfanın 6. kayıttan başlaması gerekiyor). Alınacak son sıra numarası ise sayfa numarasının bir fazlası ile sayfadaki kayıt sayısının çarpımı olacaktır: 5 x (1 + 1) = 10. Yani, 6. İle 10. kayıtlar arasını (10 dahil) listeliyoruz.
DECLARE @SayfadakiKayitSayisi INT = 5, @SayfaNo INT = 0 SELECT Urunler.SiraNo, Urunler.UrunId, Urunler.Ad, Urunler.Fiyat FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY U.UrunId) AS SiraNo FROM URUN AS U ) AS Urunler WHERE 1=1 AND Urunler.SiraNo BETWEEN (@SayfaNo * @SayfadakiKayitSayisi + 1) AND (@SayfadakiKayitSayisi * (@SayfaNo + 1)) GO
Şimdi sorgumuzu aşağıdaki parametrelerle çalıştırıp sonucunu inceleyelim:
@SayfadakiKayitSayisi: 5 | @SayfaNo: 0
@SayfadakiKayitSayisi: 5 | @SayfaNo: 1
@SayfadakiKayitSayisi: 5 | @SayfaNo: 2
@SayfadakiKayitSayisi: 5 | @SayfaNo: 4
@SayfadakiKayitSayisi: 5 | @SayfaNo: 5
NOT: Oracle veritabanı sorgusunu Pazartesi günü ekleyeceğim :)