SQL Server ve Sayfalama

Print Versiyonu

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
  

• 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.Ş.