MSSQL Server: Verimli Sayfalama Yöntemleri

Bu yazıda SQL Sever ile büyük bir tablo üzerinde farklı yöntemler ile veri çekeceğiz. Çıkan sonuçlar arasında yöntemleri yorumlayarak en hızlı ve verimli yolu bulmaya çalışacağız.

Veri çekmek için kullanacağımız sayfalama yöntemleri aynı zamanda tuş takımı sayfalaması olarak da bilinir.

Tüm örnekleri MSSQL ile 50 milyon kaydı olan bir tablo üzerinde gözlemleyeceğiz.

Bu bölümde, örnek olarak aşağıdaki tablo yapısını kullanıyoruz.

CREATE TABLE [dbo].[MUSTERI](
[musteri_no] [int] IDENTITY(1,1) NOT NULL,
[ad] [nvarchar](20) NULL,
[soyad] [nvarchar](20) NULL,
[yas] [tinyint] NULL,
[cinsiyet] [char](1) NULL,
CONSTRAINT [PK_MUSTERI] PRIMARY KEY CLUSTERED
(
[musteri_no] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

Veri çekmek için bu tabloda toplam 50 000 000 örnek kayıt olan tabloyu kullanacağız ve bu kayıtlar üzerinden sonuçlar elde edeceğiz.

Veri Görüntüleme

#1.Yöntem

SELECT musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI

50 000 000 kayıt ile bu durumda gönderdiğimiz sorgu asla bitmeyecektir. Bu yüzden kayıtların tamamı tek seferde görüntülenemez. Sorgumuz verileri istemciye göstermeden önce tüm tabloyu RAM’e yüklediği için büyük verilere sahip tabloları tek seferde görüntülemek oldukça zordur.

Başka bir varsayım ile göndermeden önce verileri önceden yüklemek çok zaman aldı ve sorgu zaman aşımına uğradı. Böylece tüm kayıtları zamanında alma girişimimiz başarısız oldu. Başka bir çözüm bulmamız gerekiyor.

#2.Yöntem

Bu yöntemde OFSET ve FETCH deyimleri ile veri çekmeyi deneyeceğiz. OFFSET ve FETCH, dizi şeklinde kayıt almak için bir yol sağlamak üzere SELECT deyimi ORDER BY deyimi ile birlikte kullanılır. Dönecek başlangıç ​​satırı, OFFSET değeri ve bu noktadan döndürülecek maksimum satır sayısı FETCH tarafından belirlenir.

Söz dizimi aşağıdaki gibidir.

ORDER BY column_list [ASC |DESC]

OFFSET offset_row_count {ROW | ROWS}

FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY

OFFSET deyimi, sorgudan satır döndürmeye başlamadan önce atlanacak satır sayısını belirtir. offset_row_count , sıfıra eşit veya daha büyük olan bir sabit, değişken veya parametre olabilir.

FETCH deyimi, OFFSET deyimi işlendikten sonra döndürülecek satır sayısını belirtir. offset_row_count bire eşit veya daha büyük olan sabit, değişken veya skaler olabilir.

FETCH deyimi isteğe bağlı iken OFFSET deyimi zorunludur. Ayrıca, FIRST ve NEXT sırasıyla eşanlamlıdır, böylece bunları birbirlerinin yerine kullanabilirsiniz. Benzer şekilde, FIRST ve NEXT değişmeli olarak kullanabilirsiniz.

Aşağıdaki fotoğrafta OFFSET ve FETCH yan tümcelerinin tam olarak nasıl işlediğini göstermek adına;

SELECT musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI ORDER BY musteri_no OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY
şeklinde sorgulama yaptığımızda, yöntemin çalışma şekli aşağıdaki gibidir.

#1.Yöntem

OFFSET ve FETCH yan tümceleri ORDER BY yan tümcesi ile kullanmanız gerektiğini unutmayın. Aksi takdirde bir hata alırsınız.

OFFSET ve FETCH yan tümceleri, sorgu sayfalama çözümünü TOP yan tümcesinden daha fazla uygulamak için tercih edilir.

Bu yöntem ile 50 000 000 kayıtlık tablomuza bir sorgu gönderelim ve sonucu görelim.

SELECT musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI ORDER BY musteri_no OFFSET 0 ROWS FETCH NEXT 100000 ROWS ONLY

0. kayıttan başlayıp 100.000 kayıtlık bir sayfalama yaptığımızda verilerin gelmesi (931 ms = 0.931) saniye sürdü.

Peki 5 000 000. kayıt ile 5 100 000. kayıt arasındaki kayıtları almaya çalıştığımızda ne kadar sürecek?

SELECT musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI ORDER BY musteri_no OFFSET 5000000 ROWS FETCH NEXT 100000 ROWS ONLY

5.000.00. kayıttan başlayıp 100.000 kayıtlık bir sayfalama yaptığımızda verilerin gelmesi (1574 ms = 1.574) saniye sürdü.

#3.Yöntem

Bu yöntemde bize sayfalama imkanı sunan fonksiyonumuz ROW_NUMBER() isimli fonksiyondur. Bu fonksiyon her bir kaydın kaçıncı satırda olduğunu veren bir fonksiyondur. Örneğin her bir kaydın satır numarasını veren bir sorgu yazalım.

SELECT TOP 5 Row_Number() OVER (ORDER BY musteri_no) as rowNum, ad, soyad, yas, cinsiyet FROM MUSTERI
Not: Sorgunun başına top 5 ekleme sebebimiz 50.000.000 kayıtlık veriyi tamamen çekmeye çalışmasını önlemek.

Sorguyu çalıştırdığımızda aşağıdaki çıktıyı elde ederiz.

Bu ekran görüntüsünden aslında ROW_NUMBER() fonksiyonun ne iş yaptığını anlamışsınızdır. Bu fonksiyon OVER ifadesi ile beraber çalışır ve OVER ile sıralamanın esas alınacağı kolonu belirtmeniz gerekir.

Sayfalama için kullanacağımız sorgu yapısı şu şekildedir;

SELECT * FROM ( SELECT Row_Number() OVER (ORDER BY musteri_no) as rowNum, musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI
)x WHERE @PAGE_SIZE*(@PAGE_NUMBER-1)+1 AND @PAGE_SIZE*@PAGE_NUMBER+1;

Yukarıdaki sorgumuz @PAGE_SIZE ve @PAGE_NUMBER olmak üzere iki parametre almaktadır. Bu parametrelerden @PAGE_SIZE her bir sayfada listelenecek veri sayısını, @PAGE_NUMBER ise listelenmek istenen sayfa numarasını temsil etmektedir. Sorguda dikkat edildiği üzere @PAGE_SIZE+1 adet veri seçilmektedir. Bunun nedeni ise arayüzde bir sonraki sayfanın varlığını kontrol etmektir.

Bu yöntem ile 50 000 000 kayıtlık tablomuza bir sorgu gönderelim ve sonucu görelim.

SELECT * FROM ( SELECT Row_Number() OVER (ORDER BY musteri_no) as rowNum, musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI
) x WHERE rowNum between 0 AND 100000

0. kayıttan başlayıp 100.000 kayıtlık bir sayfalama yaptığımızda verilerin gelmesi (952 ms = 0.952) saniye sürdü.

Peki 5 000 000. kayıt ile 5 100 000. kayıt arasındaki kayıtları almaya çalıştığımızda ne kadar sürecek?

SELECT * FROM ( SELECT Row_Number() OVER (ORDER BY musteri_no) as rowNum, musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI
) x WHERE rowNum between 5000000 AND 5100000

5.000.00. kayıttan başlayıp 100.000 kayıtlık bir sayfalama yaptığımızda verilerin gelmesi (6848 ms = 6.848) saniye sürdü.

Bu yöntem 1.yönteme göre daha yavaş çalışıyor. CPU ve Reads değerlerine bakacak olursak sistemi bir hayli yorduğunu söyleyebiliriz.

#4.Yöntem

Bu yöntemde TOP deyimini kullanacağız. Burada dikkat edilmesi gereken bir gereklilik bulunmaktadır. TOP deyimini kullanarak sayafalama yapabilmek için sorgumuza koşul biçebileceğimiz bir alan olması gerekmektedir. Bu genel yapılarda çok sık kullanılan ID alanı olabilir. Bizim örneğimizde identity özelliğini kullandığımız musteri_no alanını kullanarak sayfalama yapacağız.

Sayfalama için kullanacağımız sorgu yapısı şu şekildedir;

SELECT TOP @PAGE_SIZE musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI
WHERE musteri_no > @LAST_VALUE

Bu sorguda @PAGE_SIZE ve LAST_VALUE parametrelerini kullanmaktayız.

@PAGE_SIZE parametresine göstermek istediğimiz kayıt sayısını yazıyoruz.
@LAST_VALUE parametresine ise elimizde olan son musteri_no alanının değerini bu parametreye veriyoruz.

Bu yöntem ile 50 000 000 kayıtlık tablomuza bir sorgu gönderelim ve sonucu görelim.

SELECT TOP 100000 musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI
WHERE musteri_no > 0

0 musteri_no değerine sahip kaydı baz alıp, bu değerden büyük 100.000 kayıtlık bir sayfalama yaptığımızda verilerin gelmesi (916 ms = 0.916) saniye sürdü.

Peki 5 000 000. kayıt ile 5 100 000. kayıt arasındaki kayıtları almaya çalıştığımızda ne kadar sürecek?

SELECT TOP 100000 musteri_no, ad, soyad, yas, cinsiyet FROM MUSTERI
WHERE musteri_no > 5000000

5 000 000 musteri_no değerine sahip kaydı baz alıp ,bu değerden büyük 100.000 kayıtlık bir sayfalama yaptığımızda verilerin gelmesi (920 ms = 0.92) saniye sürdü.

Bu yazımızda 4 ayrı yöntem ile 50 000 000 kayıta sahip olan tablodan verileri görüntüleyerek performans karşılaştırması yaptık.

Çıkan sonuçlara göre 4. yöntem olan TOP deyimi hem sistemi yorma açısından, hem de hız bakımından diğer yöntemlere göre daha verimli gözüküyor. Siz de kendi verileriniz ile bu yöntemleri karşılaştırarak sizin için en uygun sayfalama yönteminde karar verebilirsiniz.