SQL Server (και άλλες databases)

Re: Custom Paging με Stored Procedures

rousso — Wed, 06 Jul 2005 02:49:58 GMT

Εκτός από όλα τα άλλα που γράφτηκαν παραπάνω για το θέμα σου δες επίσης στο 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> ακόμα καλύτερα.

Re: Custom Paging με Stored Procedures

cap — Fri, 17 Jun 2005 21:40:15 GMT

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

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

Re: Custom Paging με Stored Procedures

Παναγιώτης Καναβός — Fri, 17 Jun 2005 21:32:12 GMT

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

Re: Custom Paging με Stored Procedures

Παναγιώτης Καναβός — Fri, 17 Jun 2005 21:28:13 GMT

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

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

Re: Custom Paging με Stored Procedures

cap — Fri, 17 Jun 2005 20:50:56 GMT

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

Για την πρώτη μέθοδο που ανέφερες:
Εξυπνο! Υποκαθιστά το 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) και τον κατατρόπωσες

Re: Custom Paging με Stored Procedures

Παναγιώτης Καναβός — Fri, 17 Jun 2005 19:11:37 GMT

Και όμως, υπάρχει τρόπος να κάνεις 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

Re: Custom Paging με Stored Procedures

Παναγιώτης Καναβός — Fri, 17 Jun 2005 01:18:37 GMT

Η ιδέα είναι αντιγραμμένη από το βιβλίο των 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.

Custom Paging με Stored Procedures

cap — Fri, 17 Jun 2005 00:35:40 GMT

Διάβαζα σε αυτή τη σελίδα των 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 που την συμπαθείς τοοοοοσο πολύ! Ριχτα! )