快捷搜索:  as  test  1111  test aNd 8=8  test++aNd+8=8  as++aNd+8=8  as aNd 8=8

澳门威尼人斯人app下载:使用SQL Server2005扩展函数进行性能优化



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+树来查询数据了,直接到内存中查询,这样之以是快是由于操作内存要比操作磁盘要快得多。这此中会有些局限性和毛病,详细见下面的毛病描述。

设计思路

去数据库中把表2读掏出来,并放到private static readonly IDictionary resultCollectionDic的静态变量中。在数据库办事启动的时刻是会初始化SQLCLR函数的,以是在启数据库办事的时刻,也一路把表2的数据保存到了内存傍边了。

上面的查询中包括了两个字段Column1、Column2和一个返回字段Column3,那么我们若何把这些数据保存到IDictionary字典傍边呢?我的做法便是把Column1、Column2的中心加一个字澳门威尼人斯人app下载符“+”,把这个字符串作为Key值,把Column澳门威尼人斯人app下载3这个返回值做为Value,这样就办理了多个And的查询的问题。这个会有些局限性,详细可以见下面的毛病描述。

在函数FunctionImsi2HLR2中传进的两个字符后,就要进行上面的拼凑要领来拼凑Key值,再到IDictionary中查询。

测试结果

测试数据:表2有4.6732万笔记录,表1有54.2524万笔记录。

颠末测试:

优化1措施(零丁索引)的光阴是106秒

优化3措施(包孕性索引)的光阴是45秒

优化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.澳门威尼人斯人app下载Generic;

public partial class UserDefinedFunctions

{

//颠末测试发明:应用Hashtable和SortedList没有应用IDictionary的机能好.

//IDictionary中应用string比SqlString的机能要高.

private static readonly IDiction澳门威尼人斯人app下载arystring, string> resultCollectionDic = new Dictionarystring, string>();

static UserDefinedFunctions()

{

GetTableFromDB(resultCollectionDic);

}

///

/// 从数据库中获取某个表的数据.

///

///

private static void GetTableFromDB(IDictionarystring, 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();

}

}

///

/// 裸露给SQL Server调用的函数.

///

///

参数1

///

参数2

///

[SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlString FunctionImsi2HLR2(string NS, int NP)

{

string result = null;//这里设置为null是为了在措施IMSI2HLR2中判断继承轮回.

string key = NS + "+" + NP.ToString();//应用特殊符澳门威尼人斯人app下载号+连接两个列作为key值.

if (resultCollectionDic.ContainsKey(key))

result = resultCollectionDic[key].ToString();

return new SqlString(result);

}

};

免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。

您可能还会对下面的文章感兴趣: