go backarticles

Articles of SQLschool.gr Team

Date Correlation in SQL Server

Fivi Panopoulou - Sotiris Karras

Είναι πολλές οι φορές που στο business logic μίας εφαρμογής, υπάρχουν ημερομηνίες οι οποίες συνδέονται λογικά μεταξύ τους. Για παράδειγμα, οι ημερομηνίες αγοράς ενός προϊόντος με τις ημερομηνίες πληρωμής ή οι ημερομηνίες παραγγελίας με τις ημερομηνίες παράδοσης.

Ο SQL Server έχει την δυνατότητα να υπολογίζει το correlation μεταξύ τέτοιων ημερομηνιών και θα δούμε σε αυτό το post με ποιον τρόπο μπορούμε να το χρησιμοποιήσουμε στα δεδομένα μας.

Έχουμε δύο πίνακες οι οποίοι αντιπροσωπεύουν παραστατικά και πληρωμές αυτών τους Invoices και Payments αντίστοιχα. Παρακάτω φαίνονται τα CREATE statements τους.

CREATE TABLE Invoices  (InvoiceID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
                       ,CustomerID INT NOT NULL
                       ,DateCreated DATETIME NOT NULL
                       ,Amount DECIMAL(5,2) NOT NULL
                       ,TaxAmount DECIMAL(5,2));


CREATE TABLE Payments (PaymentID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
                      ,InvoicesID INT NOT NULL
                      ,PaymentDate DATETIME NOT NULL
                      ,PaymentAmount DECIMAL(5,2) NOT NULL
                      ,PaymentType INT NULL
                      ,CONSTRAINT FK_Pay_InvID FOREIGN KEY (InvoicesID) REFERENCES Invoices(InvoiceID));                      
GO

CREATE NONCLUSTERED INDEX IX_DateCreated ON Invoices(DateCreated) ;
GO

Είναι προφανές, ότι τα πεδία DateCreated και PaymentDate τα οποία αφορούν τις ημερομηνίες έκδοσης και πληρωμής ενός παραστατικού, συνδέονται μεταξύ τους. Ας δούμε ένα SELECT statement το οποίο φέρνει στοιχεία από τους δύο αυτούς πίνακες για ένα συγκεκριμένο χρονικό διάστημα έκδοσης παραστατικών.

SELECT PaymentID AS Payment, PaymentAmount, DateCreated AS [Invoice Date], PaymentDate
FROM Invoices    
INNER JOIN Payments
ON Invoices.InvoiceID = Payments.InvoicesID
WHERE Invoices.DateCreated BETWEEN '20110101' AND '20121231';

Το execution plan του παραπάνω είναι το ακόλουθο:

Σε αυτό, βλέπουμε ότι έχουμε ένα index seek πάνω στον πίνακα των Invoices (στον NONCLUSTERED INDEX που έχουμε ορίσει στο DateCreated) το οποίο θα μας επιστρέψει τις εγγραφές για το συγκεκριμένο χρονικό διάστημα που ορίσαμε στο WHERE και ένα index scan πάνω στον πίνακα των Payments για να μας φέρει και τις πληροφορίες αυτού.

Ιδανικά, επειδή τα δύο πεδία ημερομηνιών συνδέονται μεταξύ τους, θα θέλαμε να αντικαταστήσουμε το index scan στον πίνακα των Payments με ένα index seek με βάση τις ημερομηνίες πληρωμής

Για να μπορεί ο SQL Server να υπολογίσει το correlation μεταξύ των δύο στηλών θα πρέπει να ικανοποιούνται οι εξής συνθήκες:

  • Οι πίνακες πρέπει να συνδέονται με foreign key το οποίο να περιέχει μία μόνο στήλη (στην περίπτωσή μας τα Payments συνδέονται με τον Invoices στο InvoiceID).
  • Κάθε πίνακας πρέπει να έχει τουλάχιστον ένα NOT NULL date πεδίο.
  • Ένα από τα date πεδία πρέπει να είναι το πρώτο σε σειρά εμφάνισης σε έναν CLUSTERED INDEX πάνω σε κάποιον πίνακα.

Ας δημιουργήσουμε λοιπόν και εμείς έναν composite clustered index πάνω στον Payments ο οποίος θα περιέχει τα (PaymentDate, PaymentID) πεδία.

CREATE CLUSTERED INDEX PK_PayDate_PayID ON Payments(PaymentDate,InvoicesID) ;
GO

Σε αυτό το σημείο ο SQL Server είναι έτοιμος να υπολογίσει το correlation μεταξύ των δύο ημερομηνιών. Το τελευταίο βήμα που πρέπει να γίνει είναι να θέσουμε το DATE_CORRELATION_OPTIMIZATION ON στην βάση που δουλεύουμε.

ALTER DATABASE DateCorrelationDemo 
    SET DATE_CORRELATION_OPTIMIZATION ON;
GO

Ας ξανατρέξουμε τώρα το παραπάνω SELECT statement και ας δούμε το execution plan που θα προκύψει αυτή τη φορά:

Βλέπουμε ότι ο SQL Server έχοντας υπολογίσει το correlation μπορεί να εφαρμόσει φίλτρο το οποίο έχει δημιουργήσει μόνος του πάνω στο PaymentDate και να μετατρέψει το προηγούμενο index scan σε index seek, έχοντας με αυτόν τον τρόπο αισθητά καλύτερο performance.


Fivi Panopoulou

Fivi Panopoulou

System Engineer • Speaker

Το 2007 ξεκίνησα τις σπουδές μου στη σχολή Ηλεκτρολόγων Μηχανικών και Μηχανικών Υπολογιστών στο Εθνικό Μετσόβιο Πολυτεχνείο.Κατά την διάρκεια των σπουδών μου εκεί αγάπησα τον προγραμματισμό και τα συστήματα πληροφορικής, καθώς επίσης απέκτησα το ιδιαίτερο ενδιαφέρον μου για τις βάσεις δεδομένων. Κατά την διάρκεια της διπλωματικής μου ασχολήθηκα με ζητήματα ανωνυμοποίησης δεδομένων και την ανάπτυξη σχετικού εργαλείου. Τα τελευταία χρόνια των σπουδών μου, μου δόθηκε η ευκαιρία να ασχοληθώ περισσότερο και να διευρύνω τους ορίζοντές μου ως Microsoft Student Partner και μέσω της κοινότητας Student Guru. Στα πλαίσια των κοινοτήτων αυτών, ξεκίνησα να ασχολούμαι με παρουσιάσεις αλλά και να γνωρίζω τον SQL Server. Από την πρώτη στιγμή που ασχολήθηκα μαζί του, συνειδητοποίησα πόσο ήθελα να εμβαθύνω τις γνώσεις μου σχετικά με αυτόν και τα συστήματα διαχείρισης βάσεων δεδομένων γενικότερα, πράγμα που προσπαθώ να κάνω έκτοτε. Πριν χρόνια είχα την τύχη να συμμετέχω στο πρόγραμμα mentoring, μέσω του οποίου γνώρισα τον κ. Χατζηπαυλή. Από τότε συμμετέχω στην ομάδα του SQLschool.gr.


Sotiris Karras

Sotiris Karras

System Engineer • Speaker

Είμαι απόφοιτος της σχολής Ηλεκτρολόγων Μηχανικών και Μηχανικών Υπολογιστών του Εθνικού Μετσόβιου Πολυτεχνείου και στα ενδιαφέροντά μου συμπεριλαμβάνεται o τομέας του Knowledge and Data Engineering. Πιο συγκεκριμένα, έχω ασχοληθεί ακαδημαϊκά και ερευνητικά με τον τομέα του data privacy και data anonymity, ενώ πάθος μου είναι ό,τι έχει να κάνει με relational databases και data management. Στο παρελθόν, έχω συνεργαστεί με την Microsoft Hellas ως Microsoft Student Partner για ακαδημαϊκές δραστηριότητες και ήμουν μέρος του MVP mentoring προγράμματός της, στο οποίο είχα την τύχη να γνωρίσω τον κ. Χατζηπαυλή.


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.