It's now the time to explore CLR implementation of the Numbers and Split functions in the SQL Server.
I've created a simple C# assembly that defines two table valued functions Numbers_CLR and Split_CLR. Note that I had to fix autogenerated sql function declaration in order to replace nvarchar(4000) with nvarchar(max):
using System;using System.Collections;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Diagnostics;public class UserDefinedFunctions{ [SqlFunction] public static long GetTimestamp() { return Stopwatch.GetTimestamp(); } [SqlFunction] public static long GetFrequency() { return Stopwatch.Frequency; } [SqlFunction( Name="Numbers_CLR", FillRowMethodName = "NumbersFillRow", IsPrecise = true, IsDeterministic = true, DataAccess = DataAccessKind.None, TableDefinition = "value int")] public static IEnumerator NumbersInit(int count) { for (int i = 0; i < count; i++) { yield return i; } } public static void NumbersFillRow(Object obj, out int value) { value = (int)obj; } [SqlFunction( Name = "Split_CLR", FillRowMethodName = "SplitFillRow", IsPrecise = true, IsDeterministic = true, DataAccess = DataAccessKind.None, TableDefinition = "value nvarchar(max)")] public static IEnumerator SplitInit(string value, string splitter) { if (string.IsNullOrEmpty(value)) yield break; if (string.IsNullOrEmpty(splitter)) splitter = ","; for(int i = 0; i < value.Length; ) { int next = value.IndexOf(splitter, i); if (next == -1) { yield return value.Substring(i); break; } else { yield return value.Substring(i, next - i); i = next + splitter.Length; } } } public static void SplitFillRow(Object obj, out string value) { value = (string)obj; }};
These are results of the test of differents variants of the numbers function for different numbers of lines to return (length):
i description length duration msPerNumber ---- -------------- -------- ---------- ----------- 0 Numbers 1 0.0964 0.0964 0 Numbers_CTE 1 0.2319 0.2319 0 Numbers_Table 1 0.1710 0.1710 0 Numbers_CLR 1 0.1729 0.1729 1 Numbers 2 0.0615 0.0307 1 Numbers_CTE 2 0.1327 0.0663 1 Numbers_Table 2 0.0816 0.0408 1 Numbers_CLR 2 0.1078 0.0539 2 Numbers 4 0.0598 0.0149 2 Numbers_CTE 4 0.1609 0.0402 2 Numbers_Table 4 0.0810 0.0203 2 Numbers_CLR 4 0.1092 0.0273 3 Numbers 8 0.0598 0.0075 3 Numbers_CTE 8 0.2308 0.0288 3 Numbers_Table 8 0.0813 0.0102 3 Numbers_CLR 8 0.1129 0.0141 4 Numbers 16 0.0598 0.0037 4 Numbers_CTE 16 0.3724 0.0233 4 Numbers_Table 16 0.0827 0.0052 4 Numbers_CLR 16 0.1198 0.0075 5 Numbers 32 0.0606 0.0019 5 Numbers_CTE 32 0.6473 0.0202 5 Numbers_Table 32 0.0852 0.0027 5 Numbers_CLR 32 0.1347 0.0042 6 Numbers 64 0.0615 0.0010 6 Numbers_CTE 64 1.1926 0.0186 6 Numbers_Table 64 0.0886 0.0014 6 Numbers_CLR 64 0.1648 0.0026 7 Numbers 128 0.0637 0.0005 7 Numbers_CTE 128 2.2886 0.0179 7 Numbers_Table 128 0.0978 0.0008 7 Numbers_CLR 128 0.2204 0.0017 8 Numbers 256 0.0679 0.0003 8 Numbers_CTE 256 4.9774 0.0194 8 Numbers_Table 256 0.1243 0.0005 8 Numbers_CLR 256 0.3486 0.0014 9 Numbers 512 0.0785 0.0002 9 Numbers_CTE 512 8.8983 0.0174 9 Numbers_Table 512 0.1523 0.0003 9 Numbers_CLR 512 0.5635 0.0011 10 Numbers 1024 0.0958 0.0001 10 Numbers_CTE 1024 17.8679 0.0174 10 Numbers_Table 1024 0.2453 0.0002 10 Numbers_CLR 1024 1.0504 0.0010 11 Numbers 2048 0.1324 0.0001 11 Numbers_CTE 2048 35.8185 0.0175 11 Numbers_Table 2048 0.3811 0.0002 11 Numbers_CLR 2048 1.9206 0.0009 12 Numbers 4096 0.1992 0.0000 12 Numbers_CTE 4096 70.9478 0.0173 12 Numbers_Table 4096 0.6772 0.0002 12 Numbers_CLR 4096 3.6921 0.0009 13 Numbers 8192 0.3361 0.0000 13 Numbers_CTE 8192 143.3364 0.0175 13 Numbers_Table 8192 1.2809 0.0002 13 Numbers_CLR 8192 7.3931 0.0009 14 Numbers 16384 0.6099 0.0000 14 Numbers_CTE 16384 286.7471 0.0175 14 Numbers_Table 16384 2.4579 0.0002 14 Numbers_CLR 16384 14.4731 0.0009 15 Numbers 32768 1.1546 0.0000 15 Numbers_CTE 32768 573.6626 0.0175 15 Numbers_Table 32768 4.7919 0.0001 15 Numbers_CLR 32768 29.0313 0.0009 16 Numbers 65536 2.3103 0.0000 16 Numbers_CTE 65536 1144.4052 0.0175 16 Numbers_Table 65536 9.5132 0.0001 16 Numbers_CLR 65536 57.7154 0.0009 17 Numbers 131072 4.4265 0.0000 17 Numbers_CTE 131072 2314.5917 0.0177 17 Numbers_Table 131072 18.9130 0.0001 17 Numbers_CLR 131072 116.4268 0.0009 18 Numbers 262144 8.7860 0.0000 18 Numbers_CTE 262144 4662.7233 0.0178 18 Numbers_Table 262144 38.3024 0.0001 18 Numbers_CLR 262144 230.1522 0.0009 19 Numbers 524288 18.4638 0.0000 19 Numbers_CTE 524288 9182.8146 0.0175 19 Numbers_Table 524288 83.4575 0.0002 19 Numbers_CLR 524288 468.0195 0.0009
These are results of the test of differents variants of the split function for different length of the string (length):
i description strLength duration msPerChar ---- -------------- --------- ---------- ---------- 0 Split 1 0.1442 0.1442 0 Split_CTE 1 0.2665 0.2665 0 Split_Table 1 0.2090 0.2090 0 Split_CLR 1 0.1964 0.1964 1 Split 2 0.0902 0.0451 1 Split_CTE 2 0.1788 0.0894 1 Split_Table 2 0.1087 0.0543 1 Split_CLR 2 0.1056 0.0528 2 Split 4 0.0933 0.0233 2 Split_CTE 4 0.2618 0.0654 2 Split_Table 4 0.1162 0.0291 2 Split_CLR 4 0.1143 0.0286 3 Split 8 0.1092 0.0137 3 Split_CTE 8 0.4408 0.0551 3 Split_Table 8 0.1344 0.0168 3 Split_CLR 8 0.1324 0.0166 4 Split 16 0.1422 0.0089 4 Split_CTE 16 0.7990 0.0499 4 Split_Table 16 0.1715 0.0107 4 Split_CLR 16 0.1687 0.0105 5 Split 32 0.2090 0.0065 5 Split_CTE 32 1.4924 0.0466 5 Split_Table 32 0.2458 0.0077 5 Split_CLR 32 0.4582 0.0143 6 Split 64 0.3464 0.0054 6 Split_CTE 64 2.9129 0.0455 6 Split_Table 64 0.3947 0.0062 6 Split_CLR 64 0.3880 0.0061 7 Split 128 0.6101 0.0048 7 Split_CTE 128 5.7348 0.0448 7 Split_Table 128 0.6898 0.0054 7 Split_CLR 128 0.6825 0.0053 8 Split 256 1.1504 0.0045 8 Split_CTE 256 11.5610 0.0452 8 Split_Table 256 1.3044 0.0051 8 Split_CLR 256 1.2901 0.0050 9 Split 512 2.2430 0.0044 9 Split_CTE 512 23.3854 0.0457 9 Split_Table 512 2.4992 0.0049 9 Split_CLR 512 2.4838 0.0049 10 Split 1024 4.5048 0.0044 10 Split_CTE 1024 45.7030 0.0446 10 Split_Table 1024 4.8886 0.0048 10 Split_CLR 1024 4.8601 0.0047 11 Split 2048 8.8229 0.0043 11 Split_CTE 2048 92.6160 0.0452 11 Split_Table 2048 9.7381 0.0048 11 Split_CLR 2048 9.8848 0.0048 12 Split 4096 17.6285 0.0043 12 Split_CTE 4096 184.3265 0.0450 12 Split_Table 4096 19.4092 0.0047 12 Split_CLR 4096 19.3849 0.0047 13 Split 8192 36.5924 0.0045 13 Split_CTE 8192 393.8663 0.0481 13 Split_Table 8192 39.3296 0.0048 13 Split_CLR 8192 38.9569 0.0048 14 Split 16384 70.7693 0.0043 14 Split_CTE 16384 740.2636 0.0452 14 Split_Table 16384 77.6300 0.0047 14 Split_CLR 16384 77.6878 0.0047 15 Split 32768 141.4202 0.0043 15 Split_CTE 32768 1481.5788 0.0452 15 Split_Table 32768 155.0163 0.0047 15 Split_CLR 32768 155.5904 0.0047 16 Split 65536 282.8597 0.0043 16 Split_CTE 65536 3098.3636 0.0473 16 Split_Table 65536 315.7588 0.0048 16 Split_CLR 65536 316.1782 0.0048 17 Split 131072 574.3652 0.0044 17 Split_CTE 131072 6021.9827 0.0459 17 Split_Table 131072 630.6880 0.0048 17 Split_CLR 131072 650.8676 0.0050 18 Split 262144 5526.9491 0.0211 18 Split_CTE 262144 17645.2219 0.0673 18 Split_Table 262144 5807.3244 0.0222 18 Split_CLR 262144 5759.6946 0.0220 19 Split 524288 11006.3019 0.0210 19 Split_CTE 524288 35093.2482 0.0669 19 Split_Table 524288 11585.3233 0.0221 19 Split_CLR 524288 11550.8323 0.0220
The results are:
The End.