创建更快的正则表达式clr函数

I am trying to improve the Clr function in the link http://msdn.microsoft.com/en-us/magazine/cc163473.aspx .

我试图改善链接http://msdn.microsoft.com/en-us/magazine/cc163473.aspx中的Clr功能。

public static partial class UserDefinedFunctions 
{
    public static readonly RegexOptions Options =
        RegexOptions.IgnorePatternWhitespace |
        RegexOptions.Singleline;

    [SqlFunction]
    public static SqlBoolean RegexMatch(
        SqlChars input, SqlString pattern)
    {
        Regex regex = new Regex( pattern.Value, Options );
        return regex.IsMatch( new string( input.Value ) );
    }
}

When execute select * from Table1 where dbo.RegexMatch(col1, 'pattern') = 1, the Clr function create a new Regex object for each row in the table.

当执行select * from Table1,其中dbo.RegexMatch(col1,'pattern')= 1时,Clr函数为表中的每一行创建一个新的Regex对象。

Is it possible to create only one Regex object for each Sql statement? For each row just call regex.Ismatch(...). Is the following code valid?

是否可以为每个Sql语句只创建一个Regex对象?对于每一行,只需调用regex.Ismatch(...)。以下代码是否有效?

public static partial class UserDefinedFunctions 
{
    public static readonly RegexOptions Options =
        RegexOptions.IgnorePatternWhitespace |
        RegexOptions.Singleline;

    static Regex regex = null;

    [SqlFunction]
    public static SqlBoolean RegexMatch(
        SqlChars input, SqlString pattern)
    {
        if (regex == null) 
            regex = new Regex( pattern.Value, Options );
        return regex.IsMatch( new string( input.Value ) );
    }
}

1 个解决方案

#1

Your UDF based instance of your static regex is probably not even re-used, Your better of calling the static version of

您的静态正则表达式的基于UDF的实例可能甚至没有被重用。您最好调用静态版本的

System.Text.RegularExpressions.RegEx.IsMatch(string input, string pattern, RegexOptions options);

System.Text.RegularExpressions.RegEx.IsMatch(字符串输入,字符串模式,RegexOptions选项);

directly.

Please note that in debug mode things work different than that what they do in production and that SQL is going to free memory when it needs to.

请注意,在调试模式下,它们的工作方式与它们在生产中的工作方式不同,并且SQL会在需要时释放内存。

Also, try using RegexOptions.Compiled and CultureInvariant.

另外,请尝试使用RegexOptions.Compiled和CultureInvariant。

#1