NAV2015: MODIFYALL behaviour on SQL Server

I’ve just spent an evening on something that I noticed earlier in one of my projects that wasn’t clear to me. And when you don’t understand something? You write blogposts, right?

You might have figured from the title .. It was regarding the MODIFYALL. Now, long time ago – in 2008 – I wrote a blogpost on this: What impact does my C/AL have on SQL – MODIFYALL/DELETEALL.

What didn’t I get?

Well, when you do a MODIFYALL, you might expect a SQL statement like:

UPDATE “NAV2015″.dbo.”CRONUS BELGIË NV$Test ModifyAll” SET “Value”=@0

But we had all different statements. In the SQL Profiler, we saw a bunch of these for one MODIFYALL(FALSE):

UPDATE “NAV2015″.dbo.”CRONUS BELGIË NV$Test ModifyAll With OnModify” SET “Value”=@0 WHERE (“Entry No_”=@1 AND “timestamp”=@2)

So, in stead of one statement which is going to update multiple records at once .. we got a loop through the recordset, and updates one-by-one.

If you read the blog above, you might suspect the reason why this happened. When you do a MODIFYALL(TRUE), you call the “OnModify” trigger. And this trigger has to be executed for each record. Hence, you get a loop and updates one-by-one.

But I did a MODIFY(FALSE)

So why was it still looping?? The code explicitly stated that the OnModify trigger shouldn’t be executed. And still .. It looped through the records.

OnDatabaseModify

It took me a while. When working generically, you might use the OnDatabase-triggers in Codeunit 1. We do that quite often in different frameworks. Well, this has a major impact on the MODIFYALL (and I guess also the DELETEALL) statement. Basically: MODIFYALL doesn’t work anymore! The loop is inevitable. When you have a triggermask that says: “hey, for all modifies of this table, go through this code” you basically say: “hey, all MODIFYALLs on that table should be looped through one-by-one, and execute this OnDatabaseModify code…”. It’s quite logical, but I never thought of it as a consequence. Obviously, there is quite a performance-impact…

Not only that…

When trying to replicate the behaviour, I created a small testingapplication. And I noticed even more “intelligence”. Sometimes you see no update statements while you’re still calling the MODIFYALL .. sometimes you don’t see any statement, not even a read .. . I suspect two things:

• It takes into account whether the record got actually changed or not, when doing individual update statements. It would make sense. If the UPDATE-statment of a specific field is going to result in the same value (it reads it to lock it, so it knows the current value), there is no need to actually perform the update-statement, is there?
  
• It reads from cache as well. The NST is aware of record changes. If the record is still in cache, and it finds it, it can match the updated value with it. So in this case, you don’t even get a read, nor an update-statement.
  

Conclusion

What’s not to love? There is actually an intelligent system behind the MODIFYALL that is not only going to automagically loop for you if necessary (ok, you should be aware, because of performance-issues), but also only going to perform updates when really necessary.. .

Still not clear?

I created some tests for this. Let me try to show with a few screenshots.

I created 3 tables:

• Test ModifyAll: this table contains an autoincrement key and two textfields for values
  
• Test Modifyall With OnModify: this table contains the same, but with an OnModify where I’m going to fill the second value-field
  

  
  

• Test ModifyAll OnDatabaseModif: same as the first table. No OnModify-trigger, but I set up a codeunit which I linked in Codeunit 1 to setup this table to trigger the “modify” globally (so basically the OnDatabaseModify):
  

  
  

  So basically: I’m doing nothing in the trigger .. I just want to call the code and see what happens on SQL Server.
  

I filled these tables with data to be modified. And I have a codeunit that I call, that executes the different combinations:

• Call MODIFYALL(FALSE) in the table without anything (No OnModify, No OnDatabaseModify). Result: as expected, only one statement:
  

  UPDATE “NAV2015″.dbo.”CRONUS BELGIË NV$Test ModifyAll” SET “Value”=@0
  

• Call MODIFYALL(TRUE) in the table without anything (No OnModify, No OnDatabaseModify). Result: once again as expected:
  

  UPDATE “NAV2015″.dbo.”CRONUS BELGIË NV$Test ModifyAll” SET “Value”=@0
  

• Call MODIFYALL(FALSE) in the table with an OnModify trigger (No OnDatabaseModify). Result: as expected:
  

  UPDATE “NAV2015″.dbo.”CRONUS BELGIË NV$Test ModifyAll” SET “Value”=@0
  

• Call MODIFYALL(TRUE) in the table with an OnModify trigger (No OnDatabaseModify). Here, I expect the loop. And indeed. For every record I get a SELECT (to lock the record) AND UPDATE statement. In the profiler, it looks like this:
  

  
  

  You clearly see the updatelock and the individual updates.
  

• Call MODIFYALL(FALSE) in the table which is “monitored” by the global OnDatabaseModify trigger (No OnModify code on the table). Well, as the global trigger is executed always, the MODIFYALL is broken in any case and this is what you get even with a MODIFY(FALSE):
  
• 
  

  Quite the same as above, isn’t it? Even on a MODIFY(FALSE), you’re not able to escape from the OnDatabaseModify. Personally, I love it! Just be aware of the implications.
  

• Call MODIFYALL(TRUE) in the table which is “monitored” by the global OnDatabaseModify trigger (No OnModify code on the table). Again, the same outcome. So for no difference between MODIFYALL(FALSE) and MODIFYALL(TRUE) when it comes down to the OnDatabaseModify trigger.
  

The above assumes that data will be changed. Even when calling MODIFYALL (and I assume the following will apply to MODIFY statements as well) .. you’re not sure NAV will call an UPDATE statement. As said above, it will check if the new value is actually new. For example, when I execute my function again after it already did (so I’m going to call the update to a value that exists already in the database), the following happens. As said, you have to make a distinction between the situation that is going to update multiple records at once (with MODIFYALL), and the situation where you have either an OnDatabase or OnModify trigger, which will make you loop through the records and update one-by-one:

• The mass UPDATE:
  
  • No implication. It will executie the UPDATE statement always
    
• The One-by-one:
  
  • You get something like this in the profiler:
    

    
    

    You see? Only SELECTS, no UPDATES. To test, I changed one record, and you see it’s going to execute the UPDATE for that record when performing that MODIFYALL again:
    

    
    

And as said, sometimes, you don’t even see the SELECT-statements. But I’m sure that’s normal caching on the NST. All depends on what is in cache at that moment.

Hope this is clear. Not easy to put this into words. Not even sure this was interesting for you to know. On my end, I found it quite interesting to see :).