Eugene Elutin (10/26/2012)
...
they are all really close over 1 million records
...
If you want your CLR with Regex to perform well you need to declare your Regex object as static and use Compile option for the pattern. Try changing your CLR to this:
public partial class UserDefinedFunctions{
static readonly Regex _regex = new Regex(@"^[^\.]+\.[^\.]+$", RegexOptions.Compiled);
[Microsoft.SqlServer.Server.SqlFunction()]
public static bool RegexCLR(string input)
{
return Regex.IsMatch(input);
}
};
Thanks for the tip on the regex im new to C# but chose to learn that language specifically for CLR's. after the code change and rerunning the tests this is what i got with NTEXT:
Duration for CHARINDEX = 00:00:14:957
Duration for LEN = 00:00:15:723
Duration for CHARINDEX with COLLATE = 00:00:09:790
Duration for PARSENAME = 00:00:10:717
Duration for LIKE = 00:00:11:087
Duration for LIKE with COLLATE = 00:00:10:683
Duration for SQLCLR = 00:00:11:427
and now for NVARCHAR(MAX)
Duration for CHARINDEX = 00:00:06:253
Duration for LEN = 00:00:03:200
Duration for CHARINDEX with COLLATE = 00:00:01:033
Duration for PARSENAME = 00:00:01:327
Duration for LIKE = 00:00:07:237
Duration for LIKE with COLLATE = 00:00:01:457
Duration for SQLCLR = 00:00:01:953
Im actually supprised that by changing the datatype we can chop a factor of 10 off the execution times. never really saw the direct impact of data types like this before.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]