Início / dba / Perguntas / 341466
Accepted
user1664043
Asked: 2024-08-07 01:00:50 +0800 CST

Função com valor de tabela CLR de 1 linha ou tipo definido pelo usuário?

  • 772

Estou trabalhando com um banco de dados de web spidering e estou tentando aproveitar a classe C# Uri via CLR para ajudar na análise de tráfego.

Minha primeira etapa foi criar uma função com valor de tabela CLR (que retorna apenas 1 linha) e CROSS APPLY para dividir os URLs nas partes do componente para revisão, mas estou descobrindo que CROSS APPLY realmente retarda as consultas (como fazer um a consulta com LIKE no banco de dados pode levar de 5 a 8 minutos, mas CROSS APPLY e observar o valor do host leva cerca de 45 minutos)

Eu queria saber se seria mais rápido implementar a interface Uri como um tipo definido pelo usuário e trabalhar isso em minhas consultas? Não fiz muitos tipos definidos pelo usuário, mas pensei que saber que haveria apenas 1 objeto de resposta poderia aliviar parte da sobrecarga no Sql Server. Um UDT teria melhor desempenho em uma consulta?

Minha implementação tvf atualmente se parece com isto:

    [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 respostas

  1. Minha primeira abordagem seria tentar escrever uma solução SQL pura para analisar o nome do host, se possível.

    No entanto, se você tiver que usar a classe DotNet Uri (por exemplo, devido ao seu rigor e robustez na análise de URIs arbitrários), poderá descobrir que, em vez de chamar uma função CLR do SQL para cada linha, é realmente melhor escrever uma função/procedimento armazenado CLR que, quando chamado uma vez para todo o lote (sem argumentos), chama de volta ao banco de dados para obter os dados brutos em massa e, em seguida, os processa em um loop inteiramente dentro da função/procedimento CLR, e finalmente retorna/armazena todo o conjunto de resultados.

    Além disso, eu não descartaria a possibilidade de que a relativa ineficiência e lentidão (em comparação com um filtro LIKE) se devam ao uso da própria classe Uri e ao grande custo de analisar a string bruta em um Uri totalmente estruturado. Você já tentou avaliar a classe Uri de forma independente?

    • 1
  2. Best Answer

    Fui em frente e criei um UDT básico implementando a mesma interface de uma classe e executei alguns testes.

    Acontece que a implementação do UDT é executada cerca de 30-40% mais rápido que a função clr com bastante regularidade.

    Eu os executei com Incluir Plano. Os planos para consultas que fazem a mesma coisa, mas com função versus UDT, têm a função com uma junção aninhada, enquanto o UDT consiste em dois nós Compute Scalar. Os Planos acham que a consulta udt levará 3%, enquanto a função um levará 97%. Teria adorado se fosse esse o caso, mas o CLR é uma caixa preta para o estimador de plano.

        [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

relate perguntas