Persistent Computed Columns
Πολλές φορές χρειάζεται να σπάσουμε την κανονικοποίηση ενός πίνακα και να συμπεριλάβουμε computed columns. H θεωρία λέει ότι η τιμή κάθε computed column υπολογίζεται on-the-fly κάθε φορά που το χρησιμοποιεί κάποιο 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, φυσικά με trade-off τον αποθηκευτικό χώρο.
Μπορείτε να συνεχίσετε τα πειράματα. Δοκιμάστε να βάλετε ένα index πάνω στο computed πεδίο και στους δύο πίνακες για να δείτε πως θα επηρεαστούν τα αποτελέσματα. Επιπρόσθετα, πριν αποφασίσετε να τα χρησιμοποιήσετε, θα πρέπει να εξετάσετε τι γίνεται με τα write ops από πλευράς performance. Γενικά, τα write ops δημιουργούν overhead οπότε ένας πίνακας με πολλά write ops ενδέχεται να μην είναι κατάλληλος για persistent computed columns.