Καλώς ορίσατε στο dotNETZone.gr - Σύνδεση | Εγγραφή | Βοήθεια

Using a trigger to perform mass-update-safe, specific-field calculations

 

I ran into this scenario a couple of days ago. Nothing special or complex, but I'd just like to share it with you.

 

The scenario

You’ve got a table which contains some information and you want to perform calculations based on this information and store them in the same table or in another table.

The calculations must always be up-to-date.

The calculations are performed via a complex User Defined Function which accepts the other fields’ values as parameters.

The table has got thousands of rows and thus you do not want to use a view because it’ll degrade the overall performance.

For the same reason, you do not want to use a calculated field.

 

For the sake of simplicity, let’s just suppose that we’ve got a Products  table that has the following fields:

Id int

prodName varchar (100)

(various other product-related fields)

prodAvailabilityGrade int

prodQualityClassification char(1)

 

We want to calculate the possible revenue we would have from every product in our table using a UDF (let’s call it myComplexUDF) which will accept the availability grade and the classification of the product  and, based on a complex and time-consuming formula (which may involve lookups to other tables or complex statistical calculations), will produce a number, our estimated revenue for the product.

 

We are also aware that our table contains thousands of rows, each representing a product.

 

Let’s define myComplexUDF here:

 

CREATE FUNCTION dbo.myComplexUDF (prodAvailabilityGrade int, prodClassification char(1))

RETURNS int AS

BEGIN

            -- our specific code here

END

 

For the sake of simplicity, let’s suppose that the results of myComplexUDF will be going into a field on the same table:

 

prodEstimatedRev int

 

Now, let’s see: We haven’t got the option to allow real-time calculation of the values, since that would take too much time (we’re talking about thousands of records here!) So our last resort is to use a trigger for our update (and possibly, insert) operations. The trigger should be able to use the UDF to pre-calculate our value each time we update or insert a record.

 

The first approach

 

Let’s see a first version of the trigger (Body only):

 

DECLARE @ID int

DECLARE @QC char(1)

DECLARE @AG int

 

SELECT

     @ID=id

   , @ QC = prodQualityClassification

   , @AG= prodAvailabilityGrade

FROM

   Inserted

 

UPDATE

   Products

SET

   prodEstimatedRev = dbo.myComplexUDF (@AG, @QC)

WHERE

   Products.ID = @ID

 

But, with this trigger, we’ve got the following problem:

Product attributes get updated often. Our table has (supposedly) 10s of columns, but we only need to recalculate the value only when prodAvailabilityGrade or prodQualityClassification change, since these are the only fields participating in the UDF.

 

The second approach

 

To solve this, we will use the IF UPDATE clause to make the trigger run only if the two fields that interest us are updated. Here goes:

 

DECLARE @ID int

DECLARE @QC char(1)

DECLARE @AG int

 

IF UPDATE (prodAvailabilityGrade) OR UPDATE (prodQualityClassification)

BEGIN

   SELECT

        @ID=id

      , @ QC = prodQualityClassification

      , @AG= prodAvailabilityGrade

   FROM

      Inserted

 

   UPDATE

      Products

   SET

      prodEstimatedRev = dbo.myComplexUDF (@AG, @QC)

   WHERE

      Products.ID = @ID

END

 

Okay, we solved that. Now, what if we need to do a mass, single-statement update (e.g. UPDATE Products SET prodQualityClassification = ‘A’ WHERE SomeField = ‘SomeValue’)? This would essentially make the trigger run only once and we would not be able to update the corresponding rows with the new values for the prodEstimatedRev field.

 

The third approach

 

In order to solve this problem as well, we modify our trigger as following:

 

DECLARE @ID int

 

IF UPDATE (prodAvailabilityGrade) OR UPDATE (prodQualityClassification)

BEGIN

 

   UPDATE

      Products

   SET

      prodEstimatedRev = dbo.myComplexUDF (

           Inserted.prodAvailabilityGrade

         , Inserted.prodQualityClassification)

FROM

   Products

   INNER JOIN Inserted ON Products.ID = Inserted.ID

 

END

 

Conclusion

 

What we did was to update the Products table based on a join with the Inserted table (which, in the case of a batch update, contains more than one rows). We used the values from the Inserted table as arguments to the UDF and we are now sure that our table is batch-update-safe, and that our trigger will run only in the case that the two fields that interest us are updated.

 

I hope that helps you solve things quicker when you run into a similar situation.

 

  

 

 

kick it on DotNetKicks.com

 

 

 

Έχουν δημοσιευτεί Δευτέρα, 12 Σεπτεμβρίου 2005 8:42 μμ από το μέλος cap
Δημοσίευση στην κατηγορία:

Ενημέρωση για Σχόλια

Αν θα θέλατε να λαμβάνετε ένα e-mail όταν γίνονται ανανεώσεις στο περιεχόμενο αυτής της δημοσίευσης, παρακαλούμε γίνετε συνδρομητής εδώ

Παραμείνετε ενήμεροι στα τελευταία σχόλια με την χρήση του αγαπημένου σας RSS Aggregator και συνδρομή στη Τροφοδοσία RSS με σχόλια

Σχόλια:

# Απ: Using a trigger to perform mass-update-safe, specific-field calculations

Δευτέρα, 12 Σεπτεμβρίου 2005 11:25 μμ by rousso
Κλασικό.

Αν και νομίζω ότι θα έτερχε πιο γρήγορα αν αντί για INNER JOIN στο third approach χρησιμοποιούσε RIGHT JOIN
Δηλαδή: UPDATE ... SET ... FROM Products RIGHT JOIN Inserted ON ...

Δεν νομίζω να το πιάνει ο optimizer αυτό...

Γενικά τα INNER JOINs έχουν να κάνουν περισσότερη δουλειά από τα LEFT/RIGHT JOINs οπότε λογικά είναι πιό αργά.

Σκέψου ότι το INNER JOIN πρέπει να σκανάρει και τα δύο tables για τιμές που είναι ίσες, ενώ το RIGHT JOIN κοιτάει κατευθείαν μόνο τις τιμές του Inserted που είναι λιγότερες και ακριβώς ο αριθμός των records που πρέπει να συνδεθούν.

Θέλει βέβαια λίγο μεγαλύτερη ανάλυση για να το καλύψεις όλο το θέμα αλλά το άρθρο δίνει την κεντρική ιδέα. Χρειάζεται αντίστοιχο trigger και για το INSTEAD OF INSERT κλπ...

Αν θυμάσαι που σας έλεγα για ένα projectάκι που έφτιαχνα με multilingual SQL Server databases... Εκεί το χρησιμοποιούσα κατα κόρον αυτό το σκεπτικό.

rousso

# Απ: Using a trigger to perform mass-update-safe, specific-field calculations

Δευτέρα, 12 Σεπτεμβρίου 2005 11:31 μμ by cap
Ναι, δεν διαφωνώ σε αυτό. Το κύριο μέλημα ήταν βέβαια να αποφύγω το βάρος της εκτέλεσης του UDF για όλα τα records, οπότε δεν έδωσα τόσο μεγάλη σημασία στον τύπο του join. Παρ'όλα αυτά, ναι, νομίζω οτι έχεις δίκιο.

Τωρα για το instead of insert δεν κατάλαβα γιατί χρειάζεται, θελεις να το συνεχίσεις λίγο;

# Απ: Using a trigger to perform mass-update-safe, specific-field calculations

Τρίτη, 13 Σεπτεμβρίου 2005 10:40 πμ by rousso
όπως θες να ενημερώνεις τα πεδία που έχουν το calculation χωρίς να είναι calculated fileds όποτε κάνεις update... υποθέτω ότι προφανώς θέλεις να αποθηκεύεις τις αρχικές τιμές ότνα κάνεις insert...

Αντίστοιχα αν τα πεδία ήταν σε άλλο πίνακα θα χρειαζόσουν και ένα trigger για το DELETE...

Έτσι δεν είναι;

# Απ: Using a trigger to perform mass-update-safe, specific-field calculations

Τρίτη, 13 Σεπτεμβρίου 2005 10:46 πμ by cap
Φυσικα. Ο ιδιος trigger όμως μπορεί να χρησιμεύσει και για το insert, και μάλιστα όχι INSTEAD OF, αλλά AFTER. Ο "inserted" virtual table έχει περιεχόμενα και στο insert και μπορει να δημιουργηθεί ένας trigger FOR UPDATE, INSERT που θα εξυπηρετεί και τα δυο operations χωρίς αλλαγή στον κώδικα.

# Απ: Using a trigger to perform mass-update-safe, specific-field calculations

Τρίτη, 13 Σεπτεμβρίου 2005 6:10 μμ by rousso
Ναι περίπου...

Για το UPDATE θέλεις INSTEAD OF trigger για να μην κάνεις update δυο φορές τα ίδια records...

Για το INSERT για να μπορεί να παίξει το JOIN (ό ίδιος κώδικας) πρέπει να το κάνεις μετά το INSERT, διαφορετικά πρέπει να γράψεις αλλιώς το trigger και να το βάλεις INSTED OF INSERT...

anyway...
Λεπτομέρειες...
Το concept το έχεις καλύψει.

rousso

# Απ: Using a trigger to perform mass-update-safe, specific-field calculations

Τρίτη, 13 Σεπτεμβρίου 2005 11:42 μμ by cap
Ναι, οκ.

Στο update κάνω ουσιαστικά δύο updates, ένα το κανονικό και ένα το update του υπολογιζόμενου πεδίου.

Τωρα όμως το instead of update δεν ξέρω πόσο καλά θα έπαιζε με το if update. Anyway, όπως λες και εσύ, λεπτομέρειες σε σχέση με το συγκεκριμένο concept, πάμε σε πολύ fine-grained performance tuning εκεί, το οποίο έχει να κάνει με την ταχύτητα των updates. Αυτό που κοιτούσαμε αρχικά ήταν να βελτιώσουμε την ταχύτητα των SELECTs, πράγμα που επετεύχθη.

Στο insert παίζει και όπως έχει, κάνοντας update εκ των υστέρων βέβαια.

# Απ: Using a trigger to perform mass-update-safe, specific-field calculations

Δευτέρα, 17 Οκτωβρίου 2005 2:28 μμ by KelMan
Περί INNER vs LEFT/RIGHT Joins...

Το πρόβλημα που περιγράφεις φίλε rousso δεν έχει έχει να κάνει με το είδος του join και αυτό γιατί το optimization engine επιλέγει βάσει των statistics τον κατάλληλο join αλγόριθμο (loop, hash ή merge) ώστε να υπάρχει το καλύτερο δυνατό performance. Ουσιαστικά, αυτό το scanάρισμα που λες συμβαίνει μόνο κατά το loop join. Στο hash ή στο merge δεν συμβαίνει και για την ακρίβεια, αυτό είναι κλασικό σενάριο hash join...

Γενικά, είναι καλύτερα να χρησιμοποιούμε τα Left/Right joins, όταν θέλουμε υποχρεωτικά να συμπεριλαμβάνονται οι εγγραφές του left ή του right και null για ό,τι δεν υπάρχει από την άλλη μεριά αλλά αυτό δεν συναπάγεται απαραίτητα ότι το Left/Right join είναι γρηγορότερο από το inner.

# Using a trigger to perform mass-update-safe, specific-field calculatio

Σάββατο, 30 Δεκεμβρίου 2006 4:14 μμ by DotNetKicks.com
You've been kicked (a good thing) - Trackback from DotNetKicks.com

Ποιά είναι η άποψή σας για την παραπάνω δημοσίευση;

(απαιτούμενο) 
απαιτούμενο 
(απαιτούμενο) 
Εισάγετε τον κωδικό:
CAPTCHA Image