主页 / dba / 问题 / 341466
Accepted
user1664043
Asked: 2024-08-07 01:00:50 +0800 CST

CLR 1 行表值函数还是用户定义类型？

  • 772

我正在使用网络蜘蛛数据的数据库，并尝试通过 CLR 利用 C# Uri 类来帮助进行流量分析。

我的第一步是创建一个 CLR 表值函数（仅返回 1 行）并 CROSS APPLY 将 URL 分解为组件部分以供审查，但我发现添加 CROSS APPLY 确实会减慢查询速度（例如，对数据库使用 LIKE 进行查询可能需要 5-8 分钟，但 CROSS APPLY 和查看主机值需要 45 分钟）

我想知道将 Uri 接口实现为用户定义类型并将其用于查询是否会更快？我没有做过很多用户定义类型，但我认为知道只有一个响应对象可能会减轻 Sql Server 中的一些开销。UDT 在查询中的表现会更好吗？

我的 tvf 实现目前如下所示：

    [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;
    }
sql-server

2 个回答

  1. 如果可能的话，我的第一种方法是尝试编写一个纯 SQL 解决方案来解析主机名。

    但是，如果您必须使用 DotNet Uri 类（例如，因为它在解析任意 URI 时非常全面和稳健），那么您可能会发现，与其为每一行从 SQL 调用一个 CLR 函数，不如编写一个 CLR 函数/存储过程，当对整个批次调用一次（没有参数）时，回调数据库以批量获取原始数据，然后在 CLR 函数/过程中完全循环处理它，最后返回/存储整个结果集。

    此外，我不会排除这样一种可能性，即相对低效和缓慢（与 LIKE 过滤器相比）是由于使用 Uri 类本身以及将原始字符串解析为完全结构化的 Uri 的巨大成本造成的。您是否尝试过独立对 Uri 类进行基准测试？

    • 1
  2. Best Answer

    我继续努力，构建了一个基本的 UDT，实现与类相同的接口，并运行了一些测试。

    事实证明，UDT 实现的运行速度比 clr 函数快 30-40%。

    我使用 Include Plan 运行了它们。执行相同操作但使用函数而非 UDT 的查询的计划具有嵌套连接的函数，而 UDT 是两个计算标量节点。这些计划认为 udt 查询将占用 3%，而函数查询将占用 97%。如果真是这样，我会很高兴，但 CLR 对计划估算器来说是一个黑匣子。

        [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; }
}
    • 0

相关问题