Persistent Computed Columns


Πολλές φορές χρειάζεται να σπάσουμε την κανονικοποίηση ενός πίνακα και να συμπεριλάβουμε computed columns. H θεωρία λέει ότι η τιμή κάθε computed column υπολογίζεται onthefly κάθε φορά που το χρησιμοποιεί κάποιο query και στη βάση δεν αποθηκεύεται τίποτα, έχουμε δηλαδή ένα virtual πεδίο. Όταν όμως η φόρμουλα του computed column είναι περίπλοκη (πχ μπορεί να περιέχει CASE) τότε ενδεχομένως να θέλαμε να αφαιρέσουμε λίγο φορτίο από τη CPU σε βάρος λίγου (ή πολύ) χώρου στο δίσκο ο οποίος θα μπορούσε να φιλοξενεί τις υπολογισμένες τιμές του πεδίου.

 

Mια τεχνική που χρησιμοποιούσαμε μέχρι σήμερα για να κάνουμε persist τις computed τιμές, ήταν να βάζουμε index πάνω στο computed πεδίο ώστε να έχουμε έτοιμες τις τιμές που θέλουμε να διαβάσουμε. Το πρόβλημα είναι ότι η συντήρηση του index έχει overhead γιατί το index δεν είναι και τόσο κατάλληλο γι αυτή τη δουλειά. Ένα UPDATE στα πεδία που απαρτίζουν το computed column ενδέχεται να έχει ως αποτέλεσμα πολύ περισσότερα write ops απ’ όσα χρειάζονται για ένα αντίστοιχο απλό update ενός τυπικού πεδίου (όταν αυτό δεν έχει index), καθώς ενδεχομένως να χρειαστεί να γίνει και restructure του index.

 

Στον SQL Server 2005 υπάρχει η δυνατότητα να δηλώσουμε ένα computed column ως persistent πράγμα που σημαίνει ότι ο server θα διαχειρίζεται διαφανώς τις τιμές του computed πεδίου. Όταν τις διαβάζουμε, θα τις διαβάζουμε από τον δίσκο και όταν αλλάζουν τα πεδία που απαρτίζουν το computed column (στα Inserts και Updates) θα τo ενημερώνει αυτόματα. Ας δούμε ένα παράδειγμα:

 

Αρχικά, θα φτιάξουμε δύο πίνακες, έναν με persistent και ένα με μη-persistent computed column.

CREATE TABLE dbo.Product (

    ProductID INT,

    ProductName NVARCHAR(50),

    SellStartDate DATETIME NOT NULL,

    SellEndDate DATETIME NOT NULL,

    SellDuration AS DATEDIFF(dd, SellStartDate, SellEndDate) )

GO

CREATE TABLE dbo.ProductPCC (

    ProductID INT,

    ProductName NVARCHAR(50),

    SellStartDate DATETIME NOT NULL,

    SellEndDate DATETIME NOT NULL,

    SellDuration AS DATEDIFF(dd, SellStartDate, SellEndDate) PERSISTED )

GO

Το computed column είναι πολύ απλό, ίσα-ίσα για το proof-of-point. H μόνη διαφορά στους δύο πίνακες είναι το PERSISTED keyword στο πεδίο SellDuration. Κατόπιν θα γεμίζουμε τους δύο αυτούς πίνακες με δοκιμαστικά data.

DECLARE @i INT

DECLARE @SellStartDate DATETIME

DECLARE @SellEndDate DATETIME

SET @i = 1

WHILE @i < 1000

    BEGIN

        SET @SellStartDate = CAST(( 365.2422 * 105 ) * RAND() AS DATETIME)

        SET @SellEndDate = DATEADD(d, RAND() * 10000, @SellStartDate)

        INSERT  INTO Product

        VALUES  ( @i,

                  ‘test’ + CAST(@i AS VARCHAR),

                  @SellStartDate,

                  @SellEndDate )

        INSERT  INTO ProductPCC

        VALUES  ( @i,

                  ‘test’ + CAST(@i AS VARCHAR),

                  @SellStartDate,

                  @SellEndDate )

        SET @i = @i + 1

    END

GO

1000 εγγραφές με τυχαία SellStartDate και SellEndDate. Τώρα είμαστε έτοιμοι να δώσουμε τα query μας και να δούμε πως συμπεριφέρονται. Προσοχή! Επειδή μετά από κάθε SELECT θα χρησιμοποιούμε δύο Data Management Views για να μετρήσουμε την απόδοσή του, θα πρέπει να καθαρίζουμε την cache του Query Engine ώστε να έχουμε αξιόπιστα αποτελέσματα. Δίνουμε λοιπόν

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

Και τρέχουμε το πρώτο query. Αυτό δεν χρησιμοποιεί καθόλου computed columns και δουλεύει με την παραδοσιακή τεχνική.

SELECT  Product.ProductID,

        Product.ProductName,

        Product.SellStartDate,

        Product.SellEndDate,

        Product.SellDuration,

        DATEDIFF(dd, SellStartDate, SellEndDate) SellDuration

FROM    Product

Τώρα θα χρησιμοποιήσουμε τα δύο Data Management Views. Τα Data Management Views είναι κάποια system views που παρέχουν διάφορες πληροφορίες και είναι εξαιρετικά χρήσιμα. Αποτελούν το νέο τυποποιημένο τρόπο να κάνουμε διάφορες δουλίτσες, εκεί που χρησιμοποιούσαμε μέχρι σήμερα DBCCs, εξωτερικά εργαλεία, κλπ.

Το Sys.Dm_Exec_Query_Stats μας παρέχει στατιστικά για τα queries που έχουν εκτελεστεί. Είναι πολύ χρήσιμο καθώς μας γλυτώνει από την ανάγκη να χρησιμοποιήσουμε τον Profiler. Το sys.dm_exec_sql_text είναι βοηθητικό, για να πάρουμε το κείμενο του κάθε query. Τρέχουμε το παρακάτω λοιπόν:

SELECT  s2.text,

        total_worker_time,

        total_physical_reads,

        total_logical_reads,

        total_elapsed_time

FROM    Sys.Dm_Exec_Query_Stats

        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

 

και παίρνουμε

 

total_worker_time

total_physical_reads

total_logical_reads

total_elapsed_time

112742

3

9

126750

 

Ξανακάνουμε το ίδιο (μην ξεχάσετε τα DBCC), για το επόμενο query που χρησιμοποιεί το computed column:

GO

SELECT  Product.ProductID,

        Product.ProductName,

        Product.SellStartDate,

        Product.SellEndDate,

        Product.SellDuration

FROM    Product

GO

To query αυτό μας δίνει

 

total_worker_time

total_physical_reads

total_logical_reads

total_elapsed_time

73125

3

9

74515

 

Και τέλος, άλλη μια από τα ίδια για το persisted computed column

SELECT  ProductPCC.ProductID,

        ProductPCC.ProductName,

        ProductPCC.SellStartDate,

        ProductPCC.SellEndDate,

        ProductPCC.SellDuration

FROM    ProductPCC

GO

To DMV query μας δίνει τα παρακάτω αποτελέσματα:

total_worker_time

total_physical_reads

total_logical_reads

total_elapsed_time

58632

6

9

61130

Συγκεντρωτικά, έχουμε τα παρακάτω αποτελέσματα (τα δικά σας θα διαφέρουν):

 

 

total_worker_time

total_physical_reads

total_logical_reads

total_elapsed_time

Query 1

112742

3

9

126750

Query 2

73125

3

9

74515

Query 3

58632

6

9

61130

 

Γενικά, φαίνεται ότι τα persistent computed columns έχουν πολύ καλύτερο performance, φυσικά με tradeoff τον αποθηκευτικό χώρο.

 

Μπορείτε να συνεχίσετε τα πειράματα. Δοκιμάστε να βάλετε ένα index πάνω στο computed πεδίο και στους δύο πίνακες για να δείτε πως θα επηρεαστούν τα αποτελέσματα. Επιπρόσθετα, πριν αποφασίσετε να τα χρησιμοποιήσετε, θα πρέπει να εξετάσετε τι γίνεται με τα write ops από πλευράς performance. Γενικά, τα write ops δημιουργούν overhead οπότε ένας πίνακας με πολλά write ops ενδέχεται να μην είναι κατάλληλος για persistent computed columns.

 

Advertisements


Σχολιάστε

Εισάγετε τα παρακάτω στοιχεία ή επιλέξτε ένα εικονίδιο για να συνδεθείτε:

Λογότυπο WordPress.com

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό WordPress.com. Αποσύνδεση / Αλλαγή )

Φωτογραφία Twitter

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Twitter. Αποσύνδεση / Αλλαγή )

Φωτογραφία Facebook

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Facebook. Αποσύνδεση / Αλλαγή )

Φωτογραφία Google+

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Google+. Αποσύνδεση / Αλλαγή )

Σύνδεση με %s