מתי לא להשתמש - User Defined Functions - UDF
לכל מי שבא מעולם התכנות (כמוני למשל), שימוש בפונקציות נשמע כצורת הכתיבה הטובה ביותר. יש לכך כמובן הרבה סיבות כגון שימוש חוזר, Encapsulation, פשטות קוד ועוד.
אבל, בכתיבה של SQL זה לא תמיד כדאי.
אז הנה דוגמא מהחיים (הדוגמאות רצות על AdventureWorks):
אנו רוצים לכל ספק את סכום ההזמנות, כמות הפריטים, סכום ממוצע לפריט ומשקל ממוצע לפריט
השיטה בעזרת פונקציות:
לעבור על טבלת הספקים, ההזמנות ושורות ההזמנה
קוד בעזרת פונקציות (הגדרות הפונקציות בנספח):
SELECT PV.VendorID, Purchasing.fn_SumQty(PV.VendorID) SumQty, Purchasing.fn_SumValue(PV.VendorID) SumValue ,
Purchasing.fn_SumFreight(PV.VendorID) SumFreight,
Purchasing.fn_SumValue(PV.VendorID) / Purchasing.fn_SumQty(PV.VendorID) AvgQty,
Purchasing.fn_SumFreight(PV.VendorID) / Purchasing.fn_SumQty(PV.VendorID) AvgFreight
FROM Purchasing.ProductVendor PV
הביצועים - ברצפה (11 שניות, 200K Reads)
נעבור לשיטה חלופית (הסבר יבוא):
SELECT PV.VendorID, SumQty, SumValue, SumFreight, SumValue/SumQty AvgQty, SumFreight/SumQty AvgFreight
FROM Purchasing.ProductVendor PV
LEFT OUTER JOIN
(select VendorID,sum(Freight) SumFreight, sum(LineTotal) SumValue, sum(cast(OrderQty as int)) SumQty
from Purchasing.PurchaseOrderHeader H
inner join Purchasing.PurchaseOrderDetail D
on H.PurchaseOrderID = D.PurchaseOrderID
GROUP BY VendorID) HelpSumOrders
on PV.VendorID = HelpSumOrders.VendorID
הביצועים - מצויינים (0.2 שניות, Reads 112) - פי 55 יותר מהר, פחות מאלפית פעולות IO
אז מה הקסם:
- פעולת ה JOIN הישירה מאפשרת לאופטימייזר לקרוא את הטבלאת פעם אחת ולא פעם לכל שורה
- בנוסף, במימוש המקורי יש 5 קריאות לפונקציה לכל שורה!!
מסקנה:
אל תשמשו בפונקציות העושות פניות נוספות ל Database אלא אם מספר השורות נמוך!!
נספח פונקציות:
CREATE FUNCTION Purchasing.fn_SumFreight (@VendorID int) RETURNS money
AS
BEGIN
DECLARE @SumFreight MONEY
select @SumFreight= sum(Freight) from Purchasing.PurchaseOrderHeader
WHERE VendorID = @VendorID
RETURN (@SumFreight)
END
go
CREATE FUNCTION Purchasing.fn_SumValue (@VendorID int) RETURNS money
AS
BEGIN
DECLARE @SumLineTotal MONEY
select @SumLineTotal= sum(LineTotal) from Purchasing.PurchaseOrderDetail D
INNER JOIN Purchasing.PurchaseOrderHeader H ON H.PurchaseOrderID = D.PurchaseOrderID
WHERE VendorID = @VendorID
RETURN (@SumLineTotal)
END
GO
ALTER FUNCTION Purchasing.fn_SumQty (@VendorID int) RETURNS INT
AS
BEGIN
DECLARE @SumQty INT
select @SumQty= sum(cast(OrderQty as int)) from Purchasing.PurchaseOrderDetail D
INNER JOIN Purchasing.PurchaseOrderHeader H ON H.PurchaseOrderID = D.PurchaseOrderID
WHERE VendorID = @VendorID
RETURN (@SumQty)
END