Forums Register Login

Compare IP address in a range

+Pie Number of slices to send: Send
I have a table holding the range of IP addresses (from and to) and the region. I need to find out the region for the input IP address.
Presently, I am firing a query to get the range - something like SELECT RANGE FROM TABLE WHERE INPUT_IP BETWEEN FROM_IP AND TO_IP.

Now, the table has millions of entries and the input IP addresses count is also pretty big. So it doesn't make sense to fire a query for each input IP address.

Any better way to do this?

Thanks.
+Pie Number of slices to send: Send
If you have a decent DB on decent hardware, and properly indexed tables, then a single query against several million rows shouldn't be a problem. If you are able to do multiple queries at once, you could use PreparedStatement and batches to possibly improve performance.

The next step after that would probably be caching of some sort, whether a home-grown solution or something of the shelf like ehcache or memcahced.

Are you actually experiencing a performance problem that you've measured and determined is caused by these queries? Or are you just guessing at what might be a problem?
+Pie Number of slices to send: Send
There's certainly a performance issue. Cannot execute millions of queries one by one. Please note that the table has a lot of other information that I require when the IP address matches the range.
1
+Pie Number of slices to send: Send
 

Sud Umm wrote:There's certainly a performance issue.



So, you've actually measured this in a scenario that's a reasonable approximation of your expected production environment and load?

Cannot execute millions of queries one by one.



Of course we can. The question is how quickly can we do it, and how quickly do we need to do it? Do you have concrete numbers for those two questions?

Please note that the table has a lot of other information that I require when the IP address matches the range.



That statement by itself doesn't really say anything useful.

To summarize:

1. Decent hardware.
2. Decent DB software.
3. Proper indices.
4. PreparedStatement + batching.
5. Measure.
6. Caching.
+Pie Number of slices to send: Send
 

Sud Umm wrote:Now, the table has millions of entries and the input IP addresses count is also pretty big. So it doesn't make sense to fire a query for each input IP address.



Well, that "pretty big" phrase is kind of imprecise. But later on in the thread you stated that you would be running "millions" of queries if you did one query for each IP address. So that would mean that your database has millions of different IP addresses in it. Is that correct? Or did "pretty big" mean something like "a couple of thousand"?
+Pie Number of slices to send: Send
 

Sud Umm wrote:Now, the table has millions of entries and the input IP addresses count is also pretty big. So it doesn't make sense to fire a query for each input IP address.

Any better way to do this?


Well, assuming:
(a) Your db table does not contain overlapping ranges.
(b) You can insert all the IP addresses to be queried into a temporary table.
then what you're doing is an "inner join" between your monster table and the temp table.

You should probably look up the exact syntax for your db, since different versions of SQL may have different ways of doing it (don't you just love it? The most important data retrieval language on the planet still not standardized after 30+ years ).

However, as others have said, indexing is going to be very important in terms of performance. I'd say, at the very least:
1. If your db table contains START_IP and END_IP columns, then at least one of them should be indexed.
2. If your temp table is likely to contain more than 50 addresses or so, then it should also be indexed.

Winston
Every snowflake is perfect and unique. And every snowflake contains a very tiny ad.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 2960 times.
Similar Threads
Hacking the identicons
Multicast Addressing
Suggestion for Secure site required
how can i get client IP?
How to find ip address using mac in a network
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 02:38:31.