使用遞歸遍歷樹層次結構的標量 UDF 的更快替代方案？

我有一個標量函式，它遍歷客戶的簡單父子層次結構，以找到負責計費的祖先。這是模式的簡化版本。

CREATE TABLE Customer (CustomerID int, ParentCustomerID int, IsBillToCustomer bit)

UDF 沿著鏈向上走，直到到達最高父級，記錄鏈中每個客戶的 IsBillToCustomer 設置，然後返回鏈中最高客戶的 CustomerID，IsBillToCustomer = 1。UDF 對每個客戶來說都很快，但我需要執行報告以返回數千名客戶及其計費主數據相關的數據，而這麼多客戶需要數十分鐘。我是實現 SQL Server 的 hierarchyID 數據類型的唯一選擇嗎？這甚至會有幫助嗎？

創建一個儲存我需要的數據的 SQL 作業不是一個可行的解決方案，因為數據需要盡可能是最新的，而且我不能一遍又一遍地執行 40 分鐘的 CPU 密集型作業。

更新：我使用 HierarchyID 創建了一個臨時表並設置了一些索引，然後在我的查詢中使用了 IsDescendantOf，它仍然花費了 13 多分鐘（並且敲打了我的 CPU）。

讓我們從這個開始。讓我們知道它是否看起來像這樣。請根據需要進行編輯。我正在將數據添加到表中：

INSERT INTO Customer (CustomerID, ParentCustomerID, IsBillToCustomer)
select  1   ,   0   ,   0   union all
select  2   ,   0   ,   0   union all
select  3   ,   0   ,   0       union all
select  4   ,   1   ,   0   union all
select  5   ,   2   ,   0   union all
select  6   ,   3   ,   1   union all
select  7   ,   1   ,   1   union all
select  8   ,   2   ,   1   union all
select  9   ,   3   ,   1   union all
select  10  ,   1   ,   0   union all
select  11  ,   2   ,   0   union all
select  12  ,   3   ,   0   union all
select  13  ,   1   ,   0   union all
select  14  ,   2   ,   0   union all
select  15  ,   3   ,   1   union all
select  16  ,   1   ,   1   union all
select  17  ,   2   ,   1   union all
select  18  ,   3   ,   1   union all
select  19  ,   1   ,   0   union all
select  20  ,   2   ,   1   union all
select  21  ,   3   ,   1   union all
select  22  ,   1   ,   1   union all
select  23  ,   2   ,   0   union all
select  24  ,   3   ,   0   union all
select  25  ,   1   ,   1   union all
select  26  ,   2   ,   1   union all
select  27  ,   3   ,   1   union all
select  28  ,   1   ,   1   union all
select  29  ,   2   ,   0   union all
select  30  ,   3   ,   0   union all
select  31  ,   1   ,   0   union all
select  32  ,   2   ,   0   union all
select  33  ,   3   ,   0   union all
select  34  ,   1   ,   1   union all
select  35  ,   2   ,   1   union all
select  36  ,   3   ,   1   union all
select  37  ,   1   ,   1   union all
select  38  ,   2   ,   0   union all
select  39  ,   3   ,   1   union all
select  40  ,   1   ,   1   union all
select  41  ,   2   ,   1   union all
select  42  ,   3   ,   0   union all
select  43  ,   1   ,   0   union all
select  44  ,   2   ,   1   union all
select  45  ,   3   ,   1   union all
select  46  ,   1   ,   1   union all
select  47  ,   2   ,   1   union all
select  48  ,   3   ,   0   union all
select  49  ,   1   ,   0   union all
select  50  ,   2   ,   0   union all
select  51  ,   3   ,   0   union all
select  52  ,   1   ,   0   union all
select  53  ,   2   ,   1   union all
select  54  ,   3   ,   1   union all
select  55  ,   1   ,   1   union all
select  56  ,   2   ,   1   union all
select  57  ,   3   ,   0   union all
select  58  ,   1   ,   1   union all
select  59  ,   2   ,   1   union all
select  60  ,   3   ,   1   union all
select  61  ,   1   ,   0   union all
select  62  ,   2   ,   0   union all
select  63  ,   3   ,   1   union all
select  64  ,   1   ,   1   union all
select  65  ,   2   ,   1   union all
select  66  ,   3   ,   1   union all
select  67  ,   1   ,   0   union all
select  68  ,   2   ,   0   union all
select  69  ,   3   ,   0   union all
select  70  ,   1   ,   0   union all
select  71  ,   2   ,   0   union all
select  72  ,   3   ,   1   union all
select  73  ,   1   ,   1   union all
select  74  ,   2   ,   1   union all
select  75  ,   3   ,   1   union all
select  76  ,   1   ,   0   union all
select  77  ,   2   ,   1   union all
select  78  ,   3   ,   1   union all
select  79  ,   1   ,   1   union all
select  80  ,   2   ,   0   union all
select  81  ,   3   ,   0   union all
select  82  ,   1   ,   1   union all
select  83  ,   2   ,   1   union all
select  84  ,   3   ,   1   union all
select  85  ,   1   ,   1   union all
select  86  ,   2   ,   0   union all
select  87  ,   3   ,   0   union all
select  88  ,   1   ,   0   union all
select  89  ,   2   ,   0   union all
select  90  ,   3   ,   0   union all
select  91  ,   1   ,   1   union all
select  92  ,   2   ,   1   union all
select  93  ,   3   ,   1   union all
select  94  ,   1   ,   1   union all
select  95  ,   2   ,   0   union all
select  96  ,   3   ,   1   union all
select  97  ,   1   ,   1   union all
select  98  ,   2   ,   1   union all
select  99  ,   3   ,   0   union all
select  100 ,   1   ,   0   union all
select  101 ,   2   ,   1   union all
select  102 ,   3   ,   1   union all
select  103 ,   1   ,   1

所以，讓我們用我們擁有的數據執行一個簡單的 CTE。然後從這裡向我們展示我們在哪里以及我們正在努力實現的目標：

;With Parent(CustomerID, ParentCustomerID, IsBillToCustomer)
As
(
  Select c.CustomerID, c.ParentCustomerID, c.IsBillToCustomer
   from Customer c
   WHERE c.ParentCustomerID = 0
   UNION ALL
 Select c.CustomerID, c.ParentCustomerID, c.IsBillToCustomer
   from Customer c
   inner join Customer p on p.CustomerID=c.ParentCustomerID
)

Select *
 from Parent p

幫助我們了解問題。謝謝。

我不確定葉節點的“鏈中最高客戶”是否意味著最接近葉或最接近根。如果您的意思是“最接近葉子”，您可能希望進行非規範化並使用 FK 確保您的非規範化數據始終正確：

CREATE TABLE Customer (CustomerID int, ParentCustomerID int, IsBillToCustomer bit,
BillToCustomerID INT NOT NULL, 
FOREIGN KEY(ParentCustomerID, BillToCustomerID) 
REFERENCES Customer(CustomerId, BillToCustomerID),
CHECK((IsBillToCustomer = 1 and BillToCustomerID = CustomerId)
OR (IsBillToCustomer = 0 AND BillToCustomerID <> CustomerID ))

如果你像這樣去規範化，你根本不需要遍歷你的樹。請注意，我添加了兩個約束，一個外鍵和一個檢查，以確保 BillToCustomerID 始終正確另一種方法是使用物化路徑。如果您有興趣，我可以明天發布。

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