Skip to main content

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

16
  • It's use cases like this where I wish SQL implemented the concept of a range (start & end) data type, possibly with a uniqueness (non-overlapping) constraint, along with the one-dimensional equivalent to a spatial index. Can the cidr table have rows with overlapping start/end ranges, or are the ranges distinct and non-overlapping? If the ranges are non-overlapping, with at most one match for any given IP address, it should be possible to use the OUTER APPLY(SELECT TOP 1 ...) pattern to efficiently select a single candidate row and then verify the range. Commented Jan 2 at 20:03
  • 1
    What are the differences in the clustered index seek with respect to the "seek predicate" and "predicate" properties in the two plans? Also, what are the details on that filter operator in the CLR-based plan? It'll probably be easier for those trying to help if you upload the plans to paste the plan (even if you anonymize them using Plan Explorer) Commented Jan 2 at 20:13
  • @BenThul Thank you for the suggestion. I updated the question with links to pastes of the plans. I did edit the plan to remove the IP addresses that I was testing with, but I otherwise left the plan intact. Commented Jan 2 at 20:30
  • @TN It does look like rewriting the query to convert the LEFT JOIN to an OUTER APPLY works efficiently with the CLR function, so that's a good solution that I hadn't considered, and it would probably be the solution I use for my queries if I use the CLR function. However my broader question is ultimately if there's some sort of magic that I'm missing for deploying or defining CLR functions that would play nicer with the query planner, or if this is just emblematic of the difficulties with using CLR functions. Commented Jan 2 at 20:39
  • 1
    @TN I was running 250,000 IP addresses against the same ~1 million cidr table. I think a big part of the 11 second runtime from yesterday was that I was also running a resource intensive process on my computer when doing some testing here (I'm testing on a local instance). Today it is closer to 3 seconds, and it is the same when doing the subquery in the OUTER APPLY like you were suggesting. And yes, that condition holds true and there are no overlaps in the CIDR ranges from the table. Commented Jan 3 at 14:56