July 18, 2007 at 8:31 am
I have a Stored Procedure that updates three fields in an Employees table (28k rows) from a ZipCode table (3.4M rows). The SQL in the SP is:
TRAN UPDDIST
dbo.EMPLOYEE
SET CongDist = (Select CongDist from dbo.Ranged
Where dbo.Employee.hzip = dbo.Ranged.ZIP AND
cast(dbo.Employee.hzip4 as int) between cast(dbo.Ranged.Start as int) AND cast(dbo.Ranged.Stop as int))
dbo.Employee, dbo.Ranged
dbo.EMPLOYEE
SET UpperDist = (Select UpperDist from dbo.Ranged
Where dbo.Employee.hzip = dbo.Ranged.ZIP AND
cast(dbo.Employee.hzip4 as int) between cast(dbo.Ranged.Start as int) AND cast(dbo.Ranged.Stop as int))
dbo.Employee, dbo.Ranged
dbo.EMPLOYEE
SET LowerDist = (Select LowerDist from dbo.Ranged
Where dbo.Employee.hzip = dbo.Ranged.ZIP AND
cast(dbo.Employee.hzip4 as int) between cast(dbo.Ranged.Start as int) AND cast(dbo.Ranged.Stop as int))
dbo.Employee, dbo.Ranged
TRAN UPDDIST
Employee.hzip and hzip4 are index in a non-clustered, non-unique index. The PK for Ranged is ZIP+Start+Stop so I think I've optimized that part.
The Ranged (Zip Code) table is provided by a 3rd party so the data is what it is. I have to Bulk Load it so the columns are all CHAR, hence the CAST to convert everything to INT so I can get a valid range comparison.
1. Any suggestions on ways to translate the data as part of the load so as to not have to CAST everything in the Update query?
2. Any way to update all three fields (Employee.CongDist, UpperDist and LowerDist) in one query? I assume this would run in 1/3 of the time...
July 18, 2007 at 8:44 am
No guarentees since you didn't post the full table definitions but based on what I saw I would think this would work:
UPDATE dbo.EMPLOYEE
SET CongDist = r.CongDist,
UpperDist = r.UpperDist,
LowerDist = r.Lowerdist
from dbo.employee e join dbo.ranged on (e.hzip = r.zip)
where cast(e.hzip4 as int) between cast(r.Start as int) AND cast(r.Stop as int)
James
July 18, 2007 at 8:51 am
As for preventing the casts in each query I recommend bulk loading to a "staging" or temp table then have a function (created seperately) that merges/updates or replaces the values in the permenant table (casting as appropriate) the values from the staging table. Depending on how often you get updates to the ZIP code table (I used to get mine monthly) the time to do a one time conversion is not that bad. I did mine on the staging server (data warehouse) and then dts the final product to the production server.
James.
July 18, 2007 at 8:55 am
That update query was kind of a "Duh" on my part. For some reason I just couldn't see it. Going on vacation Friday. Maybe my brain has already started...
Loading the data into a table then converting the items sounds like a good idea. I'll take a look at it after vacation. Our updates are quarterly and we just did one (hence my questions).
Thanks for the help.
Rick
July 18, 2007 at 9:05 am
With the ZIP data, why cant you load the character data into an int column - wont SQL Server will do an implicit cast during the load?
J
July 18, 2007 at 9:13 am
Another thing to try...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply