richardn-1128243 (4/21/2010)
This is great - despite the limitations on accuracy.I have a collection of IP Addresses in the Visitor_Log table, which I convert to an IPNum - no problems there - then I wish to do a join to the Blocks table and thus the Location table. If I do very small subset (say 13 IPNums) and do a INNER JOIN with the BETWEEN criteria it takes about 13 seconds. When I do it with 300 IPNums it takes over 10 mins. Looks like the BETWEEN is too heavy.
Any ideas how I could make this thing run like "a cut cat"?
Here's the query being used:-
SELECTl.Referrer,
l.IPAddress,
l.IPNum,
b.locId,
cl.country,
cl.region,
cl.city,
cl.postalCode,
cl.latitude,
cl.longitude,
cl.areaCode,
cl.metroCode,
b.startIpNum,
b.endIpNum
FROM
Visitor_Log AS l INNER JOIN
GeoLiteCity_Blocks AS b ON l.IPNum BETWEEN b.startIpNum AND b.endIpNum INNER JOIN
GeoLiteCity_Location AS cl ON b.locId = cl.locId
Use the "trick" given in post 830703. I've definitely noticed an improvement in my queries from the BETWEEN operator. Also, I put a 100% fillfactor clustered index on StartIP, EndIP and not separate indexes on each. Even though it takes additional space, to reduce the time for the query to execute even further, I made an indexed persisted computed column on the log tables (where the IP is stored) and use the computed column (LongIP) in the queries instead of computing it on the fly.