very Slow update query - 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)

very Slow update query

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-26 : 10:45:05

Hi I'have this query that updates about 12-15k of rows of data each night, but its taken a few hours to run. and i can't see why ?

declare @now
set @now = getdate() -1

Update rca
Set ProdGroup = pc.productgroup,
ProdOrg = pc.productorg,
ProdSubOrg = pc.productsuborg,
ProdBusinessGrp = pc.ProductBusinessGrp,
ProdSkillGroup = pc.ProductSkillGroup,
Mroc = p.mroc
from tbl_Voy_RCATransfer as rca
left join tlb_Voy_ProductsLU as p
on rca.prodid=p.prodid
and rca.countrycd3 = p.countrycd3
and rca.servicelegacysys = p.serviceprovidername
left join tbl_Voy_ProductCodeLU as pc
on p.ccoacode = pc.ccoacode
and rca.StartTime >= dbo.udf_date_only(@now)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 11:40:31

What does function dbo.udf_date_only() does? Why don't you store the output of this function in a variable and use that variable in the WHERE clause?

declare @now
select @now = dbo.udf_date_only(getdate()-1)

Update rca
Set ProdGroup = pc.productgroup, 
ProdOrg = pc.productorg, 
ProdSubOrg = pc.productsuborg, 
ProdBusinessGrp = pc.ProductBusinessGrp, 
ProdSkillGroup = pc.ProductSkillGroup,
Mroc = p.mroc
from tbl_Voy_RCATransfer as rca 
left join tlb_Voy_ProductsLU as p 
on rca.prodid=p.prodid 
and rca.countrycd3 = p.countrycd3 
and rca.servicelegacysys = p.serviceprovidername 
left join tbl_Voy_ProductCodeLU as pc 
on p.ccoacode = pc.ccoacode
and rca.StartTime >= @now

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-26 : 12:07:40

The function dbo.udf_date_only()
returns the date only part of the date eg 26/04/2006 not time is part of the date. the query took 4 hours to update 11,000 rows.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 16:40:21

Try this. Which indexes do you have?
Are ony of the tables in the WHERE or JOIN ON indexed?

UPDATE		rca
SET		rca.ProdGroup = pc.productgroup, 
		rca.ProdOrg = pc.productorg, 
		rca.ProdSubOrg = pc.productsuborg, 
		rca.ProdBusinessGrp = pc.ProductBusinessGrp, 
		rca.ProdSkillGroup = pc.ProductSkillGroup,
		rca.Mroc = p.mroc
FROM		tbl_Voy_RCATransfer AS rca 
LEFT JOIN	tlb_Voy_ProductsLU AS p ON p.prodid = rca.prodid AND p.countrycd3 = rca.countrycd3 AND p.serviceprovidername = rca.servicelegacysys 
LEFT JOIN	tbl_Voy_ProductCodeLU AS pc ON pc.ccoacode = p.ccoacode
WHERE		rca.StartTime >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources