SQL Server Url正则表达式 内存常驻 完美解决方案

       在使用SQL Server2005扩展函数进行性能优化已经提到过把SQL Server中的表装载到内存中，通常这样做的目的是让频繁的表查询能通过在内存中查找来优化数据库性能，从而减少表的查询，减少IO方面的消耗。

       这篇文章的目的就是为了解决使用SQL Server2005扩展函数进行性能优化中表记录更新导致函数返回结果有误的问题。产生这个问题的原因是：如果表更新了，而程序已经把整个表加载到内存了，所以会导致调用函数时返回的结果有误。我们需要重新注册函数，如果不重新注册函数，那么就需要数据库重启服务了，因为那个程序集是在服务启动的时候就初始化了。

       上面这个问题的解决方案就是： 在表中创建一个触发器，当有Insert、Update、Delete等操作就调用一个重新注册的存储过程重新注册程序集（注意，Truncate、Drop是无法激发触发器的）。看样子，这个方案很简单，而我写这篇文章的目的是想把其中遇到的问题告诉大家。我想说的是，不要小看一个看似很解决的问题，只要你细心挖掘，你也可以学到很多东西的。

 

下面我就先来列列这些问题：

1.    这个注册函数（更确切的说是注册程序集）的存储过程该如何写？

2.    这个触发器中你是否漏了Truncate这个操作的考虑？它是不会激发触发器的，如何解决？

3.    当批量插入到这个表的时候发现插入的速度奇慢（插入的数据为几百条），这个时候才发现原来这个注册会给插入带来灾难性的性能问题。唯一办法就是先禁用这个触发器，等插入完成后再启动触发器。

4.    在开发环境中的注册可以使用VS的部署来完成，再通过SQL Server的生成脚本来导出程序集做为注册，但是注册代码比较乱，有没更好的办法？

5.    解决了在注册的时候需要读取DLL程序集文件的问题，通过生成十六进制的代码来注册程序集，这样就不用依赖于DLL了，非常适合在生产环境中使用，也适合快速部署，达到尽量解耦的目的。

6.    一个动态扩展函数和一个静态扩展函数（类似于上面的加载表到内存），这两个函数最好不要写在一个类里面，最后是分开到不同的项目中，这样是为了避免重新注册程序集的时候不必要的资源浪费，更重要的是为了一个错误，一个需要初始化Static变量的问题。

7.    默认情况下数据库的CLR是没有开启的，需要手动设置或者使用命令开启。

8.    在为URL设置正则表达式的时候，应该注意需要在URL的前后加限定符（^ 行的开头 $ 行的结尾 ）

 

总结出的一个开发CLR扩展函数的流程、步骤：

1.    在开发阶段，使用VS编写代码并部署测试；

2.    完成测试后，使用Release模式生成DLL，拷贝DLL到相应的服务器，调用模板存储过程，通过读取DLL的方式先注册一遍程序集，这是为了生成的代码比较明朗，不会乱；

3.    使用SQL Server的生成脚本的功能来导出程序集，再把生成的代码拷贝到注册的存储过程中，使之脱离读取DLL的注册方式，这样就可以快速迁移CLR扩展函数了。

 

下面就介绍一下这个解决方案的内容：

1.    最主要的注册函数

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Deploy_ETplugin_SQLRegexUrlImage]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:        <Viajar>
-- Create date: <2010.06.19>
-- Description:    <部署扩展函数>
-- 程序集在：C:\SQLRegexUrlImage.dll
-- =============================================
CREATE PROCEDURE [dbo].[Deploy_ETplugin_SQLRegexUrlImage]
AS
BEGIN
    --判断是否存在名为SQLRegexUrlImage的函数
    IF EXISTS (SELECT name FROM sys.sysobjects WHERE name = ''SQLRegexUrlImage'')
       DROP FUNCTION SQLRegexUrlImage

    --判断是否存在名为SQLRegexUrlImage的程序集
    IF EXISTS (SELECT name FROM sys.assemblies WHERE name = ''SQLRegexUrlImage'')
       DROP ASSEMBLY SQLRegexUrlImage

    --创建注册程序集
    CREATE ASSEMBLY SQLRegexUrlImage FROM ''C:\SQLRegexUrlImage.dll''
    WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS

    --创建函数与程序集的对应
    execute dbo.sp_executesql @statement = N''
    CREATE FUNCTION [dbo].[SQLRegexUrlImage](@urlString [nvarchar](500))
    RETURNS [nvarchar](50)
    AS EXTERNAL NAME [SQLRegexUrlImage].[UserDefinedFunctions].[SQLRegexUrlImage]''

END
SET ANSI_NULLS OFF
' 
END
GO

 

 

2.    使用数据库的生成脚本功能，可以导出程序集的代码，把下面的代码替换上面存储过程中使用路径注册的方法，这样就避免了部署的时候需要拷贝DLL的问题。

CREATE ASSEMBLY [SQLRegexUrlImage]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300F90D1E4C0000000000000000E0000E210B010800000E000000060000000000001E2C0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040050000100000100000000010000010000000000000100000000000000000000000D02B00004B00000000400000C803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240C000000200000000E000000020000000000000000000000000000200000602E72737263000000C8030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000002C0000000000004800000002000500A8210000280A0000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000056730D00000A80010000047E0100000428020000062A00001B3003008F000000010000117201000070730E00000A0A066F0F00000A723100007006731000000A0B076F1100000A0C2B360872C20000706F1200000A6F1300000A0D0872D60000706F1200000A6F1300000A13040211046F1400000A2D09021104096F1500000A086F1600000A2DC2DE0A082C06086F1700000ADCDE0A072C06076F1700000ADC066F1800000ADE0A062C06066F1700000ADC2A0001280000020024004266000A0000000002001D005572000A0000000002000B007984000A000000001B3002005800000002000011140A7E010000046F1A00000A13042B2A11046F1B00000A0B1201281C00000A0C08731D00000A0D09026F1E00000A2C0A1201281F00000A0A2B0911046F2000000A2DCDDE0C11042C0711046F1700000ADC06732100000A2A0110000002000E003745000C000000001E02282200000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000D0020000237E00003C0300002404000023537472696E67730000000060070000E8000000235553004808000010000000234755494400000058080000D001000023426C6F620000000000000002000001571502080900000000FA013300160000010000001C00000002000000010000000400000002000000220000000B0000000200000005000000010000000300000000000A00010000000000060044003D00060066004B000A00BF00AA000600FC00EA0006001301EA0006003001EA0006004F01EA0006006801EA0006008101EA0006009C01EA000600B701EA000600EF01D00106000302EA0006003C021C0206005C021C0206007A024B000A009D0287020A00BE02AB020A00D00287020A00DB0287020A00F702AB0206002B033D000A0060034503060075034B00060083034B00060092034B000E00E103C20306000C04F9030000000001000000000001000100010000001F000000050001000100310074000A00502000000000911888001200010068200000000091008F00160001002C21000000009600C90020000200A021000000008618DA002600030000000100740000000100E0002100DA002A002900DA002A003100DA002A003900DA002A004100DA002A004900DA002A005100DA002A005900DA002A006100DA002F006900DA002A007100DA0034007900DA0026000C00DA0026008900DA002A009100CB0226009900DA0040009900E9024700A90004034C0009000D035100140016035C00140022036200A90026036A00B1003703260091003F032600B900DA0026001C00A00315012400AE0329012C00BA032901D900DA002A00D900E70335012C00EF033A01E10018046A001900DA002A000900DA0026002E002B0056012E006300AF012E0013006C012E001B006C012E00230072012E000B0056012E00330083012E003B006C012E004B006C012E005B00A6016000CB0079006E003F01390055000A011E012E010480000001000000EF0E3C92000000000000C9000000020000000000000000000000010034000000000002000000000000000000000001009E000000000002000000000000000000000001003D00000000000000003C4D6F64756C653E0053514C526567657855726C496D6167652E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E732E47656E65726963004944696374696F6E617279603200726573756C74436F6C6C656374696F6E446963002E6363746F72004765745461626C6546726F6D44420053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053514C526567657855726C496D616765002E63746F720075726C537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650044696374696F6E61727960320053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E640053716C44617461526561646572004578656375746552656164657200446244617461526561646572006765745F4974656D00546F537472696E6700436F6E7461696E734B65790041646400526561640049446973706F7361626C6500446973706F736500436C6F7365004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650049456E756D657261626C656031004B657956616C75655061697260320049456E756D657261746F72603100476574456E756D657261746F72006765745F43757272656E74006765745F4B65790053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780049734D61746368006765745F56616C75650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F72004D6F76654E65787400000000002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D00740072007500650000808F730065006C0065006300740020005B0049006D006100670065004E0061006D0065005D002C005B00550052004C0052006500670078005D002000660072006F006D0020005B00550072006C0049006D0061006700650053006100760065005D0020007700680065007200650020005B00550052004C0052006500670078005D00200021003D002000270027002000011349006D006100670065004E0061006D006500000F550052004C00520065006700780000000024C3137D6DEAE841B1EAE8AD5A5ABA3D0008B77A5C561934E0890706151209020E0E0300000109000101151209020E0E050001110D0E03200001042001010E0420010102042001010806151241020E0E062002010E124504200012510420011C0E0320000E06151209020E0E0520010213000720020113001301032000020A07051245124D12510E0E808F010001005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A44617461416363657373010000000A15126101151165020E0E0820001512690113000A15126901151165020E0E042000130006151165020E0E042001020E04200013011607050E151165020E0E0E126D15126901151165020E0E1501001053514C526567657855726C496D61676500000501000000001001000B626F74776176652E636F6D00002201001D436F7079726967687420C2A920626F74776176652E636F6D203230313000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000F82B000000000000000000000E2C0000002000000000000000000000000000000000000000000000002C00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000700300000000000000000000700334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001003C92EF0E000001003C92EF0E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004D0020000010053007400720069006E006700460069006C00650049006E0066006F000000AC020000010030003000300030003000340062003000000038000C00010043006F006D00700061006E0079004E0061006D0065000000000062006F00740077006100760065002E0063006F006D0000004C0011000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C0052006500670065007800550072006C0049006D006100670065000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003800320033002E0033003700340033003600000000004C001500010049006E007400650072006E0061006C004E0061006D0065000000530051004C0052006500670065007800550072006C0049006D006100670065002E0064006C006C000000000060001D0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200062006F00740077006100760065002E0063006F006D0020003200300031003000000000005400150001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0052006500670065007800550072006C0049006D006100670065002E0064006C006C0000000000440011000100500072006F0064007500630074004E0061006D00650000000000530051004C0052006500670065007800550072006C0049006D006100670065000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003800320033002E00330037003400330036000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003800320033002E0033003700340033003600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

 

 

3.     调用方法和效果图