make this more effecent? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

make this more effecent?

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-02-26 : 20:18:24

i was wondering if anyone could help me write all this into one or two quries...



alter table testdata add areacode varchar(3),
	countystate varchar(35),
	citystate varchar(30),
	county_name varchar(30),
	ltv float
go
	
UPDATE t1
SET t1.county_name=t2.county
FROM testdata t1
INNER JOIN master.dbo.zip t2
ON t1.statecode+t1.countycode=t2.fips
go

update t1 set 	
	maritalstatus = case maritalstatus 
			when '1' then 'M'
			when '2' then 'S' end,
	homeowner = case homeowner
			when '1' then 'R'
			when '2' then 'T'
			when '3' then '9'
			when '4' then 'H' end,
	areacode = substring(phone,1,3),
	citystate = rtrim(city)+' '+state_abbrv,
	countystate = rtrim(county_name)+' '+state_abbrv,
	ltv = case when estcurhomeval >0 then cast(oo_mtg_amnt as float)/cast(estcurhomeval as float) end
from testdata t1 
go

basically, i have to run this against some fairly large tables, so i want to try and get it down to one pass,
instead of three.

Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-26 : 21:15:51

You can't combine the alter statement with anything.

Can't you do this for the updates:


update t1 set
	t1.county_name=t2.county,	
	maritalstatus = case maritalstatus 
			when '1' then 'M'
			when '2' then 'S' end,
	homeowner = case homeowner
			when '1' then 'R'
			when '2' then 'T'
			when '3' then '9'
			when '4' then 'H' end,
	areacode = substring(phone,1,3),
	citystate = rtrim(city)+' '+state_abbrv,
	countystate = rtrim(county_name)+' '+state_abbrv,
	ltv = case when estcurhomeval >0 then cast(oo_mtg_amnt as float)/cast(estcurhomeval as float) end
from testdata t1 
INNER JOIN master.dbo.zip t2
ON t1.statecode+t1.countycode=t2.fips
go

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-02-26 : 21:21:25

absolutely... i didnt think there was anything i can do about the alter, and update. but i guess i didnt really look at the big picture for the 2 updates.

that's why you guys/girls are them (WO)MAN!

:) thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-27 : 02:08:08

Tara, I think that INNER JOIN should be a LEFT JOIN.
We don't know if there are equal matching number of records in master.dbo.zip table as in TestData table.

There might be one or more zipcodes in TestData that does not exists in master.dbo.zip
If this is the case, neither MaritalStatus nor homeowner will be updated.

E 12°55'05.25"
N 56°04'39.16"

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-02-27 : 11:05:25

he is correct, although the zip table should be every zip in the country, there is an off chance that a fips in that table will not match a fips in the testdata table (fyi, a fips is a county code instead of the actual name)

would left-join be the best bet in that case?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-27 : 13:24:52

quote:
Originally posted by Peso

Tara, I think that INNER JOIN should be a LEFT JOIN.
We don't know if there are equal matching number of records in master.dbo.zip table as in TestData table.

There might be one or more zipcodes in TestData that does not exists in master.dbo.zip
If this is the case, neither MaritalStatus nor homeowner will be updated.

All I did was combine the queries as requested. I have no idea if the solution is correct.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources