October 24, 2007 at 2:42 pm
I have a table A partitioned by yearmonth..I need to delete the records in table A that got modified
table B is the staging table which pulls only recent changes.
table A is the fact table which has millions of records.
Indexes are present on ID columns in both the tables.
Should I use update with a combination of delete..Update to mark the rows that got modified and delete them
or
Should I directly delete based on the existence..
i) update a
set aKey = -20077006
from dbo.a A inner join dbo.Stg_a B with (nolock)
on A.id = B.id
where YearMonth = @PrevDayYoMo
delete from dbo.a
where aKey = -20077006
===========================================================================
ii) delete from dbo.a
where YearMonth = @PrevDayYoMo
and exists (
select *
from dbo.Stg_a B with (nolock)
where dbo.a.ID = b.DID
)
October 24, 2007 at 2:50 pm
My instinct says the second should run faster, but there's only one way to really find out. Try them both out on a test server (wrapped in begin transaction ... rollback transaction) and see how they run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2007 at 3:56 pm
you also have option III
delete A
from dbo.a A inner join dbo.Stg_a B with (nolock)
on A.id = B.id
where YearMonth = @PrevDayYoMo
avoiding the correlated subquery will tend to run a LOT faster than II.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply