| Author |
Topic |
|
icx
Starting Member
11 Posts |
 Posted - 2008-04-17 : 12:10:55
|
| 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.Logsset [Country] = @Countrywhere [IpNumber] = @IpNumber and [Country] = NullTherein 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 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-04-17 : 12:30:17
|
| so why don't you update the logs table at the end in one go?something like:update Lset [Country] = @Countryfrom dbo.Logs Ljoin YourLookUpTable T on L.[IpNumber] = T.[IpNumber] -- or some other key herewhere L.[Country] is null_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
icx
Starting Member
11 Posts |
Posted - 2008-04-17 : 18:53:50
|
Ok, this thought had crossed my mind - but I don't want to have to schedule a specific job to run at a set time... It is important that the data gets updated when it hits the table.Is there a way to update them all at once AFTER all the insert activity has finished?Thanksquote:
Originally posted by spirit1
so why don't you update the logs table at the end in one go?something like:update Lset [Country] = @Countryfrom dbo.Logs Ljoin YourLookUpTable T on L.[IpNumber] = T.[IpNumber] -- or some other key herewhere L.[Country] is null_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com
|
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-04-19 : 08:20:40
|
| your full insert ...after that update ...is that a problem??_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
icx
Starting Member
11 Posts |
Posted - 2008-04-19 : 12:23:53
|
| Hi,Thought I'd just try to shed a little more light on the goal. Basically, for each row that gets inserted into dbo.Logs, I want to fire a trigger that does a select from dbo.ip2location, joined on the IP address. dbo.ip2location has 3million rows, and is indexed nicely so the select here isn't a problem.Once the relvent values have been retrieved from dbo.ip2location, I need to add them to columns in the dbo.Logs table. *This* is where the performance problem seems to be arising.Any tips/ideas on how to imrpove this would be greatly appreciated.Thanks to all who have replied so far.Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-04-19 : 12:44:05
|
| how many rows get inserted at a time?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
icx
Starting Member
11 Posts |
Posted - 2008-04-21 : 06:04:38
|
quote:
Originally posted by spirit1
how many rows get inserted at a time?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com
Hi,In the insert to dbo.Logs, there can be anywhere from 1,000+ rows, but on average about 2,500.The ip2location database will always return either 1 or 0 matching records.Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-21 : 06:14:42
|
quote:
Originally posted by icx
The ip2location database will always return either 1 or 0 matching records.
How does it return the values?A function?A view?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-04-21 : 06:18:45
|
| so i'm still not clear why do you need a trigger??just do and update to location after you insert.what's the problem with that?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
icx
Starting Member
11 Posts |
Posted - 2008-04-28 : 04:25:10
|
Hi,The trigger needs to run at the same time the row is inserted - it is not possible in this scenario to run an update query AFTER the insert operation.Hence my original query...Thanksquote:
Originally posted by spirit1
so i'm still not clear why do you need a trigger??just do and update to location after you insert.what's the problem with that?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com
|
|
|
|
|
Update query running very slow