使用者定義函式性能劣勢

我有一個包含大量 UDF 的數據庫，這些 UDF 由涉及大量數據操作和計算的長期執行過程呼叫。

我使用 UDF 的想法是從底層表中分離出資訊的邏輯單元。例如，如果我想獲取有關汽車的資訊，我可能有幾個表格，例如顏色、型號、年份等，每次我都必須加入這些表格才能獲得汽車。相反，我將使用 fnCar() 之類的函式來獲取數據的非規範化視圖。

在我長期執行的過程中，我經常呼叫這些函式，我想知道如果我有一個非規範化的工作表、視圖或臨時表來進行數據操作和計算是否會更好。一般來說，我應該在性能方面注意使用 UDF 是否有一些缺點？

例如，我使用 UDF 進行了一些計算。然後我取消透視該數據並儲存在一個表中。每當我需要再次使用該數據時，我都會呼叫 UDF 將數據轉回。我們這樣做的原因是為了保持我們的計算靈活。如果我們添加/刪除/更改計算，我們不想更改數據模型。

--Calculate some values in a function

declare @location table
(
   id int,
   lattitude float,
   longitude float
)

insert into @location select  1, 40.7, 74
insert into @location select  2, 42, 73
insert into @location select  3, 61, 149
insert into @location select  4, 41, 87


declare @myLattitude float
declare @myLongitude float
set @myLattitude =43
set @myLongitude = 116

declare @distance table
(
   id int,
   distance float
)

insert into @distance
select id, sqrt(power(lattitude-@mylattitude,2)+power(longitude-@mylongitude,2))
from @location



--Store unpivoted data in a table
declare @unpivot table
(
   id int,
   attribute varchar(100),
   attributeValue float
)

insert into @unpivot
(
   id,
   attribute,
   attributeValue
)
select id
   ,attribute
   ,attributevalue 
from
(
   select 
       L.id,
       L.Lattitude, 
       L.Longitude,
       D.Distance
   from @location L 
       inner join @distance D 
       on L.id=D.id
) a
unpivot 
(
   attributeValue for attribute in
   (lattitude, longitude, distance)
) x

--retrive data from store via pivoting function for reporting

select * 
from @unpivot
pivot 
(
   max(attributeValue) for Attribute in (lattitude, longitude, distance)

) x

有時您必須決定什麼對您更重要，是程式碼的維護還是程式碼的執行速度？它執行緩慢的原因是因為 UDF 是逐行處理的 - SQL Server 使用基於集合的操作執行得最好。沒有理由不能保留 UDF，因為有時使用它們比擴展查詢更實用。

我的建議是：如果您的數據集很小，請隨意使用 UDF；但是，如果您使用的是大型數據集，那麼請花時間編寫、測試和優化查詢以獲得最佳結果 - 從長遠來看，當您沒有使用者抱怨他們的系統執行緩慢時，這將使您受益。

我同意將這些屬性記錄到非規範化表中而不是呼叫函式和取消透視結果等的想法。如果您的數據量非常低，使用函式不是問題，但在大多數業務中，數據量會逐漸增長（有時快速地）。因此，您最終會看到查詢執行速度越來越慢。請查看下面的連結，該連結告訴您有關 Functions 的劣勢性能。

https://sqlserverfast.com/?s=user+defined+ugly

如果您仍然希望保持邏輯包裝而不是儲存在非規範化表中，更好的方法是使用視圖，您可以通過其中的任何可用列更輕鬆地過濾這些視圖。

引用自：https://dba.stackexchange.com/questions/22458