Writing a Query: is an Art? Is Knowledge? Is Experience? Is Skill? Or is nothing of the above?

Εισαγωγή

Ξεκινώντας να γράψω το post αυτό ο προσανατολισμός μου ήταν καθαρά τεχνικός στην πορεία όμως άρχισε να μου αρέσει η ιδέα να πάρει την μορφή αυτή. Μου βγήκε αυθόρμητα γιατί πέρα από το τεχνικό του μέρος προσπαθώ να σκιαγραφήσω το άνθρωπο που έρχεται να γράψει ένα query για να καλύψει τις ανάγκες που αντιμετωπίζει στην καθημερινότητα του.

Πριν λίγες μέρες μου ήρθε ένα ερώτημα από ένα συνάδελφο που ζήταγε να αντλήσει μια πληροφορία από ένα πίνακα με πολλές χιλιάδες έγγραφές. Βασικά αυτό που ζητούσε ήταν απλά πάρει την πληροφορία με οποιοδήποτε τρόπο. Το πρώτο που τον ρώτησα ήταν αν έχει σκοπό αυτό να το κάνει επαναλαμβανόμενα αλλά η απάντηση του δεν ήταν αρκετά σαφής και συνήθως σε τέτοιες περιπτώσεις η εμπειρία έχει δείξει το αυτό θα επαναλαμβάνεται οπότε καλό είναι να βρεθεί μια λύση τέτοια που όταν εκτελείται να είναι γρήγορη και με όσο το δυνατόν την μικρότερη δυνατή κατανάλωση πόρων. Αλήθεια είναι εύκολο να γίνει κάτι τέτοιο; Μήπως είναι προτιμότερο να είναι γρήγορο ώστε να υπάρχει το μέγιστο user experience άσχετα από την κατανάλωση πόρων; Μήπως πρέπει να βρω τον τρόπο αυτό που θα είναι απλός κατανοητός και εύκολα συντηρήσιμος; Μήπως κάτι άλλο; Ποιό είναι αυτό;

Ερωτήματα που οι περισσότεροι DB people (Admins & Devs) κάθε φορά που γράφουν κάτι έχουν στο πίσω μέρος του μυαλού τους. Ερωτήματα που είναι βασανιστικά καθώς το ζητούμενο είναι ένα, όλα θα πρέπει να τρέχουν ιδανικά και να δίνουν τον σωστό αποτέλεσμα. Αλλά για στάσου πως είσαι σίγουρος ότι τους βασανίζουν αυτά τα ερωτήματα; Για να πω την αλήθεια έχω αμφιβολίες.

Ας κάνουμε μερικά δευτερόλεπτα μια παύση και με το χέρι στην καρδιά, τίμια ας δώσουμε μια απάντηση σε όλα αυτά. Μια απάντηση που δεν χρειάζεται να γνωρίζει κανείς άλλος παρά μόνο εμείς. Κρατήστε την απάντηση αυτή χωρίς να κάνετε καμία αυτοκριτική αλλά απλά σαν μια διαπίστωση.

Παράδειγμα 1ο

Για να έρθουμε ξανά στην ανάγκη του συναδέλφου και ας προσπαθήσουμε να δώσουμε λύση. Ας έρθουμε να δούμε ένα παράδειγμα το οποίο είναι όμοιο με αυτό που ζήτησε ο συνάδελφος αλλά όχι φυσικά με τα δεδομένα που αυτός είχε.

Θα χρησιμοποιήσω για αυτό τον πίνακα, Sales.SalesOrderHeader που είναι ο πίνακας των παραγγελιών που υπάρχει στην AdventureWorks2012 και έχει 31.465 records. Εντάξει δεν είναι πολλά αλλά είναι μια χαρά για το παράδειγμα μας.

Το ζητούμενο είναι το εξής:

Στο πίνακα αυτό υπάρχουν τρία πεδία ημερομηνίας τύπου datetime τα OrderDate, DueDate, ShipDate. Θέλουμε για λόγους επιχειρηματικούς που δεν θα αναλύσω εδώ να πάρω ένα αποτέλεσμα το οποίο να μου έχει τον αριθμό της παραγγελίας και την μεγαλύτερη ημερομηνία από τις υπάρχουσες τρεις ημερομηνίες που αναφέρθηκαν παραπάνω.

Σχηματικά αν θέλουμε να δούμε το παραπάνω ερώτημα μπορεί να γίνει αυτό αν εκτελέσουμε ένα query το οποίο να επιστρέφει μόνο αυτά που θέλουμε να επεξεργαστούμε και αυτό δεν είναι άλλο από.

select 
        SalesOrderID,
        OrderDate,
        DueDate,
        ShipDate
from sales.SalesOrderHeader;;
go

Τα αρχικά δεδομένα που θα έχω θα είναι τα παρακάτω (ένα μικρό δείγμα αυτών).

SalesOrderID OrderDate               DueDate                 ShipDate
------------ ----------------------- ----------------------- -----------------------
43659        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000
43660        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000
43661        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000
43662        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000
43663        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000
43664        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000
43665        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000
43666        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000
43667        2005-07-01 00:00:00.000 2005-07-13 00:00:00.000 2005-07-08 00:00:00.000

 

Αυτό που θέλω να πάρω στα χέρια μου είναι το εξής

 

SalesOrderID MaxDateValue
------------ -----------------------
43659        2005-07-13 00:00:00.000
43660        2005-07-13 00:00:00.000
43661        2005-07-13 00:00:00.000
43662        2005-07-13 00:00:00.000
43663        2005-07-13 00:00:00.000
43664        2005-07-13 00:00:00.000
43665        2005-07-13 00:00:00.000
43666        2005-07-13 00:00:00.000

 

Ελπίζοντας ότι είναι πλέον κατανοητό στον αναγνώστη του post αυτού το τι έχω στα χέρια μου και τι θέλω να παραχθεί ας έρθουμε να δούμε το πώς και με ποιο τρόπο θα φτάσω στο αποτέλεσμα.

1^η Πιθανή Υλοποίηση

Πιθανότατα η πρώτη σκέψη που θα έρθει στο μυαλό αρκετών είναι να φτιάξω ένα cursor και αφού κάνω τους ελέγχους μου να βάζω το αποτέλεσμα είτε σε ένα temp table ή σε ένα table variable. Μια πιθανή υλοποίηση της λύσης αυτής θα ήταν η παρακάτω

DECLARE cr CURSOR
FORWARD_ONLY READ_ONLY 
FOR SELECT
        SalesOrderID,
        OrderDate,
        DueDate,
        ShipDate
    FROM sales.SalesOrderHeader;

DECLARE @Results Table (OrderID int , MaxDateValue datetime);
DECLARE @OrderID int , @Orderdate datetime, @DueDate datetime, @ShipDate datetime;
DECLARE @MaxDate datetime;
OPEN cr;

FETCH NEXT FROM cr INTO @OrderID, @Orderdate , @DueDate, @ShipDate ;
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        IF  (@Orderdate > @DueDate ) SET @MaxDate = @OrderDate
        ELSE SET @MaxDate = @DueDate
        IF (@MaxDate < @ShipDate) SET @MaxDate = @ShipDate
        INSERT INTO @Results VALUES (@OrderID,@MaxDate);
    END
    FETCH NEXT FROM cr INTO @OrderID, @Orderdate , @DueDate, @ShipDate;
END;

CLOSE cr;
DEALLOCATE cr;
SELECT * FROM @Results;
GO

Αν και παίρνω το αποτέλεσμα μου μετά από 11 sec για το πλήθος των εγγραφών που ήδη έχω αναφέρει παραπάνω δεν μπορώ να είμαι ικανοποιημένος καθώς σε μεγαλύτερο όγκο εγγραφών αυτός θα ήταν αρκετά μεγαλύτερος.

Μια τέτοια λύση είναι επιλογή κάποιων που έχουν ακόμα στο μυαλό τους τα ISAM files ή είναι αρχάριοι με την Τ-SQL. Το δε κόστος σε πόρους, ιδιαίτερα αν ο όγκος των εγγραφών είναι μεγάλος, βράσε ρύζι, όχι ότι και με αυτές είναι μικρό.

2^η Πιθανή Υλοποίηση

Κάποιος άλλος θα σκέφτονταν να κάνει χρήση της CASE όπως παρακάτω

SELECT SalesOrderID,
       CASE 
       WHEN (OrderDate > DueDate) 
            AND 
            (OrderDate > ShipDate)
            THEN OrderDate
        WHEN (DueDate > OrderDate )
             AND 
             (DueDate > ShipDate )
             THEN DueDate
        WHEN (ShipDate > OrderDate)
             AND
             (ShipDate > DueDate )
             THEN ShipDate
        END AS MaxDateValue
FROM Sales.SalesOrderHeader;
GO

Σίγουρα είναι μια καλή υλοποίηση σε κατανάλωση πόρων και σε χρόνο εκτέλεσης αλλά γράφεις ένα περίπλοκο case statement που αν έχεις να συγκρίνεις πολλά είναι λίγο χαμαλοδουλειά που αν δεν την κάνεις με προσοχή ίσως να έχεις λάθος αποτέλεσμα.

3^η Πιθανή Υλοποίηση

Ένας καλός γνώστης της T-SQL ίσως να επέλεγε να κάνει χρήση της UNPIVOT σε συνδυασμό με CTE όπως παρακάτω

WITH Results (OrderID,MaxDateValue)
AS
(
 SELECT SalesOrderID,MaxDate
  FROM 
      (SELECT SalesOrderID,
              OrderDate,
              DueDate,
              ShipDate
      FROM Sales.SalesOrderHeader) AS t
      UNPIVOT 
            (MaxDate fOR dates IN 
            (OrderDate, DueDate, ShipDate)
  )AS unpvt)
SELECT OrderID,MAX(MaxDateValue) AS MaxDateValue
FROM Results
GROUP BY OrderID;
GO

Αν και από απόδοση χρόνου φαίνεται ότι είναι εξαιρετική αν συγκρίνω τα execution plans αυτής της υλοποίησης και των προηγούμενων βλέπω ότι το query relative to the batch cost είναι 51% ενώ στις προηγούμενες είναι από 24%. Αν κοιτάξω αναλυτικότερα το πλάνο εκτέλεσης αυτής βλέπω ότι γίνεται ένα nested loop με left outer join χωρίς όμως ΟΝ clause το οποίο βγαίνει γιατί πριν έχω ένα Constant Scan το οποίο διαβάζει τον πίνακα τόσες φορές όσες είναι οι εγγραφές στον πίνακα.

4^η Πιθανή Υλοποίηση

Κάποιος ίσως να σκεφτόταν την UNPIVOT αλλά χωρίς το CTE και έτσι να υλοποιούσε αυτό

SELECT T.SalesOrderID, MAX(T.MaxDate) AS MaxDateValue
 FROM 
 (SELECT SalesOrderID,MaxDate
  FROM 
      (SELECT SalesOrderID,
              OrderDate,
              DueDate,
              ShipDate
      FROM Sales.SalesOrderHeader) AS t
      UNPIVOT 
            (MaxDate fOR dates IN 
            (OrderDate, DueDate, ShipDate)
  )AS unpvt) AS T
GROUP BY T.SalesOrderID;
GO

Αλλά και αυτό έχει τα πλεονεκτήματα και τα μειονεκτήματα της τρίτης πιθανής υλοποίησης.

5^η Πιθανή Υλοποίηση

Κάποιος άλλος που είναι περισσότερο έμπειρος ίσως να λύσει το πρόβλημα με την χρήση της CROSS APPLY. Μια πιθανή τέτοια υλοποίηση θα μπορούσε να είναι η παρακάτω

WITH RESULTS (OrderID,[Date])
AS
(
SELECT OrderID,[Date]
FROM Sales.SalesOrderHeader
CROSS APPLY (VALUES (SalesOrderID,OrderDate),
                    (SalesOrderID,DueDate),
                    (SalesOrderID,ShipDate) ) T (OrderID,[Date]))
SELECT OrderID,MAX([Date]) AS MaxDateValue
FROM Results
GROUP BY OrderID;
GO

Σε αυτή την υλοποίηση κάποιοι θα ισχυριστούν ότι είναι καλύτερα αναγνώσιμη σε σχέση με την UNPIVOT και ίσως ως ένα σημείο να έχουν δίκιο. Όμως αν δούμε το relative query cost σε σχέση με τις προηγούμενες υλοποιήσεις έχει το υψηλότερο, αλλά από χρόνο απόκρισης είναι αρκετά καλή.

Θα μπορούσα να γράψω και άλλες υλοποιήσεις και είμαι σίγουρος ότι και εσείς θα έχετε και άλλες και θα ήμουν ευτυχής αν τις μοιραζόσασταν μαζί μου. Αλλά ας μείνουμε σε αυτές. Ποια θα επιλέγατε και με ποιο σκεπτικό; Σίγουρα ελκυστικές είναι αυτές που επιστρέφουν γρήγορα αποτέλεσμα καθώς το ζητούμενο είναι αυτό.

Παράδειγμα 2ο

Για να δούμε όμως ακόμα ένα παράδειγμα. Σε αυτό θα χρησιμοποιήσω την Northwind database.

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

CREATE VIEW dbo.OrdersTurnOverByCustomers
as
    SELECT TOP 999999999 o.CustomerID,sum(od.Quantity*od.Unitprice) Turnover
    FROM [Order Details] od 
        INNER JOIN Orders o ON od.OrderID=o.OrderID
    GROUP BY o.CustomerID
    ORDER BY o.CustomerID;
GO

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

1^η Πιθανή Υλοποίηση

Ακούω στα αυτιά μου κάποιους να λένε ότι θα χρησιμοποιούσαν μια function που θα επέστρεφε το συνολικό τζίρο και θα έκαναν μετά την πράξη για την εύρεση του ποσοστού. Ακούγεται ελκυστική λύση για να την δούμε φτιάχνοντας μια πιθανή υλοποίηση της function όπως παρακάτω

CREATE FUNCTION dbo.OrdersTotalTurnover() RETURNS DECIMAL(15,2)
AS
BEGIN
    DECLARE @RV DECIMAL(15,2);
    SELECT @RV=SUM(Quantity*UnitPrice)
    FROM [Order Details]
    RETURN @RV
END

Και στο υπάρχον view κάνουμε την χρήση της πιθανός όπως παρακάτω

SELECT CustomerID,Turnover,
       CAST((100. * Turnover / dbo.OrdersTotalTurnover()) AS decimal(5, 2)) 
       AS ParticipationPrc
FROM dbo.OrdersTurnOverByCustomers;
GO

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

2^η Πιθανή Υλοποίηση

Κάποιος άλλος ίσως να επέλεγε την λύση με sub-query και η πιθανή υλοποίηση του να ήταν η παρακάτω

SELECT CustomerID,Turnover,
       CAST((100. * Turnover / (SELECT SUM(Quantity*UnitPrice) 
                                FROM [Order Details])) AS decimal(5, 2)) 
                                AS ParticipationPrc
FROM dbo.OrdersTurnOverByCustomers;
GO

Κάνοντας μια αντιπαραβολή στο κόστος θα δούμε ότι είναι 50-50.

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

3^η Πιθανή Υλοποίηση

Κάποιος άλλος ίσως να επέλεγε να φτιάξει μια stored procedure που να έκανε αυτά που ήθελε χωρίς να χρησιμοποιούσε το υπάρχον view. Η πιθανή της υλοποίηση θα μπορούσε να ήταν η παρακάτω

CREATE PROC dbo.spCustomerOrdersTurnoverStats
AS
BEGIN
    DECLARE @TOTAL DECIMAL(15,2);
    
    SELECT @TOTAL=SUM(Quantity*UnitPrice)
    FROM [Order Details];

    SELECT o.CustomerID,sum(od.Quantity*od.Unitprice) AS Turnover,
    CAST((100. * sum(od.Quantity*od.Unitprice) / @TOTAL) AS decimal(5, 2)) 
    AS ParticipationPrc
    FROM [Order Details] od 
        INNER JOIN Orders o ON od.OrderID=o.OrderID
    GROUP BY o.CustomerID
    ORDER BY o.CustomerID;
END

Αντιπαραβάλλοντας τις τρεις αυτές υλοποιήσεις για τον όγκο των δεδομένων που έχουμε αυτή την στιγμή βλέπουμε ότι από άποψη χρόνου όλες τα πάνε καλά με ασήμαντες διαφορές μεταξύ τους. Επίσης η κατανάλωση πόρων είναι όμοια. Αλλά αν το δούμε από μια μεριά προγραμματιστή η χρήση την procedure είναι αρκετά ποιο ξεκάθαρη και ίσως ιδανική καθώς διαβάζω μια φορά το σύνολο και το αποθηκεύω σε μεταβλητή την οποία και χρησιμοποιώ στους υπολογισμούς μου.

4^η Πιθανή Υλοποίηση

Ίσως κάποιος να μην τον βόλευε η χρήση procedure και να επέλεγε να φτιάξει μια UDF και ίσως η πιθανή της υλοποίηση να ήταν η παρακάτω

CREATE FUNCTION dbo.fnCustomerOrdersTurnoverStats() RETURNS @T table
(
    CustomerID nchar(5),
    TurnOver decimal(15,2),
    ParticipationPrc decimal(5,2)
)
AS
BEGIN
    DECLARE @TOTAL DECIMAL(15,2);
    
    SELECT @TOTAL=SUM(Quantity*UnitPrice)
    FROM [Order Details];

    INSERT INTO @T
    SELECT o.CustomerID,CAST(SUM(od.Quantity*od.Unitprice) AS decimal(15, 2)),
    CAST((100. * sum(od.Quantity*od.Unitprice) / @TOTAL) AS decimal(5, 2)) 
    FROM [Order Details] od 
        INNER JOIN Orders o ON od.OrderID=o.OrderID
    GROUP BY o.CustomerID;
    RETURN;
END;
GO


SELECT * FROM dbo.fnCustomerOrdersTurnoverStats()
ORDER BY CustomerID;
GO

 

5^η Πιθανή Υλοποίηση

Κάποιος άλλος να έκανε τις δύο τελευταίες υλοποιήσεις και να σαν οπαδός του reusability να έκανε χρήση της function που επιστρέφει το συνολικό τζίρο των παραγγελιών και του view που έτσι και αλλιώς επιστρέφει το τζίρο ανά πελάτη. Δεν σας παραθέτω υλοποίηση καθώς είναι προφανές ποιες θα είναι με βάση τις παραπάνω.

6^η Πιθανή Υλοποίηση

Ένας άλλος θα επέλεγε να κάνει χρήση των CTEs με μια πιθανή υλοποίηση όπως

WITH OrdersTotal
AS
(
    SELECT SUM(Quantity*UnitPrice) AS OrdersTotal
    FROM [Order Details]
)
SELECT CustomerID,Turnover,
       CAST((100. * Turnover / t.OrdersTotal) AS decimal(5, 2)) AS ParticipationPrc
FROM dbo.OrdersTurnOverByCustomers
CROSS JOIN OrdersTotal AS T;
GO

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

7^η Πιθανή Υλοποίηση

Και μια ακόμα υλοποίηση που θα μπορούσαμε να έχουμε εάν είμαστε σε SQL Server 2012 με την χρήση των T-SQL Windows Functions η οποία κατά την γνώμη μου είναι αρκετά καλή

SELECT CustomerID,Turnover,
       CAST(100. * Turnover / SUM(Turnover) OVER() AS decimal(5, 2)) 
       AS ParticipationPrc
FROM dbo.OrdersTurnOverByCustomers;

Ερώτημα αντί για  Επίλογο

Μετά από όλα αυτά τελικά το να γράφεις ένα query είναι τέχνη, είναι γνώση, είναι εμπειρία, είναι ικανότητα, είναι όλα μαζί, δεν είναι τίποτα από όλα αυτά; Τι είναι τελικά; Οι απόψεις σας ευπρόσδεκτες.

Υ.Γ To post αυτό το αφιερώνω στους αδελφούς developers…

/*antonch*/