CLR 1-row table valued function or user defined type?

Question

I'm working with a database of web spidering data and I'm trying to leverage the C# Uri class via CLR to help with traffic analysis.

My first pass was to create a CLR table valued function (that returns only 1 row) and CROSS APPLY to break up the urls into the component parts for review, but I'm finding adding that CROSS APPLY really slows down queries (like doing a query with LIKE over the database can take 5-8 minutes, but CROSS APPLY and looking at the host value takes 45 minutes kind of thing)

I was wondering if it might be faster to implement the Uri interface as a user defined type and work that into my queries instead? I haven't done a lot of user defined types, but I thought knowing there would only ever be 1 response object might lighten some of the overhead in Sql Server. Would a UDT perform better in a query?

My tvf implementation currently looks like this:

    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true, Name = "ufn_UrlParts", SystemDataAccess = SystemDataAccessKind.None, FillRowMethodName = "GetUrlParts")]
    public static IEnumerable UrlParts(SqlString input)
    {
        if (!input.IsNull && Uri.TryCreate(input.Value, UriKind.Absolute, out Uri url) && url.Valid(false))
            yield return url;
        yield break;
    }

    private static void GetUrlParts(object input, out string scheme, out string userinfo, out string host, out int hostType, out int port, out bool isdefaultPort, out string path, out string query)
    {
        Uri u = input as Uri;
        scheme = u?.Scheme;
        userinfo = u?.UserInfo;
        host = u?.Host;
        hostType = (int)(u?.HostNameType ?? UriHostNameType.Unknown);
        port = u?.Port ?? 0;
        isdefaultPort = u?.IsDefaultPort ?? false;
        path = u?.AbsolutePath;
        query = u?.Query;
    }

Answer 1

My first approach would be to try and write a pure SQL solution to parse out the host name, if at all possible.

However if you have to use the DotNet Uri class (for example because of the thoroughness and robustness of it in parsing arbitrary URIs), then you might find that rather than calling into a CLR function from SQL for each row, it's actually better to write a CLR function/stored procedure which, when called once for the entire batch (with no arguments), calls back into the database to get the raw data in bulk, and then processes it in a loop entirely within the CLR function/procedure, and finally returns/stores the entire result set.

Also, I wouldn't discount the possibility that the relative inefficiency and slowness (compared to a LIKE filter) is due to the use of the Uri class itself and the sheer cost of parsing the raw string into a fully structured Uri. Have you tried benchmarking the Uri class independently?

Answer 2

I went ahead and knocked together a bare-bones UDT implementing the same interface as a class and ran some tests.

Turns out the UDT implementation runs about 30-40% faster than the clr function pretty regularly.

I ran them with Include Plan. The plans for queries that do the same thing but with function vs UDT have the function with a nested join while the UDT is two Compute Scalar nodes. The Plans think the udt query will take 3% while the function one will take 97%. Would have loved it if that were the case, but the CLR is a black box to the plan estimator.

    [Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = false, MaxByteSize = -1)]
public struct Url : INullable, IBinarySerialize
{
    private static readonly Url _null = new Url { is_Null = true };
    private bool is_Null;
    private Uri _url;

    public bool IsNull => is_Null;

    public static Url Null
    {
        get => _null;
    }

    public override string ToString()
    {   // Since InvokeIfReceiverIsNull defaults to 'true' this test is unnecessary if Url is only being called from SQL.  
        if (this.IsNull)
            return "NULL";
        return _url.ToString();
    }

    [SqlMethod(OnNullCall = false)]
    public static Url Parse(SqlString input)
    {
        if (!input.IsNull && Uri.TryCreate(input.Value, UriKind.Absolute, out Uri url) && url.Valid(false))
            return new Url { _url = url };
        return _null;
    }

    public void Read(BinaryReader r)
    {
        string u = r.ReadString();
        if (!String.IsNullOrEmpty(u) && Uri.TryCreate(u, UriKind.Absolute, out Uri url) && url.Valid(false))
            _url = url;
        else
            is_Null = true;
    }

    public void Write(BinaryWriter w)
    {
        w.Write(!is_Null ? _url.ToString() : "");
    }

    public string Scheme { get => this.is_Null ? null : this._url.Scheme; }

    public string UserInfo { get => this.is_Null ? null : this._url.UserInfo; }

    public string Host { get => this.is_Null ? null : this._url.Host; }

    public int? HostType { get => this.is_Null ? (int?)null : (int)this._url.HostNameType; }

    public int? Port { get => this.is_Null ? (int?)null : this._url.Port; }

    public bool? IsDefaultPort { get => this.is_Null ? (bool?)null : this._url.IsDefaultPort; }

    public string Path { get => this.is_Null ? null : this._url.AbsolutePath; }

    public string Query { get => this.is_Null ? null : this._url.Query; }
}