I implemented the two new indexes that Ypercube recommended. While the query for the InsideIp lookup completed much faster (1.5 minutes versus 9 minutes), the OutsideIp lookup is still running after 1.5 hours. I will let the query run over the weekend to see if it even completes. Note: the InsideIp list contains 3 DISTINCT IPs while the OutsideIp list contains approximately 60K DISTINCT IPs.
I understand the server may be constrained memory-wise and probably needs more memory and potententially a combination of more CPUs and disk(s) (for multiple database files that allow for LUN/stream optimization).
Before I travel down that path, I am thinking three alternative approaches.
- Change the clustered index as previously mentioned (ProtocolKey, NetworkStartIpHex, NetworkEndIpHex, RowKey)
- Place the lookup columns (ProtocolKey, NetworkStartIpHex, NetworkEndIpHex, RowKey) in an in-memory (OLTP) table.
- Partition the table. But, not sure how to effectively partition over a VARBINARY(16) column. I know VARBINARY(MAX) is explicitly ruled out by Microsoft, but is VARBINARY(16) ruled out as well?
Can anyone provide some insights as to which might be better?