Makale Özeti

SQL Serverda pek çok sayfalama yöntemi mevcuttur. Bu yazıda , SQL Server tarafında sorgulama yapıp, sadece ihtiyaç duyulan kayıtların gönderileceği şekilde sayfalama yöntemini inceleyeceğiz...

Makale

SQL Server ve Sayfalama

SQL Serverda pek çok sayfalama yöntemi mevcuttur. Fakat bu işlem için kod yazmak gereklidir. Aklıma gelen alternatifler;

  1. Sorguyu yapıp, tüm sonuç setini istemciye gönderip, istemci (client) tarafında, sayfalama yapmak;
  2. Sorguyu SQL Server tarafında yapıp, sonuç setini "temp" databaseine atıp, orada sayfalama yapmak
  3. SQL Server tarafında sorgulama yapıp, sadece ihtiyaç duyulan kayıtların gönderileceği şekilde sayfalama yapmak.
  4. ...

Bu yöntemlerin hepsinin kendine göre avantajları ve dezavantajları var. Yapılan sorgulamadaki optimizasyon, Cacheleme ihtiyacı, problemlere göre değişik yöntem seçilmesini gerektiriyor. Örneğin en çok kullanılan ve .Nette programlaması kolay olan, birinci yöntem, (bana göre - veriyi cachelemediğiniz sürece - ) Web tarafında pek kullanışlı değil ama Windows uygulamaları için kullanışlı. Çok fazla kayıt olması durumunda, bir "Cache" mekanizması implement edilmemişse, her requestte yeniden tüm sonuç setinin, SQL serverdan Web Servera taşınmasına sebep olur. İkinci yöntemde temp databaseine erişmeniz gerekir. Bu yazıda, ben 3. yöntemi biraz incelemek istiyorum.

Öyle bir sorgulama yapmalıyım ki; bir kaç parametre vererek, milyonlarca kayıt olan bir sorguda, sadece istediğim sayfadaki, kayıtlar geri dönsün ve SQL serverdan Web servera taşınsın. Bütün bu işlemleri de ölçeklenebilir ve hızlı bir şekilde yapsın. Bunun için, bir "stored procedure" yazdım. Her ne kadar kaçınılması tavsiye edilse de, yarattığım stored procedure, içinde dinamik bir SQL ifadesi oluşturup, çalıştırıyor.

SelectPage adını verdiğim stored procedureun kodunu aşağıda bulabilirsiniz.

Burada belirtmek isterim ki; belirli durumlarda, bu yazıda anlatılan uygulamadan daha hızlı ve ölçeklenebilir çözümler olabilir.

Parametreler

Stored procedure çalışmak için 9 adet parametreye ihtiyaç duyuyor (sadece ilk üç parametrenin doldurulması yeterlidir);

  • strFields: virgülle ayrılmış alan listesi,
  • strPK: Varsa tablonun "Primary Key"i (Bu değerin olması, sorgulamanın sonucunu dramatik bir şekilde artırır),
  • strTables: Tablo listesi (bu alana Joinler ve Hintler yazabilirsiniz.),
  • intPageNo: İstenen sayfa numarası (varsayılan : 1),
  • intPageSize: Sayfadaki kayıt sayısı (varsayılan : NULL - tüm kayıtları getirir) ,
  • blnGetRecordCount: Toplam kayıt sayısı da isteniyor mu? (varsayılan : 0 - hayır, eğer 1 ise ikinci bir RecordSette toplam kayıt sayısı döndürülür),
  • strFilter: Sorgunuzun WHERE kısmını buraya yazabilirsiniz (varsayılan : NULL - boş),
  • strSort: Sıralama değeri (ORDER BY) (varsayılan : NULL - boş),
  • strGroup: GROUP BY ifadesi (varsayılan : NULL - boş)

Örnekler, kullanım önerileri 

Northwind varitabanında çalışmaktadır. Aşağıdaki örnekleri, SQL Query Analyzeri çalışıtırıp "Northwind" databaseini seçerek çalıştırabilirsiniz. Örneklerdeki değerler, Northwindin ilk kurulumdaki verileri kullanılarak gösterilmiştir. (SelectPage prosedürünü çağırabilmek için, önce "Northwind" veritabanında, bu prosedürü yaratmalısınız.)

  • exec SelectPage *, EmployeeID, Employees
    Bu sorgu Emploeeys tablosundaki bütün kayıtları getirir. 
    "SELECT * FROM Employees" ifadesine eşittir.

  • exec SelectPage FirstName+ +LastName, EmployeeID, Employees, 2, 2, 1, TitleOfCourtesy<>Dr., LastName

    Bu sorgu; Employeelerin ad ve soyadını, Doktor Dr. olmayanları, Soyadına göre sıralayarak, 2. sayfadaki 2 kayıtı listeler, ayrı bir sonuç setinde de toplam kayıt sayısını döndürür.

    İlk kayıt setinde;
    Nancy Davolio
    Anne Dodsworth
    İkinci kayıt setinde ise, "8" değerini döndürür.

  • Üçüncü ve son örnekte, Northwind veritabanında, "Invoices" adlı bir View bulunmaktadır,
    SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
    	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
    	Customers.Region, Customers.PostalCode, Customers.Country, 
    	(FirstName +   + LastName) AS Salesperson, 
    	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
    	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, 
    	"Order Details".Discount, 
    	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
    FROM 	Shippers INNER JOIN 
    		(Products INNER JOIN 
    			(
    				(Employees INNER JOIN 
    					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
    				ON Employees.EmployeeID = Orders.EmployeeID) 
    			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 
    		ON Products.ProductID = "Order Details".ProductID) 
    	ON Shippers.ShipperID = Orders.ShipVia
    

Bu sorguyu çalıştırdığınızda 2155 kayıt geri gönderiyor. Böyle bir sorguda 10 kayıttan oluşan 3. sayfayı göstermek istediğimizde;

exec Selectpage Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
	Customers.Region, Customers.PostalCode, Customers.Country, 
	(FirstName +   + LastName) AS Salesperson, 
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, 
	"Order Details".Discount, 
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight,
Orders.OrderID,
Shippers(nolock) INNER JOIN 
		(Products(nolock) INNER JOIN 
			(
				(Employees(nolock) INNER JOIN 
					(Customers(nolock) INNER JOIN Orders(nolock) ON Customers.CustomerID = Orders.CustomerID) 
				ON Employees.EmployeeID = Orders.EmployeeID) 
			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 
		ON Products.ProductID = "Order Details".ProductID) 
	ON Shippers.ShipperID = Orders.ShipVia,
3, 10, 1

şeklinde bir sorgu yazmak yeterli olacaktır.

  • Gruplama özelliği için,

    exec SelectPage ProductName, Sum(ExtendedPrice) AS "Total", ProductName, Invoices, 2, 10, 0, null, null, ProductName

    şeklinde bir sorgu yazmak yeterli olacaktır.

  • Genelde ben bu prosedürü uygulamada direk kullanmıyorum, bu prosedürü çağıran prosedürler yazıyorum. Örneğin müşterileri sayfalamak istediğimde; "GetCustomersByPage" gibi bir prosedür yazıyorum;

    CREATE PROCEDURE GetCustomersByPage (@PageNo INT, @PageSize INT) AS 
    	EXEC SelectPage *, CustomerID, Customers (nolock), @PageNo, @PageSize, 1 
    RETURN

Stored Procedure

CREATE PROCEDURE SelectPage (
	@strFields VARCHAR(4000),
	@strPK VARCHAR(100),
	@strTables VARCHAR(4000),
	@intPageNo INT = 1,
	@intPageSize INT = NULL,
	@blnGetRecordCount BIT = 0,
	@strFilter VARCHAR(8000) = NULL,
	@strSort VARCHAR(8000) = NULL,
	@strGroup VARCHAR(8000) = NULL
)  AS 
  
	    DECLARE @blnBringAllRecords BIT
    DECLARE @strPageNo VARCHAR(50)
    DECLARE @strPageSize VARCHAR(50)
    DECLARE @strSkippedRows VARCHAR(50)

    DECLARE @strFilterCriteria VARCHAR(8000)
    DECLARE @strSimpleFilter VARCHAR(8000)
    DECLARE @strSortCriteria VARCHAR(8000)
    DECLARE @strGroupCriteria VARCHAR(8000)

    DECLARE @intRecordcount INT  
    DECLARE @intPagecount INT   

    SET NOCOUNT ON  
	
    IF @intPageNo < 1 SET @intPageNo = 1
    SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
    IF @intPageSize IS NULL OR     @intPageSize < 1 
		SET @blnBringAllRecords = 1
    ELSE BEGIN
        SET @blnBringAllRecords = 0
        SET @strPageSize = CONVERT(VARCHAR(50), @intPageSize)
        SET @strPageNo =  CONVERT(VARCHAR(50), @intPageNo)
        SET @strSkippedRows = CONVERT(VARCHAR(50), @intPageSize * (@intPageNo - 1))
    END

    IF @strFilter IS NOT NULL AND      @strFilter !=   BEGIN
        SET @strFilterCriteria =  WHERE  + @strFilter +  
        SET @strSimpleFilter =  AND  + @strFilter +  
    END ELSE BEGIN
        SET @strSimpleFilter = 
        SET @strFilterCriteria = 
    END

    IF @strSort IS NOT NULL AND      @strSort !=  
        SET @strSortCriteria =  ORDER BY  + @strSort +  
    ELSE
        SET @strSortCriteria = 

    IF @strGroup IS NOT NULL AND      @strGroup !=  
        SET @strGroupCriteria =  GROUP BY  + @strGroup +  
    ELSE
        SET @strGroupCriteria = 

    IF @blnBringAllRecords = 1 BEGIN
        EXEC (SELECT  + @strFields +  FROM  + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria)
    END ELSE BEGIN
        IF @intPageNo = 1 
            EXEC (SELECT TOP  + @strPageSize +   + @strFields +  FROM  + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria)
        ELSE BEGIN
            EXEC (SELECT  + @strFields +  FROM  + @strTables +  WHERE  + @strPK +  IN  + 
                (SELECT TOP  + @strPageSize +   + @strPK +  FROM  + @strTables + 
                     WHERE  + @strPK +  NOT IN  + 
                        (SELECT TOP  + @strSkippedRows +   + @strPK +  FROM  + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + )  +
                    @strSimpleFilter +
                    @strGroupCriteria +
                    @strSortCriteria + )  +
                @strGroupCriteria +
                @strSortCriteria
            )
        END
    END
    IF @blnGetRecordCount = 1
        IF @strGroupCriteria != 
	        EXEC (SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM  + @strTables + @strFilterCriteria + @strGroupCriteria + ) AS tbl (id))
        ELSE
            EXEC (SELECT COUNT(*) AS RECORDCOUNT FROM  + @strTables + @strFilterCriteria + @strGroupCriteria)

Mert Sakarya
Uygulama Mimarı
mertsakarya@hotmail.com
Doğan Online A.Ş.