Update statement - 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 2000 Forums

Transact-SQL (2000)

Update statement

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-07 : 09:00:41

Hi,
I have this update statement and it takes a very long time to run, I can run a select on the records and I get results in a second, but the update portion runs at least an hour. I am only updating a couple hundred records. What am I missing?


 
update  hmpdw.dbo.dtbl_claims  
set clm_type =  a.CLM_TYPE,
tax_id = a.TAX_ID, 
prov_num = a.PROV_NUM,
net_num = a.NET_NUM,
deal_rec = a.DEAL_REC,
upin = a.UPIN,
ppo = a.PPO,
serv_date = b.serv_date,
ub_btype = a.UB_BTYPE,
proc_date = a.PROC_DATE,
rec_date = a.REC_DATE,
proc_time = a.PROC_TIME,	
datein = a.DATEIN,
dateout = a.DATEOUT,
deal_type = a.DEAL_TYPE,
master_dup = a.dup,
ss = a.SS,	
phy_num = a.PHY_NUM,
accident = a.ACCIDENT,
time_stamp = a.time_stamp,
ediout = a.ediout,
master_deleted = a.Deleted,
last_modified_by = UPPER(a.last_modified_by),
version = a.version,
send_date_ah = a.send_date_ah,	
charge = b.CHARGE,	
price = b.PRICE,
variance_perc = cast((case WHEN  b.charge > 0 THEN((b.charge - b.price)/ b.charge *100)else 0.0 END)as numeric),
variance_amt  = (case WHEN  b.charge > 0 THEN((b.charge - b.price))else 0.0 END),
detail_time_stamp = b.time_stamp,
idnum = b.idnum,
deleted = b.Deleted,
repriced = b.repriced,
dup = b.dup,
code = b.Code,
tpa_num = a.tpa_num,
source = a.source,
status = a.status,
net_name = d.net_name,
net_owner = d.net_owner,
owner_id = d.owner_id,	
member_of = case when (a.tax_id) in('391102739', '364385181', '364495910', 
		  '390806174', '390806181', '390806347', '390872192', '390930748',
		  '391022464', '391150165', '391540571', '391597102', '391678306',
                  '391696443', '391805963', '391824445', '391943214', '391947472',
		  '391979612', '390806180', '391027676', '391211629', '391480198',
		  '391595302', '391733325')
		then 'Aurora'
		ELSE
		Null
		END,
	update_date = getdate()
FROM	HMPDW_Staging.dbo.MASTER a		
	INNER JOIN HMPDW_Staging.dbo.DETAIL b
	ON a.CLAIM = b.CLAIM
	AND a.VERSION = b.VERSION 
	and a.SOURCE = b.SOURCE	
	left outer join HMPDW_Staging.dbo.networks d
	on a.net_num = d.net_num
	WHERE b.dup = 0 
	and a.time_stamp >=  '12/05/2006 3:08:47 PM' and
		  exists	
			(select claim, version, source 	
			from hmpdw.dbo.dtbl_claims 
			where a.claim = hmpdw.dbo.dtbl_claims.claim
			and a.source = hmpdw.dbo.dtbl_claims.source
			and a.version = hmpdw.dbo.dtbl_claims.version
			and b.idnum = hmpdw.dbo.dtbl_claims.idnum)

Thanks,
Marcie

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-07 : 09:10:14

Any triggers on the table you are updating?
Any concurrent processes that are blocking this update? (run "sp_who2 active" while updating)

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 09:16:31

Try this

UPDATE		hmpdw.dbo.dtbl_claims  
SET		clm_type =  a.CLM_TYPE,
		tax_id = a.TAX_ID, 
		prov_num = a.PROV_NUM,
		net_num = a.NET_NUM,
		deal_rec = a.DEAL_REC,
		upin = a.UPIN,
		ppo = a.PPO,
		serv_date = b.serv_date,
		ub_btype = a.UB_BTYPE,
		proc_date = a.PROC_DATE,
		rec_date = a.REC_DATE,
		proc_time = a.PROC_TIME,
		datein = a.DATEIN,
		dateout = a.DATEOUT,
		deal_type = a.DEAL_TYPE,
		master_dup = a.dup,
		ss = a.SS,	
		phy_num = a.PHY_NUM,
		accident = a.ACCIDENT,
		time_stamp = a.time_stamp,
		ediout = a.ediout,
		master_deleted = a.Deleted,
		last_modified_by = UPPER(a.last_modified_by),
		version = a.version,
		send_date_ah = a.send_date_ah,	
		charge = b.CHARGE,	
		price = b.PRICE,
		variance_perc = case WHEN b.charge > 0 THEN 100.0 * (b.charge - b.price) / b.charge else 0.0 END,
		variance_amt  = case WHEN b.charge > 0 THEN b.charge - b.price else 0.0 END,
		detail_time_stamp = b.time_stamp,
		idnum = b.idnum,
		deleted = b.Deleted,
		repriced = b.repriced,
		dup = b.dup,
		code = b.Code,
		tpa_num = a.tpa_num,
		source = a.source,
		status = a.status,
		net_name = d.net_name,
		net_owner = d.net_owner,
		owner_id = d.owner_id,	
		member_of = case when w.tax_id is null then null else 'Aurora' end,
		update_date = GETDATE()
FROM		HMPDW_Staging.dbo.Master a		
INNER JOIN	HMPDW_Staging.dbo.Detail b ON b.CLAIM = a.CLAIM AND b.VERSION = a.VERSION AND b.SOURCE = a.SOURCE
INNER JOIN	HMPDW.dbo.dtbl_claims q ON q.claim = a.claim AND q.source = a.source AND q.version = a.version AND q.idnum = b.idnum
LEFT JOIN	HMPDW_Staging.dbo.Networks d ON d.net_num = a.net_num
LEFT JOIN	(
			SELECT	'391102739' tax_id UNION ALL
			SELECT	'364385181' UNION ALL
			SELECT	'364495910' UNION ALL
			SELECT	'390806174' UNION ALL
			SELECT	'390806181' UNION ALL
			SELECT	'390806347' UNION ALL
			SELECT	'390872192' UNION ALL
			SELECT	'390930748' UNION ALL
			SELECT	'391022464' UNION ALL
			SELECT	'391150165' UNION ALL
			SELECT	'391540571' UNION ALL
			SELECT	'391597102' UNION ALL
			SELECT	'391678306' UNION ALL
			SELECT	'391696443' UNION ALL
			SELECT	'391805963' UNION ALL
			SELECT	'391824445' UNION ALL
			SELECT	'391943214' UNION ALL
			SELECT	'391947472' UNION ALL
			SELECT	'391979612' UNION ALL
			SELECT	'390806180' UNION ALL
			SELECT	'391027676' UNION ALL
			SELECT	'391211629' UNION ALL
			SELECT	'391480198' UNION ALL
			SELECT	'391595302' UNION ALL
			SELECT	'391733325'
		) w ON w.tax_id = a.tax_id
WHERE		b.dup = 0 
		AND a.time_stamp >= '12/05/2006 3:08:47 PM'

Peter Larsson
Helsingborg, Sweden

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-07 : 09:27:49

Peter that worked great. Can you explain why this did the trick?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 09:35:39

I did two things.

First I changed the massive CASE statement to a LEFT JOIN for more speed.
Then I changed the EXISTS statement to a INNER JOIN because of speed. The EXISTS is evaluated for every single record! ANd since you did a lot of JOINS, that was expensive. So I changed that too to a INNER JOIN, which is evaluated once.

How long time did the query took before (at least 1 hour) and how long time does it take now?

Peter Larsson
Helsingborg, Sweden

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-07 : 09:45:43

I never let the query finish before since it took so long. It ran over an hour and I killed it last night. I can run the update now in less than a second! Thanks so much!

Marcie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 09:48:32

You're welcome.
Good luck!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources