Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  joining two tables using BETWEEN operator

Author  Topic  aborg88 Starting Member 1 Post Posted - 2014-09-22 : 11:36:26 Hello, I am using geolite to locate an ip to a geo ip, and would like to do this in bulk, i've already converted the ips, and these sit in a table ReportAnalyticsTempDB.dbo.t_login_blocks I've also imported the respective lookup table called, geoLiteCityBlocks the login table contains a ipblock, the geocityblock table contains a startIpNum and endIpNum, if the block falls within this range the result is found :) Query SELECT l.ipAddress, b.locId FROM ReportAnalyticsTempDB.dbo.t_login_blocks l INNER JOIN geoLiteCityBlocks b ON l.ipblock BETWEEN b.startIpNum AND b.endIpNum Since the login table has many rows (over 200k) and the geo ip contains 2 million, the query is very inefficient since it is not a HASH join. I've indexed the ip block in the login table and the ipstart and finish (together) in the geo table. Any clues what I can do? Shall i restructure the geoip table to have unique values? Regards, aborg88