performance of isnull vs select case statement

Question

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?

Answer 1

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;