2

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?

Improve this question
11
  • 4
    If you want to optimize a 24h query to run significantly faster, a null check is the last thing I'd look at. This is really just picking an arbitrary element of this query and blaming it for performance problems. Commented Jun 26, 2014 at 15:58
  • 2
    This article details performance between COALESCE and ISNULL. COALESCE is basically CASE under the covers. sqlmag.com/t-sql/coalesce-vs-isnull Commented Jun 26, 2014 at 16:00
  • Agree with the statements above, why not speed it up by doing where q.modifiedon >= GetDate() -1 Commented Jun 26, 2014 at 16:07
  • 1
    You are updating all the records all the time, you only need to update those that have changed. I've assumed you are running it daily. You can limit the records you are updating relatively easily. Commented Jun 26, 2014 at 16:11
  • 1
    @MikeMiller great point thank u very much. but i do not know how to tell whether a record has changed. i need to run this process only once. Commented Jun 26, 2014 at 16:14

3 Answers 3

Reset to default
3

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Also, this would go faster if the annotationId was the primary key. You could add a where statement like "Where annotationId is between @minBatch and @maxBatch" or something like that. :)
Yep.. That's what worked the fastest for us. You could try running it in higher batches. But make sure the logic is correct before running in production (obviously). The TOP worked for us because I'm limiting the update with a WHERE statement so that when the four thousand records gets updated, they are no longer in the TOP group (because the condition is not met).
1

What you are doing is wrong for two reasons:

  1. Direct updates to a Dynamics CRM database is highly unsupported and can lead to several issues with your CRM instance (you need to use CRM Web Services to update the data)
  2. CreatedOn, CreatedBy, ModifiedOn and ModifiedBy are system fields and they are always filled, they never contains null values. (in particular CreatedOn and CreatedBy are specified when the record is created and cannot be modified after, ModifiedOn and ModifiedBy are updated every time the record is updated)

As advised by Microsoft here: http://msdn.microsoft.com/en-us/library/gg328350.aspx#Unsupported

Unsupported Customizations

Data (record) changes in the Microsoft Dynamics CRM database using SQL commands or any technology other than those described in the Microsoft Dynamics CRM SDK.

Improve this answer

3 Comments

1. please show me a source that says that those specific fields are dangerous to update manually. i've spoken to a CRM MVP about this and he said it was fine.
2. correct, but i'm checking for null values in a staging database, not in crm
you are updating the CRM db or not? I can't believe that a CRM MVP said that is fine to update directly the CRM db.
0

ISNULL will be better compared to COALESCE statement. Case - When is almost same as COALESCE

http://blogs.x2line.com/al/archive/2004/03/01/189.aspx

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.