Τα User Defined Functions (UDF) είναι γνωστά στους περισσότερους. Η χρήση τους είναι μεγάλη αλλά αρκετές φορές χρησιμοποιούνται λάθος κυρίως λόγο της άγνοιας που υπάρχει γύρω από την εκτέλεση ενός UDF και ιδιαίτερα όταν αυτό εμπλέκεται μέσα σε ένα query.
Οι περισσότεροι συγκρίνοντας το execution plan ενός query χωρίς UDF και με UDF βλέπουν ότι το query cost είναι μικρότερο σε αυτό που χρησιμοποιεί το UDF και αμέσως θεωρούν ότι αυτό είναι και το καλύτερο.
Για ακόμα μια φορά θα τονίσω ότι δεν υπάρχει έτσι απλά καλό ή καλύτερο όλα είναι εξάρτηση από πολλούς παράγοντες όπως την ποσότητα και ποιότητα των δεδομένων των indexes κλπ.
Όλα αυτά τα χρόνια έχω έρθει αντιμέτωπος με πολλά λάθος queries που περιέχουν UDFs τα οποία είτε είναι κακογραμμένα είτε χρησιμοποιούνται λάθος, και αναφέρομαι κυρίως στα scalar UDFs που οι περισσότεροι τα θεωρούν αθώα και τα χρησιμοποιούν κατά κόρον.
Να τονίσω ότι δεν είμαι αντίθετος στην χρήση τους αλλά επιμένω στην σωστή χρήση τους.
Ας κάνουμε ένα παράδειγμα χρησιμοποιώντας την Northwind database στην οποία θα δημιουργήσω ένα scalar UDF το οποίο επιστρέφει τον τζίρο των παραγγελιών του πελάτη.
create function dbo.fnGetCustomerTurnOver(@custid nchar(5)) returns decimal(12,2)
as
begin
return (select cast ( sum(od.Quantity*od.UnitPrice) as decimal(12,2) )
from [Order Details] as od
inner join Orders as o on od.OrderID=o.OrderID
where o.CustomerID=@custid)
end
Αν τώρα αυτό το χρησιμοποιήσω να εμφανίζεται σαν column στα δεδομένα του πίνακα των πελατών όπως παρακάτω θα έχω μια χρήσιμη πληροφορία
select *,dbo.fnGetCustomerTurnOver(customerid) from Customers
Όμως αυτό το UDF θα εκτελεστεί τόσες φορές όσος είναι ο αριθμός των rows στον πίνακα των πελατών. Αυτό όμως δεν μπορούσαν αρκετοί να το καταλάβουν.
Ένα αρκετά χρήσιμο DMV που προσωπικά πιστεύω ότι θα χαθεί μέσα στα τόσα νέα και σημαντικά χαρακτηριστικά που έχει ο SQL Server 2016 αλλά δεν πρέπει και για αυτό γράφω το άρθρο αυτό.
Ένα DMV που μπορεί να βοηθήσει στο εντοπισμό προβλημάτων απόδοσης σε ένα ή περισσότερα queries και το λέω αυτό καθώς η πληροφορία που δίνει είναι τόσο αναλυτική που εύκολα μπορεί κανείς να εντοπίσει το πρόβλημα. Προσωπικά θα ήθελα κάτι ακόμα αλλά θα το συζητήσω με το product group για το αν είναι εφικτό να γίνει.
Το συγκεκριμένο DMV δίνει aggregate performance statistics για όσο UDFs έχουν χρησιμοποιηθεί και είναι στην cache. Αν αυτά φύγουν από την cache ότι έχει μαζευτεί μηδενίζεται και όταν αυτό χρησιμοποιηθεί ξανά και μπει στην cache τα statistics μαζεύονται ξανά.
Στο σημείο αυτό θα πρέπει να αναφερθεί ότι μόνο για τα scalar , CLR και in-memory UDFs συλλέγονται τα στατιστικά αυτά και όχι για τα table value UDFs.
Στα BOL θα βρείτε αναλυτικά όλες τις πληροφορίες γύρω από αυτό και για αυτό θα εστιάσω στην χρήση του.
Ας υποθέσουμε ότι έχουμε φτιάξει το παραπάνω UDF και το εκτελούμε για πρώτη φορά ως εξής (το αποτέλεσμα μας είναι αδιάφορο αυτή την στιγμή)
select dbo.fnGetCustomerTurnOver('anton')
Αμέσως μετά αν κάνουμε την εκτέλεση αυτού του νέου DMV θα δούμε πληροφορίες που στο παρελθόν δεν είχαμε και με αυτές μπορούμε να καταλάβουμε το τι ακριβώς κάνει.
Μπορείτε είτε να χρησιμοποιήσετε ένα απλό select που δείχνει όλα τα column
SELECT OBJECT_NAME(object_id, database_id) 'function name',*
FROM sys.dm_exec_function_stats
Είτε κάτι ποιο aggregated
SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'function name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_function_stats AS d
ORDER BY [total_worker_time] DESC;
Η εικόνα που θα έχετε στα χέρια σας γύρω από το UDF αυτό είναι πραγματικά αρκετά χρήσιμη και δεν μπορεί να απεικονιστεί στο άρθρο αυτό, αλλά και μόνο που βλέπει κανείς το πόσες φορές έχει εκτελεστεί τους χρόνους εκτέλεσης και τα physical & logical reads που έχουν γίνει είναι κάτι που θα του δώσει την αίσθηση που χρειάζεται για να αναλύσει περισσότερο το πρόβλημα του.
Ξέρω ότι κάποιοι από εσάς πιθανώς να αρχίσουν να πειραματίζονται και να βλέπουν τα στοιχεία που δίνει αυτό το εξαιρετικό, κατά την άποψη μου, DMV. Και επειδή ίσως θα θελήσουν να μηδενίσουν τα statistics για να συλλέξουν ξεχωριστά αν query που περιέχει το UDF τους θα τους δώσω μια μικρή βοήθεια. Η διαγράφη του συγκεκριμένου plan από την cache μπορεί να γίνει με την χρήση της DBCC FREEPROCCACHE και σαν παράμετρο σε αυτή μπορούν να περάσουν την τιμή της plan_handle column
Enjoy!
/*antonch*/