SQL - fastest interval lookup

I have table GeoIP with three columns: bigint Start, bigint End and varchar Country (actually, this is IP to country mapping). Intervals are specified by Start and End columns, sorted and have no overlaps or gaps. This table has a lot of rows (hundred thousands).

Table Users with three columns: int UserId, 开发者_运维问答varchar Login and bigint IP.

What is the fastest sql way (statement and table architecture) to join these tables to assign Country to each User? I'd like to have View.

At the moment I use cross apply but it works pretty slow.

UPDATE:

I was wrong in the initial question. Fortunately interval lookup works smoothly. The real issue was in string concatenations like GeoIP.Country + ' ' + GeoIP.Region + ' ' + GeoIP.City AS Region. They badly changed execution plan. Switching my calculations from SELECT to user function solved the problem.

---

I had a similar problem once with an IP range table that was constantly doing full scans when querying for the range containing a given element (on Oracle 10g some years ago):

select country
from geoip
where ? between start and end

The solution was to rewrite the query like so:

select country
from geoip
where start = (
   select max(start)
   from geoip
   where start <= ?
)
and end >= ?

Perhaps this technique will speed up your query? I'm not 100% it is the same issue, since you are looking to join, but perhaps you can use the theory.

Alternative Solution

The real issue may be in string concatenations like GeoIP.Country + ' ' + GeoIP.Region + ' ' + GeoIP.City AS Region. They can badly changed execution plan. Switching the calculations from SELECT to a user function may solve the problem.