欄位合併效能比較：CTE vs FOR XML

前一篇文章介紹用CTE實現SQL一對多關聯欄位合併的方法，找資料期間曾發現另一個替代做法，利用SQL Server的FOR XML PATH('')技巧，可將多筆資料轉成單一字串（參考），看來也相當簡潔。但當下覺得子查詢加FOR XML轉換的做法有效能疑慮，理應不如CTE（事實不然！），加上搞CTE比較有挑戰性，故選了CTE研究嚐鮮。
網友ChoeChin留言提到FOR XML寫法，讓我重新思考：如果要大量重度應用，效能議題不可忽視，CTE效能是否真的比FOR XML方法好？有待實驗證實。經一番測試後，結論是我錯估了CTE與FOR XML兩種做法的效能表現。
原本的測試樣本太小，很難看出效能差異。我改用以下指令生出10萬筆糸統，並將AppSystem的Nam欄位與AppSupport的AppName, Engineer欄位設成Primary Key確保查詢效能。

以Total execution time欄位做為比較標的，CTE＋ROW_NUMBER()法 2.96秒、CTE＋JOIN找結果法7.14秒，而FOR XML PATH法只花了0.59秒，FOR XML PATH+CTE法0.64秒（沒有變快）。

CTE慢了近5倍，顛覆我原本的想像…



原始介紹文章出自於此