Update Query Running Very Slow

Hi all,

I'm having what you might call an optimisation issue - but I'm also not sure if my approach to this problem is right. I've spent the whole day trying various methods but none seem to be performing as admirably as I'd hoped.

Basically, here's the scenario:

1. Log files are being inserted into a SQL table via Log Parser 2.2.
2. I have a lookup table of ip address ranges to countries and other geographic data.
3. Once the log row has been inserted from Log Parser, I want to update the same log table with data from the lookup table.

The main problem seems to be the updating (the initial insert from Log Parser is lightning quick).

I initially wrote a trigger on AFTER INSERT on the log table that converted the actual user's IP address into IPNumber format using a simple algorithm, then pumped the IPNumber into a quick select query which retrieved the geolocation data. Then, in the same trigger, there was an update statement which basically said:

update dbo.Logs
set [Country] = @Country
where [IpNumber] = @IpNumber and [Country] = Null

Therein lies the problem. The update statement slows everything down to almost a standstill and also seems to obtain a lock on the table.

Critical factors:

1. The log table must remain readable.
2. The query must execute in seconds -- not over half hour :)

I've experimented with various combinations of indexing, so far to no avail.

Any suggestions would be very much appreciated.

Regards