Large recurrent query

[danielharel17]

Good morning,

I'm new to the forums so be easy on me

I have a process that reads from a log file, normally 50,000 lines of registers. Each log has a IP address field, which we use to determine the geographic location (country) of the user. To do this, we must search in a SQL table with70,000+ registers for a match. Such table has 3 columns (ip_from, ip_to, country_code).

This search uses SELECT country_code FROM table WHERE @IP BETWEEN ip_from AND ip_to.

With the correct indexes its pretty fast. But I don't like the fact that I'm submitting a SQL query for each IP I need to "resolve", and I feel that I need to find a way of doing it by loading the whole table into memory about (5mb).

I tried loading the whole table into a dataset and using the DataTable.Select() method, but this resulted in way higher processing times.

What other way do you guys suggest in doing loading this whole table in memory and doing the searches without recurring to the SQL Server for each single IP we have to process.

Thank you very much!

 

[cjard]

Define "pretty fast"

 

[danielharel17]

Well, 250 registers take about 3 seconds to complete with SQL Server. Anything below that would be an improvement.

With Datatable.Select() it takes arround 30 seconds, which is simply unacceptable.

 

[cjard]

Upload the 50,000 registers to the database and use a join query instead

SELECT 
  *
FROM
  tmp_registers t
  INNER JOIN
  country_code c
  ON
    t.ip >= c.ip_from AND
    t.ip < c.ip_to