Update query performance in transaction - 12-08-2005 , 08:50 AM
I have got an update query which takes significantly more time if put within
a transaction (having another insert and update) than if run outside the
In Transaction -> 34 min
Outside transaction -> 1 min
I have enough space for log file to grow.
What could be the reason for this difference?
The update looks something like this:
UPDATE we SET
we.BaseEntitlementKey = weBEKey.BaseEntitlementKey,
we.LastRefreshDate = @StartDate
FROM Warehouse.dbo.EntitlementBase we
INNER JOIN Delta.dbo.Entitlement de ON we.EntitlementID =
INNER JOIN Staging.dbo.Wrk_EntitlementBase_PostLoad weBEKey (NOLOCK)
ON weBEKey.BaseEntitlementID = we.BaseEntitlementID
RE: Update query performance in transaction - 12-08-2005 , 10:21 AM
Did you run sp_who to see if it's being blocked when doing the transaction?
"Sumanta Gayen" wrote:
Re: Update query performance in transaction - 12-08-2005 , 04:21 PM
Even if you do not start a transaction, SQL Server will implicitly start one
for you for the batch.
As ChrisR said, check for blocking.
Mike Epprecht, Microsoft SQL Server MVP
IM: mike (AT) epprecht (DOT) net
MVP Program: http://www.microsoft.com/mvp
"Sumanta Gayen" <SumantaGayen (AT) discussions (DOT) microsoft.com> wrote