It's been in the back of my mind for quite awhile now to figure out how the kernel of Dynamics AX actually handles it's delete_from and update_recordset logic. Sometimes, the kernel decides to emit exactly one DELETE FROM query to the database versus one 'DELETE FROM table WHERE RecId = @P1' per record in the table buffer.
I found a case on PriceDiscTable (this situation is not limited to just this object), a noticeably large table especially for our business, where I noticed multiple DELETE FROM statements getting emitted to the database, taking a very long time to complete a daily task our business runs. I looked at the table and noticed that the delete() method on the table was indeed overridden, but upon investigating the details of the method, only super() was getting called inside (along with some commented out code that we used in the past and plan to use in the future, but that's beside the point).
I removed the method as there really is no functional difference in just leaving it hidden (even though I know the reason why we wanted the commented code there, we didn't want to lose it). Lo and behold, our daily task now emits one single DELETE FROM with the criteria we specified in the WHERE clause.
One would think that the kernel would notice and see that the compiled version of the overridden delete() method with just the super() call is identical to the default "hidden" delete() method and handle them the same. But, we've been duped again. We'll have to store the commented out code we had in there somewhere else even though it would've been much more convenient to just keep in where it was.
As a side note, having at least one Delete Action on your table object will also force the kernel to emit multiple DELETE FROM queries. Removing them will tell the kernel that it doesn't have to perform a row-by-row operation and can emit the DELETE FROM in one statement.
And as (Joris de Gruyter of Dynamics AX Musings) has graciously pointed out in a comment, having database logging or alerts enabled on your table and/or having any MEMO fields on that table will also force a multiple DELETE FROM, who knew?
It's even worse than that, unfortunately. I ran into some bugs (which by the way did not get resolved, I'm still mad about that) with the RecordInsertList. Some information I got back was pretty interesting and I assume also applies to delete_recordset, insert_recordset and update_recordset. They told me that alerts, db logging, and MEMO fields (!?) on the table would also trigger a fallback to single-record delete/insert/update.
ReplyDeleteOh, good call on the DB Logging and Alerts, yes, those for sure would...even though of course you and I could easily figure out a better solution to handle that. Like say, by using database TRIGGERs which have been around years.
ReplyDeleteMEMO fields? Really? I'd really have to see that one to believe it. So the table just needs to have one column with type Memo, and just do a DELETE FROM table (with no WHERE) would cause a multiple DELETE FROM?
ReplyDeleteThat's what I've been told. Never tried it myself, I was too mad about my issue not getting resolved... :-)
Delete