Question: Is there a better indexing strategy or query SELECT that I can use for looking up one large data set against another large data set? Or, should I look at placing the lookup dimension table in memory (all 125 GB of it)?

I have two data sets: IIS Exchange Server IP log records and IP GeoLocation data provided by a 3rd party vendor; where the Geolocation covers a range of IP addresses. I need to lookup the IP address from the log file and get its GeoLocation. Both data sets accommodate for IPv4 and IPv6 and the IP address is received in string format. When I load the data, I convert the IP address into a hexadecimal value [VARBINARY(16)] so that I can lookup an IP addresses GeoLocation.

The problem here is that I am loading a large amount of records. Currently, the vendor provides close to 200 million IP address Geolocations (i.e., dimension lookup table). I knew from the inception that performance optimization will be required at all stages (i.e., hardware configuration, table partitioning, and indexing strategy). I have loaded one week's worth of sample log data and that is approximately 150 million records.

Note: The log files are parsed where approximately 90% of records are ignored - we are only loading 10% of the records, so there is no performance boost that can be made here

I have created the following indexes on the ExchangeLogs table:

1. A clustered index on an integer IDENTITY column called RowId
2. A non-clustered index on the ProtocolId (i.e., IPv4 or IPv6 represented as integers), IpHex; where the RowId is included

I have created the following indexes on the IPGeoLocation table:

1. A clustered index on an integer IDENTITY column called RowId
2. A non-clustered index on the ProtocolId (i.e., IPv4 or IPv6 represented as integers), StartIpHex, and EndIpHex; where the RowId is included

When searching for the IP Geolocation, I join the two datasets as follows:

SELECT RowId
FROM ExchangeLogs E
INNER JOIN IpGeoLocation I
ON E.ProtocolId = I.ProtocolId
AND E.IpAddress BETWEEN I.StartIpHex AND I.EndIpHex

This works in finding the IP Address and the execution plan shows the following:

The log files contain both internal and external IP Address on each row. For the sample data loaded:

1. The Internal IP list contains 3 DISTINCT IP addresses.
2. The external IP list contains approximately 60,000 DISTINCT IP Address.

SELECTS on a small amount of DISTINCT IP addresses are somewhat slow. For a sample of 150 Million records on inside IP addresses; there are 3 DISTINCT IP addresses and the SELECT takes about 9 minutes to complete.

SELECTS on large data sets don't return. For a sample of 150 Million records on inside IP addresses; I stopped the query after letting it run for 16+ hours (overnight).

I have not partitioned either the log table or the IP GeoLocation table. This might provide a performance boost by streaming data through two separate LUNs, but I am still trying to get a hardware configuration specification from our IT Ops group (they just provisioned new servers, so I don't have that info yet).