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

来源:asp之家 时间:2010-06-07 11:26:00 

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万条记录。

标签:SQLServer2005,扩展函数,性能优化
0
投稿

猜你喜欢

  • Python实现yaml与json文件批量互转

    2023-11-20 07:07:54
  • MySQL 锁的相关知识总结

    2024-01-13 22:07:12
  • Python使用bar绘制堆积/带误差棒柱形图的实现

    2021-06-26 12:42:05
  • SQL Server数据库的三种创建方法汇总

    2024-01-19 01:20:24
  • Python3安装Pymongo详细步骤

    2021-06-09 10:27:20
  • pandas中对文本类型数据的处理小结

    2022-06-25 06:39:16
  • JavaScript 组件之旅(二):编码实现和算法

    2009-10-09 14:24:00
  • Python可视化最频繁使用的10大工具总结

    2022-07-01 04:00:17
  • Golang实现http文件上传小功能的案例

    2023-07-19 00:55:37
  • Python比较配置文件的方法实例详解

    2021-11-27 05:54:35
  • python的几种矩阵相乘的公式详解

    2021-03-28 21:13:56
  • 企业网站FLASH引导页存在的意义

    2008-06-04 11:09:00
  • ActionScript3.0是革命性的

    2008-05-01 12:36:00
  • python实现Nao机器人的单目测距

    2021-04-09 16:37:10
  • CSS Hack经验总结

    2008-05-01 13:13:00
  • python将一组数分成每3个一组的实例

    2021-11-21 01:40:35
  • UltraEdit编辑器免费激活方法

    2023-09-14 22:19:33
  • 算法系列15天速成 第九天 队列

    2022-06-29 14:16:43
  • 浅谈es6中export和export default的作用及区别

    2024-05-25 15:17:38
  • Django多进程滚动日志问题解决方案

    2023-02-01 16:21:16
  • asp之家 网络编程 m.aspxhome.com