What impact does my C/AL have on SQL - MODIFYALL/DELETEALL

Finally found some time to write Part II of this topic. The first part was quite a popular post, if I may say so myself Wink. I didn't expect this. I just prepared an internal developer meeting at my company about "how to use the FIND statements", as I noticed that things are sometimes misunderstood. I just prepared the meeting by creating a blogpost ("two flies in one hit" Smile).

Anyway, time for my kids to put asleep a bit earlier, so that I have some time to talk about some other statements. This time, I will try to go deeper into the MODIFYALL- and the DELETEALL-statement. Hope you enjoy it Wink. I know I will (don't know where I will end up Wink).

MODIFYALL

Good to know about the MODIFYALL statement is that the OnValidate trigger is a "no-go". You won't be able to call the OnValidate trigger of the fields you're modifying. This is very important to know, off course: Only use the MODIFYALL if no OnValidate-trigger is necessary (OK, I said it three times now in three sentences in three ways Indifferent).

The OnModify trigger is possible, even with the MODIFYALL statement.

First: a MODIFYALL without the OnModify-Trigger:

C/AL

SQL

Explanation

recCust.SETRANGE(...);

recCust.MODIFYALL(Name,'waldo',FALSE);

UPDATE "CRONUS BELGIË NV$Customer"

SET "Name"='waldo'

WHERE (("Language Code"='ENC'))

Only one UPDATE statement is produced.

So, what is the loop variant?

C/AL

SQL

Explanation

recCust.SETRANGE(...);

IF recCust.FINDSET(TRUE,FALSE) THEN

REPEAT

recCust.Name := 'waldo';

recCust.MODIFY(FALSE);

UNTIL recCust.NEXT = 0;

SELECT *,DATALENGTH("Picture") FROM "CRONUS BELGIË NV$Customer" WITH (UPDLOCK) WHERE (("Language Code"='ENC')) ORDER BY "No_"

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) SET "Klantengroep"=0 WHERE ("No_"='01905893')

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) SET "Klantengroep"=0 WHERE ("No_"='01905899')

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) SET "Klantengroep"=0 WHERE ("No_"='01905902')

...

First a select statement for the FIND.

   

For every MODIFY in the loop, an updatestatement is called

It is easy to see what statement is best to use in this case: The MODIFYALL, off course. There is only one statement to the server, and all records are modified. Now, what about updating more than one field (Like Erik P. Ernst asked as comment on my previous post)? Let's take similar code for that. I'll update three fields:

C/AL

SQL

Explanation

recCust.SETRANGE(...);

recCust.MODIFYALL(Name,'waldo',FALSE);

recCust.MODIFYALL("Name 2",'Waldo',FALSE);

recCust.MODIFYALL("Search Name",'WALDO',FALSE);

UPDATE "CRONUS BELGIË NV$Customer" SET "Name"='waldo' WHERE (("Language Code"='ENC'))

UPDATE "CRONUS BELGIË NV$Customer" SET "Name 2"='Waldo' WHERE (("Language Code"='ENC'))

UPDATE "CRONUS BELGIË NV$Customer" SET "Search Name"='WALDO' WHERE (("Language Code"='ENC'))

For every MODIFYALL statement, one UPDATE statement is generated.
 

The looping variant:

C/AL

SQL

Explanation

recCust.SETRANGE(...);

IF recCust.FINDSET(TRUE,FALSE) THEN

REPEAT

recCust.Name := 'waldo';

recCust."Name 2" := 'Waldo';

recCust."Search Name" := 'WALDO';

recCust.MODIFY(FALSE);

UNTIL recCust.NEXT = 0;

SELECT TOP 19 *,DATALENGTH("Picture")

FROM "CRONUS BELGIË NV$Customer" WITH (UPDLOCK)

WHERE (("Language Code"='ENC')) ORDER BY "No_"

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD)

SET "Name"='waldo',"Search Name"='WALDO',"Name 2"='Waldo'

WHERE ("No_"='01905893')

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD)

SET "Name"='waldo',"Search Name"='WALDO',"Name 2"='Waldo'

WHERE ("No_"='01905899')

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD)

SET "Name"='waldo',"Search Name"='WALDO',"Name 2"='Waldo'

WHERE ("No_"='01905902')

For every MODIFY an UPDATE statement, no matter how many fields are updated.

Remark:
It will only put those fields in the statements that were actually updated!

You see that the MODIFY and the MODIFYALL statements are behaving a little bit different. The MODIFYALL does not group the changes of the fields into one statement, while the MODIFY-statement does do that. Easy to remember: every MODIFY and MODIFYALL produces a servercall.

What do we learn from this?

  • What's best to use (when no Modify-trigger has to be executed): It actually depends on the number of records you expect in your range:
    • If you expect a huge number of records in the range and only a few fields to update: use the MODIFYALL
    • If you expect only a few records, less than the number of fields to update, you might want to go for the MODIFY in the loop.
  • Note that the loop always produces a SELECT statement. Be sure to send your lock together with this statement by using FINDSET(TRUE,...) or LOCKTABLE (see previous post for more info).

   

Second: A MODIFYALL with the OnModify-trigger

So tell me, how many times do you NOT want to call the MODIFY trigger? Not too many, do you? Let's see what happens when we do want that the OnModify trigger is called. Obviously, I will take the same code as above, but change the parameter:

C/AL

SQL

Explanation

recCust.SETRANGE(...);

recCust.MODIFYALL(Name,'waldo',TRUE);

SELECT *,DATALENGTH("Picture") FROM "CRONUS BELGIË NV$Customer" WITH (UPDLOCK) WHERE (("Language Code"='ENC')) ORDER BY "No_"

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) SET "Name"='waldo' WHERE ("No_"='01905893')

.

(OnModify Trigger Business Logic)

.

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) SET "Name"='waldo' WHERE ("No_"='01905899')

.

(OnModify Trigger Business Logic)

.

Instead of only one update statemens, I had (for this example) 120 SQL statements produced.

You can clearly see that the OnModify trigger was called. To be able to do this, the update isn't combined in one anymore. In fact, above result is exactly the same as doing all this in a loop:

  • The SELECT to range the loop (off course with the UPDLOCK)
  • An update, ranged on the primary key

So, if we do the same thing in a loop in stead of the MODIFYALL, you get:

C/AL

SQL

Explanation

recCust.SETRANGE(...);

IF recCust.FINDSET(TRUE,FALSE) THEN

REPEAT

recCust.Name := 'waldo';

recCust.MODIFY(TRUE);

UNTIL recCust.NEXT = 0;

SELECT *,DATALENGTH("Picture") FROM "CRONUS BELGIË NV$Customer" WITH (UPDLOCK) WHERE (("Language Code"='ENC')) ORDER BY "No_"

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) SET "Name"='waldo',"Search Name"='WALDO' WHERE ("No_"='01905893')

.

(OnModify Trigger Business Logic)

.

UPDATE "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) SET "Name"='waldo',"Search Name"='WALDO' WHERE ("No_"='01905899')

.

(OnModify Trigger Business Logic)

.

  

You see it's about the same. So, when calling the OnModify trigger, the code really isn't so different.

Now, I have to say that I noticed something strange. When I monitored the code above, I noticed that it entered the OnModfy trigger, but Rec.Name and xRec.Name were the same ... (while I did change the value). This is something I will not go into right now (who knows another blog post?Wink) - I will investigate it later. Bottom line is that it did what we expected (i.e. Calling the OnModify trigger)Indifferent.

What do we learn from this:

Except from the strange Rec-xRec behaviour, we can say that using the loop or using the MODIFYALL statement does not matter if you're calling the OnModify trigger on the table anyway.

That's about all I can think of to test and monitor about the MODIFYALL statement. Let's take a deeper dive into the DELETEALL.

   

DELETEALL

I expect this to have the same behaviour, but to be sure, I will just execute about the same as above, but with the delete statements. Also, with a DELETEALL statement, you have a similar option: you can call the OnDelete trigger, or you could choose NOT to call it. Off course

First: a DELETEALL without the OnDelete-Trigger:

To make things as transparant as possible, I'll take similar code for this example.

C/AL

SQL

Explanation

recCust.SETRANGE(...);

recCust.DELETEALL(FALSE);

DELETE FROM "CRONUS BELGIË NV$Customer" WHERE (("Language Code"='ENC'))

Only one statement to delete the entire range.

This is exactly what I expected it to be, if you compare it with the MODIFYALL statement like described above. We also know what to expect from the equivalent:

C/AL

SQL

Explanation

recCust.SETRANGE(...);

IF recCust.FINDSET(TRUE,FALSE) THEN

REPEAT

recCust.DELETE(FALSE);

UNTIL recCust.NEXT = 0;

SELECT *,DATALENGTH("Picture") FROM "CRONUS BELGIË NV$Customer" WITH (UPDLOCK) WHERE (("Language Code"='ENC')) ORDER BY "No_"

DELETE FROM "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) WHERE ("No_"='01905893')

DELETE FROM "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) WHERE ("No_"='01905899')

DELETE FROM "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) WHERE ("No_"='01905902')

One select (with UPDLOCK because of the FINDSET(TRUE)) and for every record, a DELETE statement, ranged on the primary key

And again, not really a surprise.
We are not changing the values of fields here, so like the MODIFYALL statement, we do not have to worry about the number of fields that are changed, which had an influence in the decision wether to use MODIFY or MODIFYALL (and again, makes my life easier).

What do we learn from this?

When the OnDelete-trigger doesn't have to be called, then always go for the DELETEALL statement.

Second: a DELETEALL with the OnDelete-Trigger:

Well, we know what we have to expect here as well, don't we. If the MODIFYALL is going to call an UPDATE for each record, I expect the DELETEALL to do exactly the same:

C/AL

SQL

Explanation

recCust.SETRANGE(...);

recCust.DELETEALL(TRUE);

SELECT *,DATALENGTH("Picture") FROM "CRONUS BELGIË NV$Customer" WITH (UPDLOCK) WHERE (("Language Code"='ENC')) ORDER BY "No_"

.

(OnDelete Trigger Business Logic)

.

DELETE FROM "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) WHERE ("No_"='01905893')

.

(OnDelete Trigger Business Logic)

.

DELETE FROM "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) WHERE ("No_"='01905899')

.

(OnDelete Trigger Business Logic)

.

First a SELECT with UPDLOCK to lock the range

Then the OnDelete trigger business Logic

Then the DELETE statement to delete the Customer record - ranged on the primary key

Not really a surprise, is it (OK, I know, it's getting boring, but I want to finish the story Wink). To conclude, let's take a look at the alternative loop:

C/AL

SQL

Explanation

recCust.SETRANGE(...);

IF recCust.FINDSET(TRUE,FALSE) THEN

REPEAT

recCust.DELETE(TRUE);

UNTIL recCust.NEXT = 0;

SELECT *,DATALENGTH("Picture") FROM "CRONUS BELGIË NV$Customer" WITH (UPDLOCK) WHERE (("Language Code"='ENC')) ORDER BY "No_"

.

(OnDelete Trigger Business Logic)

.

DELETE FROM "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) WHERE ("No_"='01905893')

.

(OnDelete Trigger Business Logic)

.

DELETE FROM "CRONUS BELGIË NV$Customer" WITH (REPEATABLEREAD) WHERE ("No_"='01905899')

.

(OnDelete Trigger Business Logic)

.

First a SELECT with UPDLOCK to lock the range

Then the OnDelete trigger business Logic

Then the DELETE statement to delete the Customer record - ranged on the primary key

This is EXACTLY the same as the DELETEALL statement. But let's be honest ... Again, we expected this, didn't we Wink?

What do we learn from this?

It doesn't matter to use the DELETEALL(TRUE) or the alternative loop with the FINDSET(TRUE) and DELETE(TRUE). In SQL Server, the same code is executed. Now, you could argue with the fact "only two lines of code is executed in C/SIDE" instead when in a loop it is not. But I beg to differ on that, because it is C/SIDE or NAV or the ndbcs.dll (which is for me one environment) that is sending the statements to SQL Server. That means that it's still the client that is looping, wether it was intentional (with the LOOP) or not (with the DELETEALL). So for me: doesn't matter, whatever fits for you Smile.

So, this is all, I hope you enjoyed it. Next thing on the agenda (if you're still interested): Marking records.

Comment List
Related
Recommended