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

经过测试：

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：可采用改进的哈夫曼树。如何做这方面的方案呢？
 

总结

虽然这样的方式比较难在现实的运用中被使用，因为有很多局限性和缺点，但是我写这篇文章的初衷就是想让大家知道在特殊的情况下，还有这样一种优化的方法可以使用。

原文地址：http://www.cnblogs.com/gaizai/archive/2010/05/26/1740520.html