update takes very long to execute

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)

update takes very long to execute

Author

Topic

gigitek
Starting Member

1 Post

Posted - 2008-11-26 : 07:24:29

I use a query to update about 2500 rows. when i execute the update in sql server management studio express it takes about 2 seconds. Same update, in a stored procedure, executes in 2 minutes.I execute the procedure with the same parameters. That's a lot of extra time. what's the explanation?
I have this indexes:
-column debit in sahnote
-column credit in sahnote
-column cont,cap,art in sahbalanta

this is the update :

update sahbalanta
set rlunad=(select ISNULL(sum(suma),0) from sahnote -- incarcam rulajele lunare in balanta pe debit
where month(data)=@luna
and year(data)=@an
and rtrim(capDebit)=s.capitol
and rtrim(artDebit)=s.articol
and Debit=s.cont
and uid=@uid),
rlunac=(select ISNULL(sum(suma),0) from sahnote -- incarcam rulajele lunare in balanta pe credit
where month(data)=@luna
and year(data)=@an
and rtrim(capCredit)=s.capitol
and rtrim(artCredit)=s.articol
and Credit=s.cont
and uid=@uid)
from sahbalanta as s
where luna=@luna
and an=@an
and uid=@uid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 07:32:13

May be parameter sniffing. http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 07:34:07

[code]UPDATE x
SET x.rlunad = coalesce(y.suma, 0)
FROM sahbalanta AS x
LEFT JOIN sahnote AS y ON y.capDebit = x.capitol
and y.artDebit= x.articol
and y.Debit = x.cont
and month(data) = @luna
and year(data) = @an
and uid = @uid

UPDATE x
SET x.rlunac = coalesce(y.suma, 0)
FROM sahbalanta AS x
LEFT JOIN sahnote AS y ON y.capDebit = x.capitol
and y.artDebit= x.articol
and y.Credit = x.cont
and month(data) = @luna
and year(data) = @an
and uid = @uid[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources