SQL Server2005擴展函數已經不是一件什麼新鮮的事了，但是我看網上的大部分都是說聚合函數，例子也比較淺，那麼這裡就講講我運用擴展函數來優化數據庫性能的例子，希望和大家一起分享這個經驗。如果你還不知道什麼是SQLCLR，那麼你可以參考：SQL Server擴展函數的基本概念。
需求說明
大家在使用SQL Server開發的時候一定會遇到這樣的需求，那就是通過Table_Name1表的兩個字段Column1、Column2來查詢在Table_Name2表中符合這兩個條件的記錄，並返回Table_Name2中的字段Column3，面對這樣的需求，你也許會說使用表連接就可以了，對的，沒錯，我也是這樣想的，但是有的時候往往要面對不同的突發情況，那就是並不是一定會Column1與Column2是全匹配的查詢，可能中間還需要一些邏輯的處理，比如字符串的截取後再匹配等等。
這個時候我們通常會在SQL Server中寫一個函數，這個函數接收兩個參數：Column1、Column2，函數體裡面做一些邏輯處理，在通過處理好的參數去查詢Table_Name2表，並返回相應的值。很好，那下面我們來計算下圖中數據的查詢情況。假設表1的數據有50W，表2的數據有4W，在表2沒有索引的條件下，查詢的復雜度就有50W*4W了，兩個表都需要做全表掃描，表2的全表掃描就會達到50W次。
（圖1：需求說明）
優化1：這一個優化，每個開發人員都知道，那就是對表2的兩個查詢字段分別建立索引。這樣的優化和之前相比，性能將會提高N個等級。
優化2：這第二個優化方法是使用SQL Server的復合索引，在表2上創建一個復合索引，這個符合索引包括需要查詢的兩個字段，其實就是把兩個字段的內容生成一個索引，其中索引包含了兩個索引的排序。
優化3：這第三個優化方法是使用SQL Server2005之後版本才有的索引-包含性索引（Include），就是在優化2的基礎上，把需要返回的字段也一起放入到索引中，這樣的查詢就只需要查詢索引就夠了，不需要再讀取數據頁了，減少磁盤的IO消耗。不過這個方法也不是萬能，因為有時可能返回的字段會比較多，有時幾個字段加起來的長度有可能超出了900個字符（索引大小范圍），如果想了解可以進入：SQL Server 索引中include的魅力（具有包含性列的索引）
優化4：在不考慮一些分區、分表、分到不同的磁盤等優化方式的情況下，我們是否還能進一步優化我們的查詢呢？這就是這篇文章想要告訴你的，因為我們的回答是：有的。那就是通過SQLCLR的UDT，把表2的數據一次性加載到內存，那麼在進行表1查詢的時候，我們不需要通過B+樹來查詢數據了，直接到內存中查詢，這樣之所以快是因為操作內存要比操作磁盤要快得多。這其中會有些局限性和缺點，具體見下面的缺點描述。
設計思路
1、去數據庫中把表2讀取出來，並放到private static readonly IDictionary<string, string> resultCollectionDic的靜態變量中。在數據庫服務啟動的時候是會初始化2、SQLCLR函數的，所以在啟數據庫服務的時候，也一起把表2的數據保存到了內存當中了。
3、上面的查詢中包括了兩個字段Column1、Column2和一個返回字段Column3，那麼我們如何把這些數據保存到IDictionary字典當中呢？我的做法就是把Column1、Column2的中間加一個字符“+”，把這個字符串作為Key值，把Column3這個返回值做為Value，這樣就解決了多個And的查詢的問題。這個會有些局限性，具體可以見下面的缺點描述。
在函數FunctionImsi2HLR2中傳進的兩個字符後，就要進行上面的拼湊方式來拼湊Key值，再到IDictionary中查詢。
測試結果
測試數據：表2有4.6732萬條記錄，表1有54.2524萬條記錄。
經過測試：
1、優化1方法（單獨索引）的時間是106秒
2、優化3方法（包含性索引）的時間是45秒
3、優化4方法（擴展函數）的時間是33秒
代碼
以下為引用的內容：using System;
using System.Data;
using System.Data.SqlClIEnt;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
//經過測試發現：使用Hashtable和SortedList沒有使用IDictionary的性能好.
//IDictionary<string, string>中使用string比SqlString的性能要高.
private static readonly IDictionary<string, string> resultCollectionDic = new Dictionary<string, string>();
static UserDefinedFunctions()
{
GetTableFromDB(resultCollectionDic);
}
/// <summary>
/// 從數據庫中獲取某個表的數據.
/// </summary>
/// <param name="resultCollection"></param>
private static void GetTableFromDB(IDictionary<string, string> resultCollectionDic)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
using (SqlCommand selectMGT = new SqlCommand("SELECT NS,NP,HLR FROM dbo.zh_mgt ORDER BY NS,NP", connection))
{
using (SqlDataReader zhmgtReader = selectMGT.ExecuteReader())
{
while (zhmgtReader.Read())
{
string NS = zhmgtReader["NS"].ToString();
string NP = zhmgtReader["NP"].ToString();
string HLR = zhmgtReader["HLR"].ToString();
string key = NS + "+" + NP;
if (!resultCollectionDic.ContainsKey(key))
{
resultCollectionDic.Add(key, HLR);
}
}
}
}
connection.Close();
}
}
/// <summary>
/// 暴露給SQL Server調用的函數.
/// </summary>
/// <param name="NS">參數1</param>
/// <param name="NP">參數2</param>
/// <returns></returns>
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString FunctionImsi2HLR2(string NS, int NP)
{
string result = null;//這裡設置為null是為了在方法IMSI2HLR2中判斷繼續循環.
string key = NS + "+" + NP.ToString();//使用特殊符號+連接兩個列作為key值.
if (resultCollectionDic.ContainsKey(key))
result = resultCollectionDic[key].ToString();
return new SqlString(result);
}
};
調用方式對比
以下為引用的內容：
--1：這個是在NP和NS字段中分別建立索引
SELECT @rc=HLR FROM zh_mgt WHERE NP=7 and NS=@mgt
--2：這個是在NP、NS、HLR字段中建立了一個包含性索引(Include)
SELECT @rc=HLR FROM zh_mgt WHERE NS=@mgt and NP=7
--3：這是使用SQLCLR擴展函數的調用方法
SELECT @rc= dbo.FunctionImsi2HLR2(@mgt,7)
優點
1、性能上的比較（這裡的>是表示時間的長短，時間越小，性能越優）：每個列有單獨的索引>使用Include的包含索引>擴展函數
把表裡面的記錄放到內存上，直接去內存上查詢，不需要使用到B+樹來查詢數據。當你的內存足夠大或者空閒，並且使用到這個表的次數很多，而且更新不頻繁，那就可以考慮這樣的優化方案。
2、如果需要面對一些比較復雜的邏輯處理，也許SQL是沒有辦法做到，即使做到了，那麼SQL代碼的閱讀和維護會比較困難，其實這個既是優點又是缺點，下面的缺點中有提到。
封裝代碼，加強代碼安全。
缺點
1、有一定的局限性，當有多個AND條件一起查詢或者幾個鍵通過上面的方法加起來的字符串不唯一，那麼就沒有辦法像上面IDictionary<string, string>的方法來使用key了，但是也不是沒有辦法的，其實辦法就是IList，把唯一的值作為key，再構造一個實體作為key的value。
2、如果表更新了，需要重新注冊函數，因為程序已經把整個表加載到內存了；如果不重新注冊函數，那麼就需要數據庫重啟服務了，因為那個程序集是在服務啟動的時候就初始化了。
3、針對上面第二個缺點，也是有辦法解決的，那就是在表中做一個觸發器，當有Insert、Update、Delete等操作就調用一個重新注冊的存儲過程就可以了。
4、如果裡面的邏輯處理比較復雜，那麼更新邏輯所帶來的部署、維護成本比較大，因為如果是寫成函數或者是建立包含性索引可能會更好維護。
疑問
1、在SQL Server中，對一個包含性索引的疑問：比如有一個int類型的字段和一個nvarchar的字段，int字段的重復率比較大，而nvarchar的重復率比較少，我之前是根據重復率來確認誰放前面的，但是int與nvarchar的匹配效率是不一樣的，int只要匹配一次，而nvarchar需要匹配跟字符串長度一樣多的次數，那麼應該如何把誰放到前面呢？
2、數據庫中可以把90%的查詢都歸結為1：完全匹配，2：前綴匹配。對應解決方案是：1：可采用bloom-filter擴展函數進行高速匹配，2：可采用改進的哈夫曼樹。如何做這方面的方案呢？
總結
雖然這樣的方式比較難在現實的運用中被使用，因為有很多局限性和缺點，但是我寫這篇文章的初衷就是想讓大家知道在特殊的情況下，還有這樣一種優化的方法可以使用。
