Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a project where I need to look up the hostnames associated with IP addresses who were logged making HTTP requests. The lookups currently happen as part of a daily ETL job. The current method is to use a scalar CLR function (similar code to this is posted a number of places on the web, posted below with my revisions; I'm not sure who the original author was):

using System.Data.SqlTypes;
using System.Net;
using System.Security;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class udfn_GetHostName
{

[Microsoft.SqlServer.Server.SqlFunction]
public static string udfn_GetHostname(string IPAddr)
{
    try
    {
        /* 
         Using deprecated method intentionally.
         GetHostEntry() is now recommended.
         But it does some irritating things like returning an error if a PTR
         record points to a name that doesn't have an A record.
        */
        IPHostEntry IpEntry = Dns.GetHostByAddress(IPAddr);
        // Test whether the record returned has at least one alphabetic character
        // If it does, then it's a name
        // Otherwise the DNS server might have returned the IP address
        Match match = Regex.Match(IpEntry.HostName.ToString(), @"[a-zA-Z]+");

        if (match.Success)
        {
            return IpEntry.HostName.ToString();
        }
        else
        {
            return "None";
        }      

     }
    catch(Exception ex)
    {
        return "Failed";
        //return ex.Message.ToString();
    }
  }
} 

I'm not a C# developer so the quality of the CLR code is likely not great.

Then I call the function like this after loading new rows into the dimension:

-- Update only rows that we just inserted
UPDATE DIM.Network_Addresses
SET reverse_dns = dbo.[udfn_GetHostname](client_ip) 
WHERE reverse_dns IS NULL 
AND is_current = 1
AND created_date = (SELECT MAX(created_date) FROM DIM.API_Network_Address);

This method works but is very slow, for at least a couple reasons.

1) Using a scalar function makes SQL Server call the CLR function once per row that needs to be updated, using a new SQL context.

2) The function calls themselves are very slow due to how GetHostname() and other CLR name resolution functions work: long timeouts, sometimes multiple round trips across the network that all time out if a DNS server doesn't respond or there is no PTR record, etc.

Could any recommend a design pattern that would improve the performance of looking up reverse DNS records and updating the table?

I'm considering a few different things:

1) Move this work outside of the database and use a tool such as dig to do the lookups in parallel.

2) Try to find some way to call the function in parallel or convert it to an in-line function (not making much progress on this!)

However, any ideas would be welcome.

share|improve this question
    
Which version of SQL Server? Do the name resolutions need to be completed before the loading of that dimension is considered complete, or is it okay if that part of it happens in parallel with the rest of the loading? –  Jon Seigel Nov 18 '13 at 14:54
    
This is on SQL Server 2008 R2. We could do the name resolutions before, after, or during loading the dimension if there was a way to make them take less time. –  James Nov 18 '13 at 14:58
    
Do the name resolutions need to complete before the SSIS package completes, or can they continue to be processed after the data loading is done? –  Jon Seigel Nov 18 '13 at 15:17
    
Sorry, I thought I had already answered that. I meant no, they could be done later (as well as before or during). –  James Nov 18 '13 at 15:41
2  
Okay, Remus' answer will work for you. For future readers, if the name resolution had to complete before the SSIS package completed, I would try using multiple Script Components to parallelize the calls within SSIS itself. –  Jon Seigel Nov 18 '13 at 16:04

1 Answer 1

up vote 7 down vote accepted

Do not do it in SQL. From the few things that can be categorized as 'stupid use of SQLCLR', making expensive lengthy network calls ranks as #1. At the very very very very least, make sure the CLR code calls Thread.BeginThreadAffinity() before it goes into waiting for the intertubez to respond (DNS lookup and reverse lookup included).

The proper way to handle this is to use an external process, place the IPs to resolve in a queue, dequeue in batches and resolve several (tens) of IPs in parallel using asynchronous I/O, eg. the non-obsolete Dns.BeginGetHostEntry().

share|improve this answer
    
Thanks, I think I will pursue this approach. –  James Nov 18 '13 at 15:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.