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
transaction.

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 =
de.EntitlementID
INNER JOIN Staging.dbo.Wrk_EntitlementBase_PostLoad weBEKey (NOLOCK)
ON weBEKey.BaseEntitlementID = we.BaseEntitlementID

Did you run sp_who to see if it's being blocked when doing the transaction?
--
TIA,
ChrisR


"Sumanta Gayen" wrote:

Hi

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.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike (AT) epprecht (DOT) net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Sumanta Gayen" <SumantaGayen (AT) discussions (DOT) microsoft.com> wrote