Performance problems - udf and join with no primary key

Guys,

I am working on trying to figure out country from which ip originated. This can be done using ip number and a dataset (which is availiable on the web for free).

I written a MS SQL 2005 function which will transform ip address to ip number. Problem is performance.

My query looks like this

select top 10
personId, t.*
from
person
left outer join ipToCountry t on cast(t.ipLower as bigint)<=ipToIpNumber(ipaddress) and cast(t.ipUpper as bigint)>=pToIpNumber(ipaddress)

It takes ages to run and the server is pretty beefy. Any suggestions? How I could make it quicker?

Thanks.

Possible to add computed column for ipnumber in the table and set value with your function?

It could be possible to do that. I am not sure if I would be able to do this retrospecively - there are millions of records in the person table and the server is in use all the time.

Would there be a way of imporving the performance with indexes?

SQL Server does not support function based index, so you have to create a computed column and index that.

Thanks Guys.

What indexes I should add onto the table with ip numbers and countries?

Can't tell without table schema and query exection plans.

It should be on the computed column.

The tables are organised in follwing way.

Table ipToCountry:
ip_lower (bigint, not null)
ip_upper (bigint, not null)
country (varchar(200))

Person:
personId (bigint, not null, pk)
ipAddress (varchar(whatever))
etc.
etc.

There are already multiple indexes on person table and preferably I would not want to add any more - slower insertions; there are thousands done every hour.

So I will add a computed column to the person table. What kind of index would be best?

I will get you execution plan tomorrow morning - not feeling like accessing the db from home.

Thanks.

wujtehacjusz, what everyone is trying to tell you is that all those indexes you have on the tables are useless (for this query) because you are using Functions to derive values for the actual column date, the Indexes will NOT be used.

I do have a question. In your last post you indicate that column IPLOWER and IPUPPER are defined as BIGINT.

Quote:

---

Table ipToCountry:
ip_lower (bigint, not null)
ip_upper (bigint, not null)
country (varchar(200))

---

However, in you statement in the first post, you use CAST(IPLOWER AS BIGINT).

Code:

---

SELECT TOP 10
      personId, t.*
FROM person
      LEFT OUTER JOIN
      ipToCountry t
        ON    CAST(t.ipLower as bigint) <= ipToIpNumber(ipaddress)
            AND CAST(t.ipUpper as bigint) >= ipToIpNumber(ipaddress)

---

If it is already a BIGINT, you shouldn't need the CAST. This will eliminate the Functions on both IPUPPPER and IPLOWER and the indexes on those columns can then be used.

This would leave the suggestions to add 1 column to Person and populate it with the converted data. For example:

New column: IP_Number
Populate: set IP_Number = ipToipnumber(ipaddress)

Then you can Index IP_Number and your query should process faster (and possible much faster) with this query (without functions):

Code:

---

SELECT TOP 10
      personId, t.*
FROM person
      LEFT OUTER JOIN
      ipToCountry t
        ON    t.ipLower <= IP_Number
            AND t.ipUpper >= IP_Number

---

Agreed. Thanks guys for your help! I am happy with the info you provided and hopefully I will be able to improve query performance.