Διάβαζα σε αυτή τη σελίδα των Patterns & Practices μια πρακτική που προτείνει η Microsoft για custom SP paging. Αυτό βοηθάει γενικά όταν έχεις ένα μεγάαααααλο result set και δεν θέλεις να το γυρίσεις όλο μαζί στον client (ειδικά σε asp.net), παρά μόνο τα δεδομένα που πραγματικά θέλεις.

Η Microsoft προτείνει κάτι τέτοιο:

CREATE PROCEDURE UserPaging
(
  @currentPage int = 1, @pageSize int =10
)
AS
  DECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)
 
  SET @rowsToRetrieve = (@pageSize * @currentPage)
 
  SET NOCOUNT ON
  SET @SQLSTRING = N'select CustomerID,CompanyName,ContactName,ContactTitle from ( SELECT TOP '+ CAST(@pageSize as varchar(10)) + 'CustomerId,CompanyName,ContactName,ContactTitle from (SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +
      'CustomerID,CompanyName,ContactName,ContactTitle
    FROM (SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +
        'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
         ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3) As T4 ORDER BY contactname ASC'
 
  EXEC(@SQLSTRING)
  RETURN
  GO

Εγω όμως, από την άλλη μεριά, λεω οτι αυτό δεν κάνει ΑΚΡΙΒΩΣ τη δουλειά αυτή. Επιστρέφει μεν τα data τα οποία χρειάζεσαι, αλλά υποψιάζομαι οτι το performance υποφέρει στις τελευταίες σελίδες.

Εχει κανείς κάτι άλλο υπόψη του για sp paging? Η, κατά πόσο έχει δει κανείς οτι αυτό που προτείνει η Microsoft "παιζει";

(Καναβε! Dynamic SQL που την συμπαθείς τοοοοοσο πολύ! Ριχτα! )

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Η ιδέα είναι αντιγραμμένη από το βιβλίο των Gan, Moreau, "Advanced Transact-SQL for SQL Server 2000", σελ. 655, Getting Rows m to n. Μόνο που εκεί δεν φτιάχνει ένα string αλλά έχει ένα SQL statement με το m, n σαν αριθμούς. Προφανώς, κάποιος πήρε τη λύση και την έκανε dynamic sql για να μπορέσει να βάλει παραμέτρους.
    Η επίδοση της λύσης δεν επηρρεάζεται από τον αριθμό της σελίδας, κάτι που θα δεις και αν την δοκιμάσεις στον query analyzer.

Η άλλη λύση που προτείνεται στο βιβλίο χρησιμοποιεί temporary tables και είναι πιο αργή αλλά δεν χρειάζεται dynamic sql. Με αυτή θα είχες μια λύση του στυλ:

ALTER PROCEDURE UserPaging2
(
  @currentPage int = 1, @pageSize int =10
)
AS

select identity (int,1,1) as rownum , *
into #tmpCustomers
from customers
order by contactname asc

--alter table #tmpCustomers add constraint PK_tmpCustomers PRIMARY KEY (rownum)
select * from #tmpCustomers where rownum between (@currentPage-1)*@pageSize and @currentPage*@pageSize

return


Για μια καλύτερη λύση θα πρέπει να ψάξω σε κάποιο αρχαίο κώδικα που χρησιμοποιούσε paging.

---

Παναγιώτης Καναβός, Freelancer
Twitter: http://www.twitter.com/pkanavos

Και όμως, υπάρχει τρόπος να κάνεις paging χωρίς dynamic sql και είναι και γρηγορότερος! Η λύση υπάρχει στο άρθρο "How do I page a recordset?" του Aaron Bertrand. Χρησιμοποιεί το RowCount και έχει την παρακάτω μορφή:

CREATE PROCEDURE SampleCDs_Paging_Rowcount 
    @pagenum INT = 1, 
    @perpage INT = 50 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    DECLARE 
        @ubound INT, 
        @lbound INT, 
        @pages INT, 
        @rows INT 
 
    SELECT 
        @rows = COUNT(*), 
        @pages = COUNT(*) / @perpage 
    FROM 
        SampleCDs WITH (NOLOCK) 
 
    IF @rows % @perpage != 0 SET @pages = @pages + 1 
    IF @pagenum < 1 SET @pagenum = 1 
    IF @pagenum > @pages SET @pagenum = @pages 
 
    SET @ubound = @perpage * @pagenum  
    SET @lbound = @ubound - (@perpage - 1)  
 
    SELECT 
        CurrentPage = @pagenum, 
        TotalPages = @pages, 
        TotalRows = @rows 
 
    -- this method determines the string values 
    -- for the first desired row, then sets the 
    -- rowcount to get it, plus the next n rows 
 
    DECLARE @aname VARCHAR(64), @title VARCHAR(64) 
 
    SET ROWCOUNT @lbound 
 
    SELECT 
        @aname = ArtistName, 
        @title = Title 
    FROM 
        SampleCDs WITH (NOLOCK) 
    ORDER BY 
        ArtistName, 
        Title 
 
    SET ROWCOUNT @perPage 
 
    SELECT 
        ArtistName, 
        Title 
    FROM 
        SampleCDs WITH (NOLOCK) 
    WHERE 
        ArtistName + '~' + Title 
        >= @aname + '~' + @title 
    ORDER BY 
        ArtistName, 
        Title 
 
    SET ROWCOUNT 0 
END 
GO

---

Παναγιώτης Καναβός, Freelancer
Twitter: http://www.twitter.com/pkanavos

Σχολιάζω, όχι τόσο για μένα και για σένα, όσο πιθανώς για τρίτους αναγνώστες:

Για την πρώτη μέθοδο που ανέφερες:
Εξυπνο! Υποκαθιστά το row number το οποίο δεν υφίσταται (ακόμα), με ένα identity field στον προσωρινό πίνακα ωστε να έχει την κατάταξη που του χρειάζεται για να επιλέξει τα records. Βεβαια, για πολύ μεγάλα result sets είναι overkill μια και χρειάζεται να κάνει πάντα insert into το πλήρες result set.

Μια εναλλακτική που μου πέρασε από το μυαλό είναι (οταν και αν γίνεται) ο προσωρινός πίνακας να μην έχει το πλήρες result set, αλλά μόνο τα primary keys (αν υφίστανται) του result set στο οποίο θέλεις εσύ να κάνεις paging. Μετά, αφού βρεις τι θέλεις, κάνεις join το result set που προκύπτει από τον προσωρινό πίνακα με το result set στο οποίο θέλεις να κάνεις paging. Από πλευράς performance, σε μεγάλα result sets, υποψιάζομαι οτι ίσως αυτό έχει κάποια βελτίωση.

Για την δεύτερη μέθοδο που ανέφερες:
Πρωτότυπο! Το rowcount ήταν μια τεχνική που δεν την είχα υπόψη μου. Το κακό είναι οτι και εδώ το performance γίνεται χειρότερο όταν βρίσκεσαι στις τελευταίες σελίδες (μια και διαλέγεις όλα τα records ΜΕΧΡΙ την τελευταία σελίδα για να πάρεις το offset και να συνεχίσεις με τα επόμενα n records οπου n=page size), συν το οτι για να αποφύγεις την dynamic sql δεν έχεις έλεγχο επάνω στο sorting, αλλά το ορίζεις στατικά. Ειναι πολύ πολύ χρήσιμο σε κάποιες περιπτώσεις παρ'όλα αυτά.

Παντως δεν μπορείς να πεις, αυτή τη φορά σου έδωσα στο πιάτο τον "εχθρό" (dynamic sql) και τον κατατρόπωσες

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Μή βιάζεσαι! Το δοκίμασες? Μόνο αν δοκιμάσεις και τις δύο μεθόδους μπορείς να βγάλεις συμπέρασμα. Εξάλλου και η πρώτη λύση με τα αλλεπάλληλα top το ίδιο κάνει, απλά δεν το βλέπεις. Στην πραγματικότητα όμως, ο SQL χρησιμοποιεί τα indexes από πίσω για να κάνει τη δουλειά χωρίς να χρειαστεί να διαβάσει τα πάντα.

Αν δεν δοκιμάσεις, δεν βγάζεις άκρη. Ανάλογα με τα indexes και τα στατιστικά των πινάκων το ένα ή το άλλο να βγουν πιο γρήγορα

---

Παναγιώτης Καναβός, Freelancer
Twitter: http://www.twitter.com/pkanavos

Και για το sorting. Δες αυτό το άρθρο του Erland Sommarskog πως να το κάνεις χωρίς dynamic sql. Έχει και άλλα ενδιαφέροντα άρθρα όπως Dynamic Search Conditions και Arrays and Lists in SQL Server, ή πως να περάσεις λίστα από IDs χωρίς dynamic sql και ιστορίες

---

Παναγιώτης Καναβός, Freelancer
Twitter: http://www.twitter.com/pkanavos

Πανο μπράβο! Εισαι αστείρευτος! Υποκλίνομαι!

Οι παρατηρήσεις, για να μην παρεξηγηθώ, δεν είχαν μορφή κριτικής για τις προτεινόμενες λύσεις, ήταν απλά αυτά που μου έκαναν εντύπωση με την πρώτη ματιά. Φυσικά και χωρίς εφαρμογή σε συγκεκριμένη περίπτωση δεν μπορεί κανεις να βγάλει συμπεράσματα.

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Εκτός από όλα τα άλλα που γράφτηκαν παραπάνω για το θέμα σου δες επίσης στο patterns & practices αυτό το αρθράκι το οποίο έχει διάφορους τρόπους να σου προτείνει ανάλογα με το την περίπτωση...

Πάντως αυτό με το ROWCOUNT δεν είμαι σίγουρος ότι είναι η καλύτερη ιδέα. Εγώ θεωρούσα ότι το ROWCOUNT παρέμενε για λόγους backward compatibility με τον SQL 7 και δεν του έδινα σημασία, (εξάλλου γι αυτό υπάρχει και το TOP n). Θα το δω όμως πιο προσεκτικά όταν έχω χρόνο και τα λέμε ξανά...

Πάντως αυτό που εγώ θεωρώ πιο κομψό από όλα είναι μέσα στο άρθρο που προανέφερα και συνοψίζεται στον παρακάτω ψευδοκώδικα:

SELECT TOP <pageSize> <Column1>, ..., <ColumnN>
  FROM
  (SELECT TOP <currentPageNumber * pageSize> 
          <Column1>, ..., <ColumnN>
   FROM 
     <YourTable> AS T1 ORDER BY <SortColumn> DESC)
  AS T2 ORDER BY <SortColumn> ASC

Αυτό prepared μέσα σε ένα stored proc τρέχει μια χαρά και εφόσον έχεις και indexed το <SortColumn> ακόμα καλύτερα.

---

rousso