Sample Header Ad - 728x90

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

0 votes
2 answers
145 views
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; }
Asked by user1664043 (379 rep)
Aug 6, 2024, 05:00 PM
Last activity: Aug 8, 2024, 07:46 PM