CTE Kullanarak Sorgularda Sayfalama Yapma(Paging)

Fazla sonuç döndüren sorgu sonuçlarında sayfalama ihtiyacı ortaya çıkar. Örneklendirmek adına düşünürsek arama motoruna ilgili kelimeleri arama için girdiğimizde çok fazla dönen sonuçtan ilk 20’si gösterilir. Örneğin “SQL Server” ile alakalı arama yapmak istediğimizde 22 milyon sonuç arasından ilk 20’sini getirir. Arama kırılımını genişletip  “SQL” olduğunda 2 milyar sonuç arasından yine ilk 20’sini getirir.

Veri sayfalama kavramı olmasaydı, 2 milyar sonucu size göstermeye çalıştığını varsayarsak sayfanın yüklenmesi biraz zaman alacaktır. Arama sonucunuz size gösterilen ilk 20 sonuç arasında değilse 2. ve 3. sayfaya geçiş yaparak daha fazla sonuçlar arasından işinize yarayana ulaşabilirsiniz. Bu kavram genel olarak sayfalama olarak bilinir.

Raporlama ve uygulama içerisinde listeleme yaparken bu kullanım size hız kazandıracaktır. Top 5000 veya Top 10000 ile çekilen sonuçların tamamı aynı anda görüntüleyip anlamlandırmak çok mantıklı olmayacağından ihtiyaca göre küçük sonuçlar her zaman uygulamanın belirli bir hız seviyesinde çalışmasına imkan verir.

AdventureWorks veri tabanı üzerinde örnek bir çalışma gerçekleştirelim.

SELECT [Name]
      ,[ProductNumber]
      ,[MakeFlag]
      ,[FinishedGoodsFlag]
      ,[Color]      
  FROM [Production].[Product]

Bu sorgu sonucunda 504 satırlık bir sonuç döndü. Ekranda bu veriyi sürekli göstermeye çalışmak mantıklı olmayabilir.

İşlem Adımları

1. Öncelikle orjinal sorgunuzu oluşturun.
2. Row Number fonksiyonunu Over ile birlikte kullanarak sorgunuza satır numarası ekleyin. Order by kullanıyorsanız Over ile birlikte kullanmanız gerekmektedir.
3. Adım 2’de yer alan sorguyu CTE olarak revize edin.
4. Sayfalama işlemi yapmak için veri kümesini parçalayarak sayfalandırın. Bunu where şartı ile gerçekleştireceksiniz.
5. Sayfalama işlemini gerçekleştirmek için where şartına sayfa değerlerini dinamik olarak gönderin.

1.Adım: Sorgumuzu Oluşturalım

SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Sales].[Customer]
  Order by CustomerID desc

2. Adım: ROW_NUMBER ve OVER Parametrelerini Ekleyelim

ROW_NUMBER fonksiyonu ile OVER kullanarak, bir satır numarasını görüntülemek için orijinal sorguya yeni bir sütun ekleyin. Orijinal sorguda bir ORDER BY varsa, ORDER BY öğesini OVER yanına taşıyın.

SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
      ,[ModifiedDate]
      ,ROW_NUMBER() OVER (ORDER BY CustomerID DESC) AS RowNum
  FROM [Sales].[Customer]

Bu noktada yaygın olarak yapılan hata, aşağıdaki kodda gösterildiği gibi WHERE şartına ROW_NUMBER() değerini koymaktır.

SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
      ,[ModifiedDate]
      ,ROW_NUMBER() OVER (ORDER BY CustomerID DESC) AS RowNum
  FROM [Sales].[Customer]
  WHERE ROW_NUMBER() OVER (ORDER BY CustomerID DESC) BETWEEN 10 and 20;

3. Adım: Sorguyu CTE Çevirelim

Sorgumuz hazır. Artık CTE olarak revize edelim.

 
  ;WITH SayfalamaOrnekCTE AS
(
  SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
      ,[ModifiedDate]
	  ,ROW_NUMBER() OVER (ORDER BY CustomerID DESC) AS RowNum
  FROM [Sales].[Customer]
)
SELECT *
  FROM SayfalamaOrnekCTE;

4. Adım: Filtrelememizi Ekleyelim

Aşağıdaki örneklendirmemde, sayfa boyutu 15 olarak düşünülmüş ilk sayfayı filtrelemek için WHERE RowNum 0 ve 15 BETWEEN; eklenmiştir.

 
  ;WITH SayfalamaOrnekCTE AS
(
  SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
      ,[ModifiedDate]
	  ,ROW_NUMBER() OVER (ORDER BY CustomerID DESC) AS RowNum
  FROM [Sales].[Customer]
)
SELECT *
  FROM SayfalamaOrnekCTE
   WHERE RowNum BETWEEN 0 AND 15;

5. Adım: Dinamik Hale Getirelim

Sabit değerleri kaldıralım ve sayfa numarasını ve sayfada kullanacağımız veri adeti için bunları değişkenlerle değiştirelim.

DECLARE @pageIndex AS INT;
DECLARE @pageSize AS INT;
SET @pageIndex = 2;
SET @pageSize = 15;

  ;WITH SayfalamaOrnekCTE AS
(
  SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
      ,[ModifiedDate]
	  ,ROW_NUMBER() OVER (ORDER BY CustomerID DESC) AS RowNum
  FROM [Sales].[Customer]
)
SELECT *
  FROM SayfalamaOrnekCTE
   WHERE RowNum BETWEEN  (@pageIndex -1) * @pageSize + 1
                  AND @pageIndex * @pageSize;

Bu noktada 3. sayfaya ulaşmak için tek yapmamız gereken, önceki kod örneğinden @pageIndex değişken değerini değiştirmektir.

SET @pageIndex = 3;

Büyük bir sonuç kümesi döndüren bir sorguyu, hızlı cevaplar dönen bir veri sayfalama sorgusuna dönüştürebildik.. Sorgularımız artık daha performanslı çalışacaktır. Bu sayede yazılım ya da raporları kullanan kullanıcılar daha yüksek hızda sonuçlara erişebileceklerdir.