RSS 2.0
Sign In
# Tuesday, February 27, 2007

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:

  1. Recursive common table expression shows the worst timing.
  2. Split_CLR is on the pair with Split_Table, however Numbers_Table is better than Numbers_CLR.
  3. Split and Numbers based on unrolled recursion show the best timing (most of the time).

The End. :-)

Tuesday, February 27, 2007 1:40:04 PM UTC  #    Comments [0] -
SQL Server puzzle
Comments are closed.
Archive
<February 2007>
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
25262728123
45678910
Statistics
Total Posts: 365
This Year: 1
This Month: 0
This Week: 0
Comments: 221
Locations of visitors to this page
Disclaimer
The opinions expressed herein are our own personal opinions and do not represent our employer's view in anyway.

© 2019, Nesterovsky bros
All Content © 2019, Nesterovsky bros
DasBlog theme 'Business' created by Christoph De Baene (delarou)