1

This question is an extension of a previously asked question Index Strategy and Query optimization, which further extends this question: Search between IP string addresses

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.

  1. Change the clustered index as previously mentioned (ProtocolKey, NetworkStartIpHex, NetworkEndIpHex, RowKey)
  2. Place the lookup columns (ProtocolKey, NetworkStartIpHex, NetworkEndIpHex, RowKey) in an in-memory (OLTP) table.
  3. 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?

Improve this question
2
  • Have you tried a clustered column store index at all?
    – Mr.Brownstone
    Commented Mar 9, 2019 at 0:00
  • @Mr.Brownstone Initially I did not even consider it due to limitations that exclude INCLUDE and ALTER statements. Would you say column store index is better? If yes, can you provide some insights?
    – J Weezy
    Commented Mar 9, 2019 at 5:13

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.