Hi Ultimately, either mechanism will result in locks being held on the affected records until you commit the transaction. In fact, because the looped approach takes longer to execute the locks will actually be held longer using this approach.
Malcolm From: Axapta-Knowledge-Village@yahoogroups.com [mailto:axapta-knowledge-vill...@yahoogroups.com] On Behalf Of thomas 'znal' ramdhan Sent: 01 September 2010 10:34 To: Axapta-Knowledge-Village@yahoogroups.com Subject: Re: [Axapta-Knowledge-Village] delete_from vs loop delete Hi Burtt, Thanks for brief explanation :) I have declare all suitable index for my table, but sometimes still returns me lock error. If I use batch helpers, for sure I get more lock issue in activity monitor. If this is the case, is loop delete the righteous solution ? Thanks and Regards, Afin On Wed, Sep 1, 2010 at 4:07 PM, Malcolm Burtt <malcolm.bu...@touchstone.co.uk<mailto:malcolm.bu...@touchstone.co.uk>> wrote: Hi The difference between the two approaches is in the speed of execution. Assuming that the tables delete() method has not been overridden and that there is no database logging of deletes for the table then "delete_from" x++ code will generate a single database round trip which will delete all of the records matched by the where clause. The looping approach will issue one database round trip for each record to be deleted in addition to the initial select and is, consequently, slower than the "delete_from" approach. It's worth noting that "delete_from" will automatically revert to a looped approach if the table's delete() method has been overridden or AX has been configured to log deletes for the table. This can be avoided by call the table's skipDataMethod() and skipDatabaseLog() passing "true" as the argument to each of these calls but you can only do that if you are sure that the code in the delete() method isn't needed in this instance and that its okay to not log the delete. You might have seen table locks with "delete_from" if your where clause has no viable index. In such cases the database has no choice but to lock the whole table while it deletes the rows. My advice: use "delete_from" wherever you can but, as with all database access, make sure you have suitable indexes. Regards Malcolm Burtt Touchstone Group People - Partnership - Solutions From: Axapta-Knowledge-Village@yahoogroups.com<mailto:Axapta-Knowledge-Village@yahoogroups.com> [mailto:Axapta-Knowledge-Village@yahoogroups.com<mailto:Axapta-Knowledge-Village@yahoogroups.com>] On Behalf Of thomas 'znal' ramdhan Sent: 01 September 2010 06:20 To: Axapta-Knowledge-Village@yahoogroups.com<mailto:Axapta-Knowledge-Village@yahoogroups.com> Subject: [Axapta-Knowledge-Village] delete_from vs loop delete Dear all, I'd like to ask about delete issue in AX. AX has 2 way to delete records by using 'delete_from' or below code while select forUpdate * from tableA { tableA.doDelete(); } Which one do you prefer to delete some records from both above code ? Sometimes, I found locked table if I use delete_from code. Thanks and Regards, Afin -- ne faites pas une limite pour votre competence ------------------------------------------------------------------------- bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit ------------------------------------------------------------------------- non faccia un limite per la vostra abilita ------------------------------------------------------------------------- --(-@ -- ne faites pas une limite pour votre competence ------------------------------------------------------------------------- bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit ------------------------------------------------------------------------- non faccia un limite per la vostra abilita ------------------------------------------------------------------------- --(-@
