Thanks for your fast answer!
The ip is converted this way: For example, If the IP address 126.96.36.199, then the IP number is 2709785857.IP Number, X = 161 x (256*256*256) + 132 x (256*256) + 13 x (256) + 1 = 2709785857.
You can have more info here: http://www.ip2location.com/docs/IP2Location_IP_Country_Region_City_Specification.pdf
So it can have 8 or 9 digits.
Sorry for the 256, it's obviously 255!
OK, that clears up the IP conversion algorithm. I still don't think that your sample data from tbl_ip2countries and from #regions tally up with each other, but let's not worry about that for now.
update rset countryId = c.countryIdfrom #regions r join tbl_ip2countries con r.ipFloat between c.ipFrom and c.ipTo
Not sure how it would perform, but much better than a cursor, I should think. You could experiment with using >= and <= operators instead of the BETWEEN function to see if that goes any faster.
That's really weird: For 10 ip, it takes 7-8 seconds. For 100, it takes about a minute which is slower than my problem at the beginning.
If I try with 40 or 50 ip, it executes in a flash: 1 second!
The difference is in uses table spool (lazy spool) in the first case but only clustered index seek in the second case.
What the ...???
By the way, in the prod environment, it's going to be up to 1000 ip.
I really don't get it...
10-20-30 ip uses the lazy spool so it takes several seconds.
40-50-60 ip uses the clustered index seek so it executes in a flash.
70 and more also uses the lazy spool so it takes more than a minute.
Is there a way to force the use of the index instead of the costly table spool?
By the way, it's a clustered index SCAN with the table spool instead of a clustered index seek. What's the difference?
SCAN : reads all rows.
SEEK : binary search algorithm to find only the required row.
Could you reindex and / or update the statistics on the table.
If that doesn't help you could always use index and join hints (last resort).
The scan is scanning the whole index (the whole table in the case of a clustered index) whereas a seek goes straight to where it needs to go to find the data. Therefore a seek is usually desirable. I think the reason for this is that the query optimizer can't use the clustered index to work out the "<= ipTo" part of the "BETWEEN ipFrom and ipTo" clause. Therefore I would recommend you create a non-clustered index on ipTo (assuming that your clustered index is created on ipFrom and ipTo in that order).
Still the same results. Works great for 40-50-60, but not for any other number...
I tried this hint:
Does not improve anything...
Thanks for your time guys!
I made few more tests and It's working great with few hundreds ip. It's really fast.
I'll stick with that from now on!
Thanks a lot!