Results 1 to 10 of 10

Thread: Performance problems - udf and join with no primary key

  1. #1
    Join Date
    May 2009
    Posts
    9

    Question 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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Possible to add computed column for ipnumber in the table and set value with your function?

  3. #3
    Join Date
    May 2009
    Posts
    9
    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?

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    SQL Server does not support function based index, so you have to create a computed column and index that.

  5. #5
    Join Date
    May 2009
    Posts
    9
    Thanks Guys.

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

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Can't tell without table schema and query exection plans.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

  8. #8
    Join Date
    May 2009
    Posts
    9
    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.

  9. #9
    Join Date
    Apr 2009
    Posts
    86
    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.
    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

  10. #10
    Join Date
    May 2009
    Posts
    9
    Agreed. Thanks guys for your help! I am happy with the info you provided and hopefully I will be able to improve query performance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •