Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Logic on Delete_From c...
Microsoft Dynamics AX (Archived)

Logic on Delete_From command on temp tables (real tables set to tmp) reliable?

(0) ShareShare
ReportReport
Posted on by Douglas Noel 3,905

Hi all,

normally the set oriented delete_from command falls back to a looped record single delete, if (the delete method is overwritten). This behavior does not occur if the table is a real one, which was set to temp mode using the setTmp() method.

I've detected this behaviour during analyzing the functionality of ReqTransExplosion form. I was wondering why they can use a delete_from (without any further skipXXXXX methods) on a temporary ReqTrans buffer which is filled using some helper classes (ReqTransFormExplosion, ReqTransExplode), without running in the ReqTrans delete method, which is (maybe) going to delete other records (in other tables). 

My question: Is this reliable in all cases? Or is this behaviour bound to the tier where the temp ist created? Or if the cursor is attached to a Formdatasource? Or does this this even depend on the kernel version? 

Is there a differnt behaviour for example, if using such constructs under batch/cil. I need to use some of the helper classes (or at least code parts) within a batch class and don't want to have some strange side effects on deleteting the (temp) ReqTrans cursor witihin my code.

Is there an official statemenet about that available?

Or is it even better not to rely on the "never fallback"?

regards

Douglas

*This post is locked for comments

  • Martin Dráb Profile Picture
    Martin Dráb 230,476 Most Valuable Professional on at
    RE: Logic on Delete_From command on temp tables (real tables set to tmp) reliable?

    I can confirm your observations. I set up an test case when I have a temporary table logging some data to a persistent table from delete().  If I use delete_from, the data get deleted but nothing gets logged, proving that delete() wasn't called.

    It behaves the same for both InMemory and TempDB tables.

  • Martin Dráb Profile Picture
    Martin Dráb 230,476 Most Valuable Professional on at
    RE: Logic on Delete_From command on temp tables (real tables set to tmp) reliable?

    Okay, so we're talking about whether delete() method is called or not, not whether the engine can and do use set-based processing.

    I'll try to test it by myself when I get into an AX 2012 environment...

  • Douglas Noel Profile Picture
    Douglas Noel 3,905 on at
    RE: Logic on Delete_From command on temp tables (real tables set to tmp) reliable?

    Hi,

    I've done a lot of checks with the result. Using delete_from on temp tables (or realtable set to tmp mode) never executes the delete method, independant of the setting of any of the skiPXXXXX methods, even if explicitly set to false.

    So this seems to be the only way to do a secure delete on temp buffers without having to worry about the coding of the delete method.

    So delete_from used on temp tables never has any side effects due to code within the delete trigger, where, at the opposite, a while select for update ... delete(), or record.delete() can have side effenss (beside the appropriate logic within the delete method use construct like if (!this.isTmp() do other sql operations). It 'simply' empties the temporary table without doing any other stuff. So even for deletion of a single temp record (if it is 'normally' a real one) using a delete_from where recid = xxxx is the better approach than using this. delete().

    At least in kernel version 6.3.3000.3971

    I don't know if the deletion is done in a row-by-row  way internally, but this is not important.  But delete_from on temp tables seems to never fall back to the 'normally' known row by row' behaviour.

    This is contrary to all the descriptions I've found. And therefore I'm afraid that this could be some of the 'could be changed at any time' things.

    It's to dangerous to rely on this non-execution of code within the delete trigger using delete_from on temp tables (at least if they are normally real standard tables).

    regards

    Douglas

  • Martin Dráb Profile Picture
    Martin Dráb 230,476 Most Valuable Professional on at
    RE: Logic on Delete_From command on temp tables (real tables set to tmp) reliable?

    How did you test that it's not executed row by row?

    I assumed that the engine for InMemory table doesn't have any support for set-based operations, so it can't use them in any case. It's just an assumption, but I never had a reason to question it before.

    By the way, I don't understand why you seem to claim that for the temporary buffer, using a set based set-based operation uses the temporary buffer by a degradation to row-by-row operation would go to database. It doesn't make good sense to me.

    Aren't you simply looking at if delete() is called (instead of whether it's a row-by-row operation), while the method is suppressed by skipDataMethods()?

  • Douglas Noel Profile Picture
    Douglas Noel 3,905 on at
    RE: Logic on Delete_From command on temp tables (real tables set to tmp) reliable?

    Hi Vilmos,

    thanks for the answer. But what I describe is exactly the opposite. They are not running in a row-by-row operation (and this is good). I would also rely on the garbage at out of scope, but this is MS code, and I just was wondering, if this is really OK.

    Btw: They can't rely on the out of scope in that case, bacause this cursor is always reused.

    So the documentation IS wong (or not reliable). The problem is, that if it would fall to row by row, this would be a bad approach in that particular case. Because they (MS) would delete REAL records in other tables.

    So I recently was lloking for the opposite answer like "Temp table will NEVER fall back to row by row)

    I think I'm going to use another own table (copy of ReqTrans) for my case, this one seems to dangerous for me at least,

    BTW: The documentaion speeks about 'can' fallback: YES, so I have to avoid this condition. Maybe this would be a good approach for MS too.

    Regards

    Douglas

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Logic on Delete_From command on temp tables (real tables set to tmp) reliable?

    Non-SQL tables (InMemory and TempDB tables are considered to be in this category) are running as a row-by-row operation anyway, refer to the documentation:

    msdn.microsoft.com/.../aa849875

    It might be better to rely on the scope of declared variables. Wherever your temporary buffers' code execution gets out of scope, it will be discarded, so you do not have to manually delete values one by one.

    So if you run a static method where the temporary buffer is declared and populated for further processing within the call, once code execution leaves that static method, the temporary buffer gets removed be it InMemory (garbace collector frees up space) or TempDB entries (drop table command is executed, which is faster than row-by-row deletion).

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,784 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,476 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans