使用SQL Server2005扩展函数进行性能优化

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);
}
};