What's faster?
update c
set
c.createdon=q.CreatedOn
,c.createdby=case when q.createdby is not null then q.createdby end
,c.modifiedon=q.modifiedon
,c.modifiedby=case when q.ModifiedBy is not null then q.ModifiedBy end
from crm_annotationbase c
join IncidentWorknote q
on c.annotationid=q.annotationid
or this:
update c
set
c.createdon=q.CreatedOn
,c.createdby=isnull(q.createdby,c.createdby)
,c.modifiedon=q.modifiedon
,c.modifiedby=isnull(q.modifiedby,c.modifiedby)
from crm_annotationbase c
join IncidentWorknote q
on c.annotationid=q.annotationid
I have the first query running for 24 hours already. I'm updating a CRM 2013 table based on staging data.
I'd like to know whether I've chosen the most effecient solution of doing this?
Ok.. I had to dig around for this script. From reading the comments, it's a very large table that you are trying to update. The BEST way to speed this update up is to break it into batches. The reason it's taking so long is because of the transactional nature of the system... If something fails, the ENTIRE transaction (your whole update) will be rolled back. This takes SOOO much extra time. If you DON'T need this transactional all-or-nothing, try something like this (below). We have to update hundreds of millions of records and we were able to speed it up by HOURS just by batching the update.
Tweaking this could make it faster for you based on your data.
DECLARE @Update INT
DECLARE @Batch INT
-- Total number of records in database
SELECT @Update = (
SELECT COUNT(id)
FROM [table] WITH (NOLOCK) -- be CAREFUL with this
WHERE [' + @fName + '] IS NOT NULL) --optional
SELECT @Batch = 4000 --Batch update amount
WHILE (@Update > 0)
BEGIN
UPDATE TOP(@Batch) c
set
c.createdon=q.CreatedOn
,c.createdby=case when q.createdby is not null then q.createdby end
,c.modifiedon=q.modifiedon
,c.modifiedby=case when q.ModifiedBy is not null then q.ModifiedBy end
from crm_annotationbase c
join IncidentWorknote q
on c.annotationid=q.annotationid
SELECT @Update = @Update - @Batch; -- Reduce for next set
WAITFOR DELAY '000:00:00.400'; -- Allows for waiting transactions to process optional
END;