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 :).

5.00 avg. rating (98% score) - 4 votes

Permanent link to this article: https://www.waldo.be/2015/01/21/nav2015-modifyall-behaviour-on-sql-server/

9 comments

2 pings

Skip to comment form

    • kriki on January 21, 2015 at 6:13 am
    • Reply

    I just hope they thought about multiple nst’s and the fact they have all their own cache that is synchronized once a minute.

      • waldo on January 21, 2015 at 12:42 pm
        Author

      Multiple NST’s is indeed always a challenge when it comes down to caching… .

  1. On a related topic, i noticed there is a bug (or maybe it’s stupid intended behavior…) you can’t do something like this with MODIFYALL:

    Rec.HideValidationDialog(TRUE);
    Rec.MODYFYALL(TRUE);
    Rec.HideValidationDialog(TRUE);

    The Global Variable for hiding the dialog will be ignored…
    You have to loop & MODIFY(TRUE).

    Anyone else noticed this?

  2. @devch
    You are not using MODIFYALL the way it is intended – where is the field inside your statement you want to update for all records?
    Second, record function calls (such as HideValidationDialog) always work one record only: the one that’s currently loaded in your variable. MODIFYALL intentionally does not work on one record (hence does not use a pointer or whatsoever on the record variable), but on all (considering filters, if existing), hence it cannot work with HideValidationDialog.
    I am not sure I was able to explain it nicely, but I really see no bug here.

  3. Well .. I didn’t mention the full statement myself .. when I write “MODIFYALL(TRUE)”, I mean “a MODIFYALL wher I want to call the OnModify trigger”. The field is less important .. . True .. the statement isn’t complete .. but that’s intentional.

    I guess devch means that as well?

    I understand Natalie in reasoning, but also devch .. . Never tested it… . Interesting :-).

    May be there will be a part 2 on this blogpost :-). Thanks for the remarks!

  4. Jep, it was missed intentionally as you did too. What i try to say is that when you set a global variable for the record instance, it will not be respected when you execute the OnModify Trigger by calling MODIFYALL(TRUE).
    IF it is not supposed to work, then i don’t understand why… At least it is questionable why it’s not working.

    Ok i found the real world example again, but i was wrong, it uses DELETEALL. But it should be the same issue:

    //ProdOrderComp.DELETEALL(TRUE);
    ProdOrderComp.SetSkipWhseComission(TRUE);
    IF ProdOrderComp.FINDSET THEN
    REPEAT
    ProdOrderComp.DELETE(TRUE);
    UNTIL ProdOrderComp.NEXT = 0;
    ProdOrderComp.SetSkipWhseComission(FALSE);

    Here i have to use a loop to ensure the Global Boolean is set when the OnDelete Trigger is executed. For me that is unexpected behavior. Correct me if i’am wrong.

      • waldo on January 21, 2015 at 12:44 pm
        Author

      I see what you mean. Performance wise, I see no issue .. DELETEALL with the OnDelete trigger would loop anyway. But indeed .. not respecting the global var smells like a bug..

  5. Well, that was an interesting read, thanks for sharing!

    P.S. Where do you find the time to test it all?..

  6. Not easy with three kids and a wife … (especially because of the wife ;-))

  1. […] Continue reading » […]

  2. […] Bron : Waldo’s Blog Lees meer… […]

Leave a Reply to waldo Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.