I'm trying to update the columns of one table to contain values copied from a table in a different database.
The update statement I'm using is:
The ip2location.dbo.ip2locationData is a table the contains approx 7 million records with regional data based on IP addresses.
I've created non-clustered indexes on the ipFrom and ipTo columns as follows: create nonclustered index idx_ipFrom on ip2locationdata (ipFrom) ON FG_INDEXES create nonclustered index idx_ipTo on ip2locationdata (ipTo) ON FG_INDEXES
The loadBannerImpressions table contains approx. 500,000 rows.
The update statement tends to take a long time to execute, and I'm wondering if I'm going about this the wrong way. 500,000 rows, joining with a table of 7,000,000 rows is going to eat some CPU time, but I'm wondering if there's a better way I could be doing this?
Have you examined the execution plans to see which of the indexes are being used? In all likelihood you will probably need to create a compound index using both the IP columns. SQL Server can do index intersection, but it's not that common for it to do so. You may also need to include the columns from the select list to avoid the key lookup. But again, without the execution plan, it's hard to know.
What's the clustered index on ip2locationData?
answered Feb 04, 2010 at 02:28 PM
Grant Fritchey ♦♦
In SQL Server 2008 and later it's best to avoid using "UPDATE with join". Use MERGE instead. MERGE is frequently more efficieant and is standard SQL whereas UPDATE...JOIN is proprietary. UPDATE...JOIN also gives unpredictable results if the join criteria is non-unique.
answered Feb 04, 2010 at 03:46 PM